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?
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 :
- Detach the data source, duplicate the.mdf documents and also re-attach.
- 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.
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' */ SET NOCOUNT ON; Select @query = 'Create Database ' + @NewDB exec(@query) 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 + '''''''' + '', NOUNLOAD, REPLACE, STATS = 10'' exec(@restQuery) --print @restQuery' exec(@query)