Migrate BBDD from SQL to another SQL

Hi!

Today I’ll post about a script for SQL, this script helps me to migrate all the BBDD from one SQL to another SQL server.

SET NOCOUNT ON;

Declare
–Rutas en el servidor de origen
@path_data_Files_source as nvarchar(500) = ‘C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA’,
@path_log_files_source as nvarchar(500) = ‘C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA’,

–Rutas en el servidor de destino
@path_data_Files_destination as nvarchar(500) = ‘U:SQL ServerMSSQL12.MSSQLSERVERMSSQLData’,
@path_log_files_destination as nvarchar(500) = ‘V:SQL ServerMSSQL12.MSSQLSERVERMSSQLData’

Select
‘EXEC sp_attach_db @dbname = N”[‘ + data_Files.database_name + ‘]”, @filename1 = N”’ + data_Files.file_path + ”’, @filename2 = N”’ + log_Files.file_path + ””
–data_Files.database_name, data_Files.file_path, log_files.file_path
From
(
select d.database_id, REPLACE(f.physical_name, @path_data_Files_source, @path_data_Files_destination) as file_path, d.name as database_name, physical_name
from sys.databases as d
inner join sys.master_files as f on ( d.database_id = f.database_id)
WHERE not d.name in ( ‘MSDB’,’MASTER’, ‘MODEL’,’TEMPDB’, ‘Distribution’)
and f.type = 0 –ROWS
) AS data_Files
Inner Join (
select d.database_id, REPLACE(f.physical_name, @path_log_files_source, @path_log_files_destination) as file_path, d.name as database_name, physical_name
from sys.databases as d
inner join sys.master_files as f on ( d.database_id = f.database_id)
WHERE not d.name in ( ‘MSDB’,’MASTER’, ‘MODEL’,’TEMPDB’,’Distribution’)
and f.type = 1 –LOG
) as log_files on (data_Files.database_id = log_files.database_id)
ORDER BY data_Files.database_name

Hope it helps someone

Advertisement