11 Restore

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:

• Restore an entire instance

• Create scripts that ease disaster recovery

• Quickly recover from database corruption

• Restore from Azure

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.

11.1 Limitations and considerations

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.

Table 11.1 System database restore support within dbatools

And now let’s dive into user-database restores.

11.2 Restore scenarios

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.

11.2.1 File

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.

Listing 11.1 Restoring from a file

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.

Listing 11.2 Restoring from a file that’s piped in

PS> Get-ChildItem \nasackupsmydb.bak |
Restore-DbaDatabase -SqlInstance sql01

Remember, like with T-SQL, the path to the backup file is relative to the SQL Server instance and not your own computer.

11.2.2 Directory

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.

Listing 11.3 Restoring a database from full, diff, and logs stored in a directory

# 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.

Get-DbaProcess sql01 -Database testdb | Stop-DbaProcess

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.

11.2.3 Output T-SQL restore scripts

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.

Listing 11.4 Viewing the output script

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.

Restore-DbaDatabase -SqlInstance sql01 -Path "C:	empsqlfull.bak"
➥ -OutputScriptOnly | clip

Restore-DbaDatabase -SqlInstance sql01 -Path "C:	empsqlfull.bak"
➥ -OutputScriptOnly | Out-File C:	emp
estore.sql

11.3 Restoring to custom data and log directories

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.

Get-Help Get-DbaDbFileMapping -Detailed

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.

Listing 11.5 Restoring to a different directory

PS> $splatRestoreDb = @{
    SqlInstance = "sql01"
    Path = "\nassql01mydb01.bak"
    DestinationDataDirectory = "D:data"
    DestinationLogDirectory = "L:log"
}
PS> Restore-DbaDatabase @splatRestoreDb

11.3.1 No recovery

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.

Listing 11.6 Restoring full and diff backups with no recovery

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.

Listing 11.7 Recovering a database with a final transaction log

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.

11.3.2 Renaming a database

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.

Listing 11.8 Completely renaming a database

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.

11.3.3 Point-in-time restores

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.

Listing 11.9 Restoring to a point in time

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.

11.3.4 Restoring to a marked transaction

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.

Listing 11.10 Creating a transaction 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.

Listing 11.11 Restoring up to a transaction

# 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.

11.3.5 Recovering a corrupt database

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.

Get-DbaRegisteredServer | Get-DbaSuspectPage

Get-DbaSuspectPage -SqlInstance sql01 -Database pubs

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.

Listing 11.12 Restoring all corrupt pages found in pubs

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.

11.4 Azure

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.

11.4.1 Shared access signatures

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.

Listing 11.13 Restoring a single backup from Azure using SAS

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.

Listing 11.14 Restoring striped backup from Azure using SAS

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

11.4.2 Access keys

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.

Listing 11.15 Restoring a single backup from Azure using an access key

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.

11.5 Hands-on lab

• 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.

• Restore a single database to a new name.