Restoring a SQL Server database without recovery
If you’ve not done much work with SQL Server restores, a little bit of background may help with todays post
The are 2 phases to what we commonly call a SQL Server databae restore:
- Restore the data to the disk
- Recover the database
The first phase is the one we normally think of, writing the SQL Server data back onto the disks.
During the recovery phase SQL Server ensures that everything in the database is consistent and that all transactions that were open at the point the database is restored to are rolled back.
All of the previous Restore-DbaDatabase examples in this series haveve been restoring the database, and then recovering it as well so it’s been hidden inside the function.
However there are a couple of scenarios when you don’t want the recovery phase to fire.
The first is when you are going to want to restore more backups to bring the database further forward in time. This might be in a disaster scenario where you’ve lost some of your backup storage. You could restore what you have while your Storage team are pulling the missing backups from tape.
The second is when you’re not sure when you want to stop the restore. So you restore to a specific point in time, put the database into standby mode, and then if needed you apply more backups.
Leaving a restore so it can be continued
We’ll look at the first scenario first. Disaster has struck, you’ve lost your database and your backups storage is missing backup files.
We’ll use Restore-DbaDatabase to restore as many files as it can from the backup storage. Remember we’ll scan all the files on there and build the longest restore chain we can. So far, so usual, the difference between this example and the previous ones is that we’ll use the -NoRecovery
switch:
Restore-DbaDatabase -SqlInstance server1\instance -Path \\filestore\backups$\db1 -NoRecovery
This just tells us not to perform the recovery step once the restores have finished. You will have data on the disks, you just can’t access it yet.
You get the call from the storage team that they’ve retrieved the missing backups, so now how do you restore more backups to the existing database? With the -Continue
switch
Restore-DbaDatabase -SqlInstance server1\instance -Path \\filestore\backups$\db1 -Continue
This lets Restore-DbaDatabase know that you want to carry on restoring the existing database. The same backup scans will take place as normal, but those that have already been restored will be skipped and just the new ones applied.
If your storage team is really having problems and are just getting the backups back in batches, it is possible to combine the 2 switches:
Restore-DbaDatabase -SqlInstance server1\instance -Path \\filestore\backups$\db1 -Continue -NoRecovery
This will restore any newer backups, but still allow you to restore more when they arrive.
If the storage team run out of luck and can’t rescue any more backup files, you can for the databse to recover and came back online with:
Restore-DbaDatabase -SqlInstance server1\instance -Database db1 -Continue -Recover
Which will perform the recovery stage and leave the database open and ready for use. Any uncommitted transactions will have been rolled back, so you will be unable to restore any more backups without starting from scratch.
Restoring to Standby mode
When a database is in recovering mode you can’t examine it as it’s closed to users. If you’re not sure exactly when you wanted to restore to this could be an issue as you can’t query the database.
In this case restoring the database to a Standby state is the solution. When you restore a SQL Server database to a Standby state, you provide a file for the rolled back transactions to be stored in. This means you can query the database, read only queries, and also still continue with restores if you want to.
Standby cannot be used when restoring a database from an earlier version of SQL Sever to a later version of SQL Server. This is because the upgrade is done during the recovery phase, which doesn’t happen. Restore-DbaDatabase will return an error if you try to do this. It’s a limitation of SQL Server, so Restore-DbaDatabase can’t work around it.
To restore a Database to a point in time, and put it into a standby state you can use the following command:
Restore-DbaDatabase -SqlInstance server1\instance -Database db1-test -Path \\filestore\backups$\db1 -RestoreTime (Get-Date).AddHours(-4) -StandByDirectory c:\temp
The standby directory path is relative to the SqlInstance. So in this example, it will use c:\temp on server1. You need to make sure that your SQL Server instance has permissions to the folder to create, read and modify a file.
In this folder we will create a standby file with the format DbNameyyyyMMddHHmmss.bak
, that is the database name folowed by a timestamp so at the time of writing that would generate db1-test20200514104822.bak.
The reason we ask for a folder rather than a filename is that we’ve designed Restore-DbaDatabase to let you do this with multiple databases. So if you’ve an application than relies on multiple databases and you want them all them in standby at the same time you can still do:
Restore-DbaDatabase -SqlInstance server1\instance -Path \\filestore\backups$\db1 -RestoreTime (Get-Date).AddHours(-4) -StandByDirectory c:\temp
And we can create a standby file for each database being restored.
What do you if you’ve not hit the right point in time? Then you can combine StandbyDirectory
and Continue
to step through to another point in time:
Restore-DbaDatabase -SqlInstance server1\instance -Path \\filestore\backups$\db1 -RestoreTime (Get-Date).AddHours(-3.75) -StandByDirectory c:\temp -Continue
This will only restore the backups needed to bridge the extra 15 minutes so it’s quite fast to do. This only works FORWARDS, if you’ve overshot your target then unfortunately you have to start from the beginning I’m afraid.
Once you’ve found the correct spot, to open up the database you just do:
Restore-DbaDatabase -SqlInstance server1\instance -Database db1 -Continue -Recover
And you’re database is ready to go.
It is possible to wrap this in a PowerShell ForEach loop to step through a database to work out exactly when something happened. I’ve a blog post on using this technique Using Restore-DbaDatabase continue to find when a row was deleted, which shows an example of stepping through a database restore in varying sized time slices
Conclusions
It may sound strange to say you want to restore a SQL Server database, but not to recover it. But there are good reasons for doing so, and we’ve seen some of them in this post.
With Standby and continue you also have a great way to use Restore-DbaDatabse to step through restores at time intervals to find the exact point you want to recover the database
All posts in this series can be found at 31 Days of dbatools Backup and Restores
Leave a Reply