(This post comes from one of our Junior DBAs – @SQLDork)
If you haven’t read it already, i recently wrote a blogpost where i go over Backup-DbaDatabase, you can read that post here.
Or here, it’s your choice which link you want to click to go there.
Today’s command: Restore-DbaDatabase
Same setup as last time, backups, ISE, SQL Server instances, all that good stuff.
Restore-DbaDatabase -SqlInstance PRECISION-M7520SQL2016
This kinda works, but asks to specify a bunch of paths if you run it. Best to specify those in the parameters ahead of time, i’d imagine.
Restore-DbaDatabase -SqlInstance PRECISION-M7520SQL2016 -Path 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLBackup'` -DestinationDataDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLDATA'` -DestinationLogDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLLog'
This seems to work, but won’t do anything because the databases already exist.
Warning message suggests using -WithReplace to overwrite the existing databases, but i think there’s another option…
Restore-DbaDatabase -SqlInstance PRECISION-M7520SQL2016 -DatabaseName 'test_restored'` -Path 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLBackuptest_202004241133.bak'` -DestinationDataDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLDATA'` -DestinationLogDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLLog'
Moved some parameters around for readabilities’ sake.
Warning message implies we need to specify the names for the .mdf and .ldf, which makes sense.
Restore-DbaDatabase -SqlInstance PRECISION-M7520SQL2016 -DatabaseName 'test_restored'` -Path 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLBackuptest_202004241133.bak'` -DestinationDataDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLDATA'` -DestinationLogDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLLog'` -DestinationFilePrefix 'restored_'
Success! Database name is “test_restored”, and file names are “restored_test.mdf” and “restored_test.ldf”
Let’s dork around with the parameters a bit to get the names to be consistent:
Restore-DbaDatabase -SqlInstance PRECISION-M7520SQL2016 ` -Path 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLBackuptest_202004241133.bak'` -DestinationDataDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLDATA'` -DestinationLogDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLDATA'` -DestinationFilePrefix 'restored_' -RestoredDatabaseNamePrefix 'restored_'
Moved the log files to go in the DATA folder, because apparently the Log folder is for different stuff. (Ed: yep…that’s for errorlogs, crash dumps, default trace files, etc.)
No longer specifies the database name via -DatabaseName.
Instead uses -RestoredDatabaseNamePrefix to put ‘restored_’ at the beginning of the name.
This would be useful if we were restoring multiple databases at once.
NOTE: There is a -DestinationFileSuffix, but there is no -RestoredDatabaseNameSuffix parameter. I’ve asked twitter for clarification on this.
There doesn’t seem to be a parameter for renaming the logical names of the
files in Restore-DbaDatabase either, but there is one in Rename-DbaDatabase.
Restore-DbaDatabase -SqlInstance PRECISION-M7520SQL2016 ` -Path 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLBackuptest_202004241133.bak', 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLBackupsquare[]brackets_202004241133.bak'` -DestinationDataDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLDATA'` -DestinationLogDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLDATA'` -DestinationFilePrefix 'restored_' -RestoredDatabaseNamePrefix 'restored_'
Restore multiple databases at once, there’s probably a better way to do this without having to specify the obnoxiously long path both times.
Yes, i have a database named square[]brackets.
Yes, i have gone crazy.
No, you shouldn’t make a database with square brackets in the name.
Restore-DbaDatabase -SqlInstance PRECISION-M7520SQL2016 ` -Path 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLBackuptest_202004241133.bak'` -DestinationDataDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLDATA'` -DestinationLogDirectory 'C:Program FilesMicrosoft SQL ServerMSSQL13.SQL2016MSSQLDATA'` -DestinationFilePrefix 'restored_' -RestoredDatabaseNamePrefix 'restored_' -OutputScriptOnly
Using -OutputScriptOnly dumps a T-SQL script into the command line, which you can edit or run to your heart’s content.
Summary
Parameters i found to be useful, some of which i didn’t test
because i’d have to insert lines into the database and i’m lazy
:
-SqlInstance: Required, tells powershell what instance to restore to.
Can use commas to point to multiple instances, like if you wanted a fresh copy of prod data on your 7 dev boxi.
-Path: Tells powershell where the backups to restore are, enclose in single-quotes if there’s a space. (Looking at you, Program Files)
Can point to a folder to restore all the backups in that folder, or to a .bak file to restore just that file.
Use commas to specify multiple paths.
-DestinationDataDirectory: Where to put the physical data files, enclose in single-quotes if there’s a space.
-DestinationLogDirectory: Where to put the physical log files, enclose in single-quotes if there’s a space.
NOTE: All paths need to be specified relative to the instance specified with -SqlInstance.
-DestinationFilePrefix: Adds a string to the beginning of the names of the restored files.
-RestoredDatabaseNamePrefix: Adds a string to the beginning of the restored database name.
-DestinationFileSuffix: Adds a string to the end of the names of the restored files.
-RestoredDatabaseNameSuffix: Does not exist, i wish it did, but it doesn’t.
-OutputScriptOnly: Paranoid? This will give you the script for the thing you just did, just in case.
-NoRecovery: Restores the files with NoRecovery, for if you have more files to restore.
-Recover: Does a With Recovery, nothing fancy.
-RestoreTime: Point in time recovery, stop restoring before the bad thing happened.
-WithReplace: Overwrites the existing database if there is one with the same name.
-MaintenanceSolutionBackup: Tell dbatools that you’re using OlaHallengren’s (b) maintenance solution.
Makes things faster since it now knows what to look for, rather than checking every file in the folder.
That’s all for today, if you want to bombard me (b|t) with questions i don’t know how to answer, you can do so on twitter!
The post TIL: Restore-DbaDatabase appeared first on DallasDBAs.com.