/* SCRIPT - CLONEDB.SQL DEVELOPED BY - ADARSH DATE - 27/4/2017
USAGE- USED TO CLONE A DATABASE FROM ONE SERVER TO ANOTHER. THIS IS A STORED PROCEDURED, THAT GETS CREATED IN MASTER DATABASE UNDER DBO SCHEMA. PROCEDURE NAME - CLONEDB PRE-REQUISITES - 1. A WORKING LINKED SERVER CONNECTION SHOULD BE CREATED TO THE DESTINATION/TARGET SERVER 2. A SHARE TO BE CREATED ON DESTINATION SERVER WHERE THE SQL INSTANCE'S BACKUPS GO BY DEFAULT 3. YOU HAVE ENOUGH PERMISSIONS TO RESTORE THE DATABASE
- PARAMETERS - @DB - NAME OF THE DATABASE THAT NEEDS TO BE CLONED TO ANOTHER SERVER @DEST_SERVER - IS THE DESTINATION OR TARGET SQL SERVER INSTANCE NAME @SHARE - IS A SHARE LOCATION WHERE YOU LIKE TO BACKUUP THE DATABASE TO. PREFERABLY CREATE THIS SHARE ON DESTINATION SERVER, THAT HAS ACCESS TO THE DESTINATION SQL SERVER INSTANCE'S SERVICE ACCOUNT
EXAMPLE USAGE: SOURCE: SOURCE_SERVER DESTINATION: DEST_SERVER
EXECUTE STORED PROCEDURE FROM SOURCE, I.E., SOURCE_SERVER_INSTANCE
EXEC MASTER.DBO.CLONEDB DBNAMETOCLONE, DEST_SERVER_INSTANCE, '\DEST_SERVER\SHARE';
IF THERE ARE MORE THEN 1 DATABASES TO BE CLONED, THEN, CALL THE STORED PROCEDURE IN LOOP USING THE CURSOR SCRIPT
--SCRIPT START DECLARE @DBNAME SYSNAME DECLARE @DEST_SERVER VARCHAR(100) DECLARE @SHARE VARCHAR(500) SET @DEST_SERVER = 'DEST_SERVER' --REPLACE THIS DESTINATION SERVERNAME WITH YOURS SET @SHARE = '\DEST_SERVER\BACKUPSHARE' --REPLACE THIS WITH THE SHARE YOU CREATED
DECLARE CLONECURSOR CURSOER FOR
SELECT NAME FROM SYS.DATABASES WHERE NAME IN ('DB1', 'DB2', ....., 'DBn') ORDER BY NAME --REPLACE THE DB NAMES WITH THE LIST OF WHAT YOU WANT TO CLONE
OPEN CLONECURSOR
FETCH NEXT FROM CLONECURSOR INTO @DBNAME
WHILE @@FETCH_STATUS=0
BEGIN
EXEC MASTER.DBO.CLONEDB @DBNAME, @DEST_SERVER, @SHARE
FETCH NEXT FROM CLONECURSOR INTO @DBNAME
END
CLOSE CLONECURSOR
DEALLOCATE CLONECURSOR
--SCRIPT END
*/
USE MASTER
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'CLONEDB', 'P' ) IS NOT NULL
DROP PROCEDURE CLONEDB;
GO
CREATE PROCEDURE DBO.CLONEDB @DB AS SYSNAME, @DEST_SERVER AS VARCHAR(25), @SHARE AS VARCHAR(200) AS
DECLARE @CMD nvarchar(max) DECLARE @DATAFILEPATH VARCHAR(500) DECLARE @LOGFILEPATH VARCHAR(500) DECLARE @TYPE VARCHAR(5) DECLARE @PHYSICALPATH Varchar(500) DECLARE @LOGICALNAME Varchar(255) DECLARE @PHYSICALFILENAME Varchar(max) DECLARE @DEST_SP nvarchar(100)
SET @DEST_SP = @DEST_SERVER + '.MASTER.DBO.SP_EXECUTESQL '
BEGIN TRY --INITIATE BACKUP OF THE DATABASE SELECT @CMD ='BACKUP DATABASE ['+@DB+'] TO DISK='''+@SHARE+''+@DB+'.BAK'' WITH INIT' --PRINT @CMD EXECUTE(@CMD)
--DATAFILE AND LOGFILE PATHS ARE HARD CODED TO YOUR REQUIREMENT IF(@DEST_SERVER = 'SERVER1') BEGIN SET @DATAFILEPATH='F:\MSSQL10_50.QA01\MSSQL\DATA' SET @LOGFILEPATH='G:\MSSQL10_50.QA01\MSSQL\Translog' END ELSE IF(@DEST_SERVER= 'SERVER2') BEGIN SET @DATAFILEPATH='J:\MSSQL10_50.QA02\MSSQL\DATA' SET @LOGFILEPATH='K:\MSSQL10_50.QA02\MSSQL\Translog' END ELSE BEGIN PRINT 'DESTINATION SERVER SHOULD BE EITHER SERVER1 OR SERVER2. THE SCRIPT IS HARDCODED TO USE DEFAULT DATA AND LOG PATHS OF THE TWO INSTANCES' RETURN 0 END CREATE TABLE #filelist ( LogicalName VARCHAR(255), PhysicalName VARCHAR(500), [Type] VARCHAR(1), FileGroupName VARCHAR(64), Size DECIMAL(20, 0), MaxSize DECIMAL(25,0), FileID bigint, CreateLSN DECIMAL(25,0), DropLSN DECIMAL(25,0), UniqueID UNIQUEIDENTIFIER, ReadOnlyLSN DECIMAL(25,0), ReadWriteLSN DECIMAL(25,0), BackupSizeInBytes DECIMAL(25,0), SourceBlockSize INT, filegroupid INT, loggroupguid UNIQUEIDENTIFIER, differentialbaseLSN DECIMAL(25,0), differentialbaseGUID UNIQUEIDENTIFIER, isreadonly BIT, ispresent BIT , TDEThumbprint Varchar(255) )
SET @CMD = 'RESTORE FILELISTONLY FROM DISK= '''+ @SHARE +''+@DB+'.bak''' INSERT INTO #filelist exec (@CMD)
SET @CMD = 'RESTORE DATABASE ['+ @DB +'] FROM DISK = '''+ @SHARE+''+@DB+'.bak'' WITH STATS = 10 , RECOVERY, '
DECLARE file_list cursor for
SELECT LogicalName, PhysicalName, Type FROM #filelist ORDER BY type
OPEN file_list
FETCH NEXT FROM file_list into @LogicalName, @PhysicalFileName, @type
WHILE @@fetch_status = 0
BEGIN
-- If it is data file move to data file location.
IF @TYPE = 'D'
SET @CMD = @CMD + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @DATAFILEPATH + Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + ''','
ELSE
-- Log files move to log file location.
SET @cmd = @cmd + ' MOVE ''' + @LogicalName + '''' + ' TO ''' + @LOGFILEPATH + Substring(@PhysicalFileName , LEN(@PhysicalFileName)-CHARINDEX('\' , REVERSE(@PhysicalFileName))+2 , CHARINDEX('\' , REVERSE(@PhysicalFileName))) + ''''
FETCH NEXT FROM file_list into @LogicalName, @PhysicalFileName, @type
END
CLOSE file_list
DEALLOCATE file_list
EXEC @DEST_SP @CMD
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;