Renaming Databases during restore
Yesterday we looked at the simplest of options for restoring SQL Server backups with Restore-DbaDatabase. We saw that it’s simple and easy to restore a single database or as many as you want to.
But what happens if you want to restore a database with a different name? Maybe you want to restore Production as Development so it’s obvious which database is which (hands up all those who’ve not been sure which database named Production is really development (no lying, it’ll be most of you.))
As it’s such a common request of course we built it in and made it as simple as possible.
There are 2 ways we can rename databases:
- For a single database restore
- For batch database restores
The reason for the split, is that when you’re restoring 20 databases in one batch there isn’t an easy way to work out which one you want to rename as ‘MyNewDB’.
Renaming a Single Database
Lets say you’re restoring ‘ProductionDatabase’ and you want it be called ‘DevelopmentDatabase’. That’s simply a case of providing the DatabaseName switch:
Restore-DbaDatabase -SqlInstance server1\instance -Path \\server\backups\ProductionDatabase -DatabaseName DevelopmentDatabase
Restore-DbaDatabase will scan the files in ‘\\server\backups\ProductionDatabase’, calculate which backups are needed to get to the most recent point in time and then restore them as a database called DevelopmentDatabase. Super simple
Renaming multiple databases
On day 10 we looked at using Restore-DbaDatabase to restore a batch of databases at once by recursing backup folders
As it would be hard to come up with a way to rename an unknown number of databases in an easy and consistent way, we offer you RestoredDatabaseNamePrefix to prevent name conflicts
If we modify our first example to:
Restore-DbaDatabase -SqlInstance server1\instance -Path \\server\backups\ -DirectoryRecurse
We are now going to restore every database with backups in that folder, not just ProductionDatabase as we were in the first example.
To avoid database name collisions we’ll prefix them all with ‘Restored’:
Restore-DbaDatabase -SqlInstance server1\instance -Path \\server\backups\ -DirectoryRecurse -RestoredDatabaseNamePrefix Restored
Now all databases restored in this batch will have their name prefixed with Restored.
If you want to do any further renaming, then the
Rename-DbaDatabase function is your friend and offers you lots of options to tailor your renaming
Avoiding a database name collision during a restore is a great way to avoid an error message, so we’ve provided two options to rename the databases on the fly.
All posts in this series can be found at 31 Days of dbatools Backup and Restores