In the previous chapter, we talked about how important backups are, but we’d argue that restores are equally important. When there’s a disaster, you need to quickly and (hopefully) easily restore your databases. PowerShell is the perfect solution, because it simplifies bulk actions.
Restores are fundamental for migrations, disaster recovery, continuous integration/continuous deployment (CI/CD), and even testing to ensure restores comply with governmental regulations. This chapter will cover the multitude of ways to effectively use our restore commands both for simplified restores and advanced ones as well. You’ll learn how to do the following:
By the end of this chapter, you’ll feel more relaxed knowing you can easily restore an entire instance’s worth of database backups with a single command. And you’ll be ready to save the organization in the event of a disaster or unexpected event.
We designed our primary restore command, Restore-DbaDatabase
, to be as flexible as possible. If you can do it in SQL Server Management Studio (SSMS) or with T-SQL, you can do it in dbatools. We handle restores using built-in SQL Server functionality, so if it’s not possible in SQL Server, we can’t support it. For instance, if you have a backup that is a newer version than the destination SQL Server version, we can’t restore it. dbatools also can’t decrypt an encrypted database without the required key.
One scenario supported by Microsoft that we don’t offer a command for is restoring the master database. Although you can go through a torturous routine, like the one described in Microsoft Docs at sqlps.io/systemdbrestore, to restore your master database, we were unable to automate this routine with 100% reliability across all scenarios, and ultimately, we decided it wasn’t safe enough to add to dbatools.
Read more about limitations and design decisions The primary author of our backup/restore suite, Stuart Moore, wrote an entire article dedicated to discussing the limitations of our backup and restore commands. In it, he talks about some of the decisions behind why we don’t support third-party backups, built-in multithreading, restoring master, and more. Read all about it at sqlps.io/limitations.
dbatools can restore some system databases, however, and you can see some of them in table 11.1.
And now let’s dive into user-database restores.
We have a ton of ways to restore a database, and with each of these scenarios, it’s important to keep in mind the following:
The path to the backup file is relative to the SQL Server instance and not your own computer.
The SQL Server service account must have access to the backup file to successfully perform a restore.
Now let’s look at progressively complex ways of restoring a database.
When restoring a database, the most common scenario is restoring a single full backup. Restoring a single full backup is useful when you’ve got a sizable outage window and want to move a database to a new server (such as test-to-production or even production-to-production). We’ve performed quick one-off restores of a single file after failed application upgrades as well. To restore a full backup to SQL Server, you can specify the -Path
, as seen in the next code snippet.
PS> Restore-DbaDatabase -SqlInstance sql01 -Path S:ackupspubs.bak ComputerName : sql01 InstanceName : MSSQLSERVER SqlInstance : sql01 BackupFile : S:ackupspubs.bak BackupFilesCount : 1 BackupSize : 2.84 MB CompressedBackupSize : 2.84 MB Database : pubs Owner : sqladmin DatabaseRestoreTime : 00:00:01 FileRestoreTime : 00:00:01 NoRecovery : False RestoreComplete : True RestoredFile : pubs.mdf,pubs_log.ldf RestoredFilesCount : 2 Script : {RESTORE DATABASE [pubs] FROM DISK = N'S:ackups ➥ pubs.bak' WITH FILE = 1, MOVE N'pubs' TO N'D:MSSQL15.MSSQLSERVERMSSQLDATA ➥ pubs.mdf', MOVE N'pubs_log' TO N'D:MSSQL15.MSSQLSERVERMSSQLDATA ➥ pubs_log.ldf', NOUNLOAD, STATS = 10} RestoreDirectory : D:MSSQL15.MSSQLSERVERMSSQLDATA WithReplace : False
You can also pipe in files from Get-ChildItem
, as shown here.
Remember, like with T-SQL, the path to the backup file is relative to the SQL Server instance and not your own computer.
Restoring databases from a directory of backups is one of our favorites and a scenario that is commonly used by the dbatools community, because it works so well in environments using Ola Hallengren’s Maintenance Solution (sqlps.io/ola), which is a free and open source solution for backups and integrity checks, as well as index and statistics maintenance. When designing the command, we knew PowerShell’s pipeline was crazy powerful, so we thought, “What if you could just pipe in a bunch of files, then the command would examine the restore header to figure out what needs to be restored?” Stuart figured out how to do just that and added it right into Restore-DbaDatabase
.
Check out listing 11.3, which gets a list of all user databases; backs them up with one full, one diff, and three logs; and stores all of the files in C: empsql. Then it performs directory listing and stores the output to $files. Then, it magically restores all fulls, diffs, and logs to the same server, overwriting the existing databases, because -WithReplace
was specified.
If a database exists, dbatools will not overwrite it unless you specify -WithReplace
. So, you can rest easy, knowing that we do not overwrite by default, and you’ll just get a warning if you try.
This emulates a real-world restore scenario, where you’d restore a whole instance’s worth of databases from a single directory for either disaster recovery or “offline” migration scenarios.
# Make a full backup, a diff backup, and three log backups PS> New-Item -Path 'C: empsql' -Type Directory -Force PS> $splatGetDatabase = @{ SqlInstance = "sql01" ExcludeDatabase = "tempdb", "master", "msdb" } PS> $dbs = Get-DbaDatabase @splatGetDatabase PS> $dbs | Backup-DbaDatabase -Path C: empsql -Type Full PS> $dbs | Backup-DbaDatabase -Path C: empsql -Type Diff PS> $dbs | Backup-DbaDatabase -Path C: empsql -Type Log PS> $dbs | Backup-DbaDatabase -Path C: empsql -Type Log PS> $dbs | Backup-DbaDatabase -Path C: empsql -Type Log # See files, set the variable to $files, restore all files PS> Get-ChildItem -Path C: empsql -OutVariable files PS> $files | Restore-DbaDatabase -SqlInstance sql01 -WithReplace
Behind the scenes, Restore-DbaDatabase
figures out all of the restore chains, pieces them together, then performs the restore. It’s so good that you can pipe in two full backups for the same database and it’ll only restore the most recent one.
Note that it takes time to read each backup header, so piping in thousands of files will take a while to process. If you have thousands of files, we recommend filtering first using Get-ChildItem
and Where-Object
and then piping once you have a reasonable number of files.
Sometimes, you just want to see or save the scripts that will be executed instead of actually executing them. You may even want to save the T-SQL to source control for disaster recovery. We’ve got you covered there, too, with the -OutputScriptOnly
parameter. Add -OutputScriptOnly
to any Restore-DbaDatabase
run to see the T-SQL, as shown next.
PS> $splatRestoreDatabase = @{ SqlInstance = "sql01" Path = "C: empsqlfull.bak" WithReplace = $true OutputScriptOnly = $true } PS> Restore-DbaDatabase @splatRestoreDatabase
By default, all of the text is dumped to screen, but you can pipe it to clip
to save it to your clipboard or pipe it to Out-File
to save the output to disk.
We assume sane defaults as often as possible, which helps make dbatools commands convenient and enjoyable to use. The same is true with Restore-DbaDatabase
: by default, our restore command figures out the default data and log directories and restores the databases to those directories. You’re probably familiar with these directories, as shown in figure 11.1.
There may be times, however, that you want to restore databases to nondefault data and log directories. This is a common scenario for databases that require dedicated drives or directories. To restore to a specific data and log directory, use the -DestinationDataDirectory
and -DestinationLogDirectory
parameters, as shown next.
PS> $splatRestoreDb = @{ SqlInstance = "sql01" Path = "\nassql01mydb01.bak" DestinationDataDirectory = "D:data" DestinationLogDirectory = "L:log" } PS> Restore-DbaDatabase @splatRestoreDb
Want to restore a chain of backups over time? You must use -NoRecovery
to allow the restores to continue; otherwise, the database is recovered and ready for action. We support the WITH NORECOVERY
scenario with the -NoRecovery
parameter.
Nonrecovered databases are useful in high-availability scenarios such as availability groups and log shipping. They are also useful when prepping for migrations, because they allow you to restore most of a database, then just restore the logs once the migration is finalized. To leave your database in a restoring state, use the code shown here.
PS> $splatRestoreDb = @{ SqlInstance = "sql01" NoRecovery = $true } PS> Restore-DbaDatabase @splatRestoreDb -Path C: empsqlfull.bak PS> Restore-DbaDatabase @splatRestoreDb -Path C: empsqldiff.bak
When it’s time to recover the database with the final backup file, use the -Continue
parameter as shown next.
PS> $splatRestoreDbFinal = @{ SqlInstance = "sql01" Path = "C: empsql rans.trn" Continue = $true } PS> Restore-DbaDatabase @splatRestoreDbFinal
Once this command is run, your database will be fully recovered and no longer ready for additional backups to be restored.
Renaming a database during a restore can be useful in a number of situations, like renaming it to reflect that it’s in the test environment. For instance, you may want to rename mydb to mydb_test.
When renaming a database during a restore, you may be tempted to use only the -DatabaseName
parameter, but this will change only the name of the database, much like right-clicking on a database and renaming it in SSMS. To rename the underlying physical filenames as well, you want to use the -ReplaceDbNameInFile
parameter, as shown in the next listing.
PS> $splatRestoreDbRename = @{ SqlInstance = "sql01" Path = "C: empsqlpubs.bak" DatabaseName = "Pestering" ReplaceDbNameInFile = $true } PS> Restore-DbaDatabase @splatRestoreDbRename
Note that this does not rename the logical database name, because renaming the logical filenames is not supported at this time.
One of most useful features of SQL Server backups is the ability to restore to a specific point in time. This is useful for restoring mistakenly deleted data or restoring to a specific time of day. We’ve made point-in-time restores as straightforward as possible, using the -RestoreTime
parameter, which accepts the PowerShell datetime
format.
Imagine you have a folder at \nassqlsql01mydb with a full backup and a few log backups. You can restore to a specific point in time by specifying \nassqlsql01mydb as the -Path
and providing the exact moment in time you’d like to restore to. You can see this in action in the following code sample.
PS> $splatRestoreDbContinue = @{ SqlInstance = "sql01" Path = "\nassqlsql01mydb" RestoreTime = (Get-Date "2019-05-02 21:12:27") } PS> Restore-DbaDatabase @splatRestoreDbContinue
Considering how long it can potentially take to manually piece the backups together, this method can save quite a bit on time and stress.
Did you know that since SQL Server 2008, you can mark transactions in the database and use the marked transaction as a guide when performing a restore? We prefer this method over restoring to a specific point in time: when restoring mistakenly deleted data, it can be far more accurate, because we don’t have to know the exact time that a transaction was executed. We suspect that most people haven’t seen this in action, so we’ll include the T-SQL code as well, in the next listing.
Marked transactions Read more about marked transactions at sqlps.io/ mark.
BEGIN TRANSACTION DeleteCandidates WITH MARK N'Deleting a Job Candidate' DELETE FROM pubs.dbo.employee WHERE employeeid = 13 GO COMMIT TRANSACTION DeleteCandidates
Note that any time you mark a transaction using WITH MARK
, you must also name the transaction. In listing 11.10, we named the transaction DeleteCandidates
. We’ll use this name as the -StopMark
in the next listing.
# Backup your database PS> $splatRestoreDb = @{ SqlInstance = "sql01" Database = "pubs" FilePath = "C: empfull.bak" } PS> Backup-DbaDatabase @splatRestoreDb # Restore to the point right before the delete was executed PS> $splatRestoreDbMark = @{ SqlInstance = "sql01" Path = "C: empfull.bak" StopMark = "DeleteCandidates" StopBefore = $true WithReplace = $true } PS> Restore-DbaDatabase @splatRestoreDbMark
If you’d like to stop right after the transaction, set -StopBefore
to $false
.
One of the coolest features we automate is restoring corrupt pages from backup. In our experience, corruption is most often caused by failing hardware, such as an unstable storage system. Restoring corrupt pages instead of the entire database can save you a ton of time, especially if it’s a large database and a small bit of corruption. If page restores are new to you, Microsoft has some really good docs (sqlps.io/restorepages) that detail pages, including the limitations and restrictions. Restoring a corrupt page first starts with a check for pages marked as suspect (suspected of corruption) using Get-DbaSuspectPage
.
Once you have a list of suspect pages, you pass it to the -PageRestore
parameter of Restore-DbaDatabase
. In the next listing, we’ll restore all corrupt pages found in the pubs database on sql01.
PS> $corruption = Get-DbaSuspectPage -SqlInstance sql01 -Database pubs PS> $splatRestoreDbPage = @{ SqlInstance = "sql01" Path = "\nasackupssqlpubs.bak" PageRestore = $corruption PageRestoreTailFolder = "c: emp" } PS> Restore-DbaDatabase @splatRestoreDbPage
You’ll notice that the code references -PageRestoreTailFolder
. This is a required parameter when using -PageRestore
--it specifies the folder where SQL Server will back up the tail of the log.
In chapter 10, we backed up a couple databases to Azure using both shared access signatures (SASs) and access keys. Now we’re going to learn how to restore those backups.
Because you created the SAS credential in the previous chapter, you can simply specify the path to the backup file in Azure, and it’ll be restored using that SAS credential.
PS> $splatRestoreDbFromAzure = @{ SqlInstance = "sql01" Path = "https://acmecorp.blob.core.windows.net/backups/mydb.bak" } PS> Restore-DbaDatabase @splatRestoreDbFromAzure
If you chose to stripe your backups, dbatools can easily handle that too: just pass in all the stripe addresses (this works for local backups as well), as shown here.
PS> $stripe = "https://acmecorp.blob.core.windows.net/backups/mydb-1.bak", "https://acmecorp.blob.core.windows.net/backups/mydb-2.bak", "https://acmecorp.blob.core.windows.net/backups/mydb-3.bak" PS> $stripe | Restore-DbaDatabase -SqlInstance sql01
Now we’ll discuss restoring a database using the access keys method outlined in chapter 10. Like Backup-DbaDatabase
, Restore-DbaDatabase
also uses the -AzureCredential
parameter to restore databases using access keys, as illustrated in the following code.
PS> $splatRestoreDbFromAzureAK = @{ SqlInstance = "sql01" Path = "https://acmecorp.blob.core.windows.net/backups/mydb.bak" AzureCredential = "AzureAccessKey" } PS> Restore-DbaDatabase @splatRestoreDbFromAzureAK
If you need more advanced scenarios for performing your restores, we recommend reading Stuart Moore’s post at sqlps.io/complex for advanced restore magic. Actually, even if you don’t need more advanced scenarios, we still recommend his multipart series anyway, because it gives detailed insight directly from the author of the commands.
Back up all user databases in your test SQL Server instance. Perform one full backup, one differential backup, and three log backups, and then restore the entire folder back to your test instance, ensuring you use -WithReplace
.
Restore a single database, and move the data and log files to a new location.