What is the most effective means to replicate a data source?

When I intend to make a duplicate of a data source, I constantly create a new vacant data source, and afterwards recover a back-up of the existing data source right into it. Nonetheless, I'm asking yourself if this is actually the least error-prone, the very least difficult, and also the majority of reliable means to do this?

2019-05-06 02:00:18
Source Share
Answers: 4

It is feasible to miss the action of developing the vacant data source. You can create the new data source as component of the recover procedure.

This is in fact the most convenient and also ideal means I recognize of to duplicate a data source. You can remove mistakes by scripting the back-up and also recover procedure as opposed to running it via the SQL Server Management Studio

There are 2 various other alternatives you can discover :

  1. Detach the data source, duplicate the.mdf documents and also re-attach.
  2. Usage SQL Server Integration Services (SSIS ) to replicate all the things over

I recommend sticking to back-up and also recover and also automating if essential.

2019-05-07 21:41:59

Here's a vibrant sql manuscript I've made use of in the past. It can be more changed yet it will certainly offer you the essentials. I favor scripting it to stay clear of the blunders you can use the Management Studio :

Declare @OldDB varchar(100)
Declare @NewDB varchar(100)
Declare @vchBackupPath varchar(255)
Declare @query varchar(8000)

/*Test code to implement 
Select @OldDB = 'Pubs'
Select @NewDB = 'Pubs2'
Select @vchBackupPath = '\\dbserver\C$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\pubs.bak'


Select @query = 'Create Database ' + @NewDB

Select @query = '
Declare @vBAKPath varchar(256)
declare @oldMDFName varchar(100)
declare @oldLDFName varchar(100)
declare @newMDFPath varchar(100)
declare @newLDFPath varchar(100)
declare @restQuery varchar(800)

select @vBAKPath = ''' + @vchBackupPath + '''
select @oldLDFName = name from ' + @OldDB +'.dbo.sysfiles where filename like ''%.ldf%''
select @oldMDFName = name from  ' + @OldDB +'.dbo.sysfiles where filename like ''%.mdf%''
select @newMDFPath = physical_name from ' + @NewDB +'.sys.database_files where type_desc = ''ROWS''
select @newLDFPath = physical_name from ' + @NewDB +'.sys.database_files where type_desc = ''LOG''

select @restQuery = ''RESTORE DATABASE ' + @NewDB + 
' FROM DISK = N'' + '''''''' + @vBAKpath + '''''''' + 
'' WITH MOVE N'' + '''''''' + @oldMDFName + '''''''' +  
'' TO N'' + '''''''' + @newMDFPath + '''''''' +  
'', MOVE N'' + '''''''' + @oldLDFName + '''''''' +  
'' TO N'' + '''''''' + @newLDFPath + '''''''' +  

--print @restQuery'


2019-05-07 21:24:10

The Publish to Provider capability has actually functioned wonderful for me. See Scott Gu's Blog Entry.

If you require something actually durable consider redgate software program's devices here ... if you are doing much SQL in all, these deserve the $$.

2019-05-07 18:14:51

Backup and also Restore is one of the most straight-forward means I recognize. You need to take care in between web servers as protection qualifications do not featured the recovered database.

2019-05-07 17:58:43