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.
Script execution
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.
If you want to ensure the success of your migrations, trust in our SQL Server Consulting service. You can contact us without obligation, on our contact page.
Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .
Follow GPS on LinkedIn