Hello everybody, today we are going to share a very useful script for server migrations and database moves. Sooner or later, database professionals have to deal with moving databases between servers. Preparing the script for one database can be straightforward, but it gets complicated if you want to move multiple databases. Therefore we are going to share with you a dynamic SQL Server restore script. This script will generate the code to move one database, 10, or 200.
Why do I need a dynamic SQL Server restore script?
If you are not in the database world, possibly never. Otherwise, it will be useful for several cases:
- Database movements between development and production environments
- Servers migrations
- SQL Server version changes
- Cloning databases on the same server
- Cloning databases on another server
- Move database to another instance
These are just a few examples, but I am sure it can help you in more situations.
What does the script actually do and how to run it?
This script takes care of preparing the backup and restore code for one or more databases, but does not execute it, so it can be run without fear in productive environments.
This script must be run on the “origin” server. This is because it dynamically generates the logical files of the databases. These logical database files should be the same for new databases. But it is very common to find names that are different from what they should be. This is because they are often renamed copies of old databases.
It is divided into two parts. The backup script and the restore script. The first part, the backup script, takes care of preparing the backup with compression. This would only require changing the backup paths.
The second part is responsible for generating the restore script. This script should be run on the target server or instance, never on the same instance. The restore script includes the REPLACE option, so it would replace the source database if run on the same instance.
For it to be generated correctly, it is necessary to activate in SSMS an option to keep the line breaks in the script, otherwise when pasting it, it would not work as it would be on the same line. WE must go to Tools –> Options –>
And check this option:
Once we have everything ready, we run it.
select ('--' + a.databasename + ' print '' ======================================== Making backup de ' + a.databasename + ' ======================================== '' Go BACKUP DATABASE ' + a.databasename + ' TO DISK=''F:\migracion' + a.databasename + '.bak'' WITH INIT, STATS = 10, COPY_ONLY, COMPRESSION GO') as script_backup, ('--' + a.databasename + ' print '' ======================================== Restoring ' + a.databasename + ' ======================================== '' Go USE master Go RESTORE DATABASE ' + a.databasename + ' FROM DISK = ''\\servidor_backup\migracion' + a.databasename + '.bak''' + ' WITH MOVE ''' + a.ficherodatos + ''' TO ''F:\data' + a.databasename + '.mdf'',' + ' MOVE ''' + b.ficherolog + ''' TO ''F:\log' + a.databasename + '_log.ldf'',' + 'STATS = 10, RECOVERY, REPLACE Go ') as script_restore from ( select db.name databasename, mf.name ficherodatos FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id where mf.database_id > 4 and type_desc='ROWS') a, (select db.name databasename, mf.name ficherolog FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id where mf.database_id > 4 and type_desc='LOG') b where a.databasename=b.databasename and a.databasename IN ('DB1', 'DB2', 'DB3', 'DB4') order by a.databasename
Before testing in production environments, we recommend running it in test environments to ensure a good result.
Follow GPS on LinkedIn