Simple Restores
Finally we’re going to start using Restore-DbaDatabase after talking about it for a couple of days.
Given all the options that Restore-DbaDatabase offers, we’ll start off simple and build up the complexity of the commands over the next couple of days. We’ll also cover what’s going under the hood of the command, if you’ve got a basic understanding of that a lot of other options make more sense
At the basic level what does a Restore-DbaDatabase run do during a simple restore:
- Connect to a SQL Server Instance
- Scan 1 or more backup file
- Work out whan can be restored from these files
- Begin restoring the database(s) to the SQL Server instance
- Continue restores until it runs out of backups to restore
- Open the database
There’s a fair amount going on under the hood, even for a ‘simple’ restore. Let’s work through those as we build up our first Restore-DbaDatabase command.
From here on in the post, I’m making the assumption that we’re running against an on-premise SQL Server instance and that you have DBA rights on that instance.
First off we need a SQL instance to connect to, and credentials to do that. If we’re running the command as a Windows user with rights to connect we don’t need to provide credentials as that will be taken care of us. If not, we need to provide a credential to connect. We can do that like this;
Via a prompt for username and password:
$sqlCredential = Get-Credential
Or in code:
$sqlPasswd = ConvertTo-SecureString 'P@ssw0rdl!ng' -AsPlainText -Force
$SqlCredential = New-Object System.Management.Automation.PSCredential ('sa', $sqlPasswd)
The latter has the downside of leaving the plaintext password in your command history.
Now, we need some backup files. To be successful, we need at least one full backup file per database. On top of that we can have as many differential and log files per database as you’d like. These files must be reachable and readable by the SQL Server instance doing the restore. Any path you pass in must be relative to the SQL Server instance, not from where you’re executing the command.
In these examples, we’re going to be working on the box hosting SQL Server to make life easier.
If you’d like to follow along, the backups I using are in this github repository: . They were created on SQL Server 2014 Express edition.
Or you create them using my Time Series db script – – I set the delay to ’00:00:05′ (5 seconds) to speed things up.
We now have the basics needed to run a restore. For this, the command is going to look quite simple:
Restore-DbaDatabase -SqlInstance localhost -Path c:\backups\localhost\db1
Really, that’s all we need. As this is a simple example we’re making some assumptions here:
- The database we’re restoring doesn’t exist on server1\DevInstance
- There are no filename conflicts on the target instance
- That there aren’t a lot of backups in the backup folder
- There are some backup files in the folder
What would happen if any of those assumptions were wrong? In order:
- The restore would fail. We won’t restore over an existing database without explicit instructions
- The restore would fail. We won’t resstore over existing files without explicist instructions
- The restore won’t start as there’ll be nothing to restore
- The backup will work, it might just take a long time
If everything goes right, you’ll see something like this happen:
The first progress bar is scanning the backup files, the second bars are restoring each of the backups needed.
I’ve hidden the restore output for the moment as we’ll cover that later on.
What would you have seen if any of the mentioned issues occured?
Database already exists
If the database already existed, you’d still see the file scans happening, but then when the restore starts it would fail with this error message:
WARNING: [21:11:02][Test-DbaBackupInformation] Database db1 exists, so WithReplace must be specified
WARNING: [21:11:03][Test-DbaBackupInformation] File C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\db1.mdf already exists on JUMPBOX and WithReplace not specified, cannot restore
WARNING: [21:11:03][Test-DbaBackupInformation] File C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\db1_log.ldf already exists on JUMPBOX and WithReplace not specified, cannot restore
WARNING: [21:11:03][Restore-DbaDatabase] Database db1 failed testing, skipping
It even tells you 1 way you could fix it, by using the -WithReplace
switch. While that may fix this error, it could also mean you wipe out a database you’d really rather not have
Database filename conflict
If there’s a file name conflict, you’ll see the file scan progress bar, but then the restore will stop and display this error message:
WARNING: [21:15:55][Test-DbaBackupInformation] File C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\db1.mdf already exists on JUMPBOX and owned by another database, cannot restore
WARNING: [21:15:55][Test-DbaBackupInformation] File C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\db1_log.ldf already exists on JUMPBOX and owned by another database, cannot restore
WARNING: [21:15:55][Restore-DbaDatabase] Database db1 failed testing, skipping
In this case the only way to fix this is to rename or move the files as they restore. We’ll be covering this in tomorrow’s post as there’s quite a lot to cover
No backups in the folder
If there are no backups in the folder, or the SQL Server Instance can’t see then you’ll see this error message:
WARNING: [21:22:13][Restore-DbaDatabase] No backups passed through.
This could mean the SQL instance cannot see the referenced files, the file's headers could not be read or some other issue
Check that you've got the path to the backup folder correct. If the path is correct then check that the SQL Server instance has read access to it, and any files it contains.
Remember, just because you can see the files it doesn't mean the SQL Server instance can. There may be a network issue, or the Service Account may not have permissions.
Too Many files in the folder
If it takes forever to scan the backup file headers then there’s a chance you’re pushing too many files in. A quick way to fix this, is to use Get-ChildItem
with a filter to pass in a limited set of files:
Get-ChildItem c:\backups\localhost\db1 -File | Where-Object {$_.CreationTime -gt (Get-Date).AddDays(-1)} | Restore-DbaDatabase -SqlInstance localhost
This will only attempt to scan all the backup files created in the last 7 days.
Restore-DbaDatabase dry runs
If you want to do a dry run to see what’s happening, then the -OutputScriptOnly
switch is for you:
Restore-DbaDatabase -SqlInstance localhost -Path c:\backups\localhost\db1 -OutputScriptOnly
Running this will produce the following T-SQL output:
RESTORE DATABASE [db1] FROM DISK = N'C:\backups\localhost\db1\db12.bak' WITH FILE = 1, MOVE N'db1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\db1.mdf', MOVE N'db1_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\db1_log.ldf', NORECOVERY, NOUNLOAD, STATS = 10
RESTORE DATABASE [db1] FROM DISK = N'C:\backups\localhost\db1\db1_22.bak' WITH FILE = 1, MOVE N'db1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\db1.mdf', MOVE N'db1_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\db1_log.ldf', NORECOVERY, NOUNLOAD, STATS = 10
RESTORE LOG [db1] FROM DISK = N'C:\backups\localhost\db1\db1_23.trn' WITH FILE = 1, MOVE N'db1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\db1.mdf', MOVE N'db1_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\db1_log.ldf', NOUNLOAD, STATS = 10
Which is restoring 1 Full backup, 1 Diff backup and then a log backup to get to the latest point in time.
How about if there’s backups from more than 1 database?
Once scenario we’ve not covered yet, becuase it’s not really an error, is what happens you give Restore-DbaDatabase a folder with backups from more than 1 database?
That’s easy. It will restore all of them! They’ll all be scanned, sorted, fitered and then restored on the target instance. We don’t even mind if they come from different SQL Server instances.
Now, what happens if you’re storing your backups in different folders? Let’s take this ascii art represenation of a backup folder, where backups are sp
c:\backups\Server
|-- Instance1
|-- db1
|-- Full
|-- Diff
|-- Log
|-- db2
|-- Full
|-- Diff
|-- Log
|-- Instance2
|-- db3
|-- Full
|-- Diff
|-- Log
If we run:
Restore-DbaDatabase -SqlInstance localhost -Path c:\backups\localhost\Instace1\db1\full
All we’ll do is restore the most recent full backup of db1, which is probably not what we want.
If we run:
Restore-DbaDatabase -SqlInstance localhost -Path c:\backups\localhost\Instace1\db1\
We won’t restore anything, as there are no backup files in that folder.
By default Restore-DbaDatabase doesn’t recurse down folders, to try and prevent scanning more files than are needed. To overcome this, we can use the DirectoryRecurse
switch like this:
Restore-DbaDatabase -SqlInstance localhost -Path c:\backups\localhost\Instace1\db1\ -DirectoryRecurse
Which causes Restore-DbaDatabase to recurse into ALL folders under that folder. So we’ll scan the Full, Diff and Log folders or backup files, which will allow us to restore db1 to the most recent point in time.
If we move the starting folder up one level:
Restore-DbaDatabase -SqlInstance localhost -Path c:\backups\localhost\Instace1\ -DirectoryRecurse
Then we’ll now recurse every folder under the Instance1 folder. So, we’ll be restoring every database under there to the latest point in time supported by the backup files.
One more level up:
Restore-DbaDatabase -SqlInstance localhost -Path c:\backups\localhost\ -DirectoryRecurse
And we’d be recursing the backup files of every database on every instance that’s being backed up to that folder. That may well take a long time to do!
This is why we don’t recurse by default, but do offer it as an option. Be careful of your starting path when using DirectoryRecurse or you may end up restoring a lot more than you think you are.
Conclusions
These are the simplest examples of running Restore-DbaDatabase, but already show that you can throw a folder of backups at it and it will just sort them out for you. Tomorrow we’ll look more at how we can rename database during the restore.
All posts in this series can be found at 31 Days of dbatools Backup and Restores
Leave a Reply