Part 2 -- Restore a database to another instance...

,

restore_databases_to_another_instance_sp_generic should be created within the source instance of the database that is being backed up and restored. It needs to be created in conjunction with the kill_users_other_than_system_sp procedure in Part 1, which should be created within the destination instance for the restore. The process relies on a pre-seeded backup having been restored to the destination from the source, so that subsequent backups can be overwritten at regular, scheduled intervals using the SQL Agent.

You will need to create a linked server within the source instance, that links, with the appropriate permissions to the source instance, so that the kill sproc can be run remotely before the restore is initiated across the network. An example linked server creation script can be found here...

---- Linked server : --sp_addlinkedserver '','SQL Server' --go --sp_addlinkedsrvlogin '','false',null,'','' --go --sp_serveroption '','collation compatible','off' --go --sp_serveroption '','data access','on' --go --sp_serveroption '','rpc','on' --go --sp_serveroption '','rpc out','on' --go --sp_serveroption '','use remote collation','on' --go --sp_serveroption '','collation name',null --go --sp_serveroption '','connect timeout',0 --go --sp_serveroption '','query timeout','7200' --go ... the name that you give this linked server MUST be referenced in the restore_databases_to_another_instance_sp_generic sproc on the source instance, and so is currently denoted in the attached script as []!

--############################################################################################################################
--
--This script is being offered for public use and as such is being offered as untested and unverified.
--Please use this script at your own risk, as I take NO responsibility for it's use elsewhere in environments 
--that are NOT under my control. 
--Redistribution or sale of restore_databases_to_another_instance_sp_generic, in whole or in part, is prohibited! 
 
--Always ensure that you run such scripts in test prior to production and perform due diligence as to whether they meet yours, 
--or your company needs!
--
-- you will need to replace any item enclosed in <> with your own values!
--
--############################################################################################################################

USE [<your admin DB>]
GO
/****** Object:  StoredProcedure [<your admin schema>].[restore_databases_to_another_instance_sp_generic]    Script Date: 01/03/2012 16:10:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [<your admin schema>].[restore_databases_to_another_instance_sp_generic]

--#############################################################
--
-- Author	: Haden Kingsland
-- Date		: 01/09/2010
-- Version	: 01:00
--
-- Desc		:	To restore databases to another instance from a given shared backup area
--				Both the destination file name (source location for the backup), and a string of 
--				databases to restore / look for to restore are passed in as paramaters ... examples follow ...
--				The procedure is hard coded to include the [<your linked server name>] linked server which runs under the "sa"
--				account.
--
-- SET @DestFileName	='\\myshare\folder\' -- UNC path to your source backup source
-- set @databases		= 'adatabasename' -- your database name to restore
--
-- ############################
-- EXAMPLE SQL AGENT JOB CALL
-- ############################
--
--DECLARE @RC				int
--DECLARE @DestFileName		nvarchar(1000)
--DECLARE @databases		nvarchar(1000)
--DECLARE @job_name			VARCHAR(256)
--DECLARE @destDataDir		VARCHAR(512)
--DECLARE @destLogDir		VARCHAR(512)

-- select @destDataDir		= 'F:\SQLDATA\data' -- for the restore with move for data files
-- select @destLogDir		= 'E:\SQLLOGS\Logs' -- for the restore with move for log files
-- select @job_name			= 'Restore <your database name> databases to another instance' -- whaever you want the job called
-- select @databases		=  'adatabasename' -- your database name to restore
-- select @DestFileName		= '\\myshare\folder\' -- your source backup file location

-- EXECUTE @RC = [<your admin DB>].[<your admin schema>].[restore_databases_to_another_instance_sp_generic_DEV] 
-- @DestFileName,
-- @databases,
-- @job_name,
-- @destDataDir,
-- @destLogDir
--	
-- ##################################################################################################################################
-- Create your linked server on the source database instance, where you are backing up the database to restore to another instance!
-- ##################################################################################################################################

-- ###########################################################
-- the below linked server MUST exist at the source instance
-- ###########################################################
--
----  Linked server : <your linked server name>
--sp_addlinkedserver '<your linked server name>','SQL Server'
--go
--sp_addlinkedsrvlogin '<your linked server name>','false',null,'<your login credentials>','<password here>'
--go
--sp_serveroption '<your linked server name>','collation compatible','off'
--go
--sp_serveroption '<your linked server name>','data access','on'
--go
--sp_serveroption '<your linked server name>','rpc','on'
--go
--sp_serveroption '<your linked server name>','rpc out','on'
--go
--sp_serveroption '<your linked server name>','use remote collation','on'
--go
--sp_serveroption '<your linked server name>','collation name',null
--go
--sp_serveroption '<your linked server name>','connect timeout',0
--go
--sp_serveroption '<your linked server name>','query timeout','7200'
--go
--
----#############################################################
--
-----------------------
-- Modification History
-----------------------
-- 08/09/2010 -- Haden Kingsland -- Added a call to ...
--									exec [<linked server>].[<your admin DB>].[<your admin schema>].[kill_users_other_than_system_sp] @dbname, @p_error_description output
--									This sp MUST exist in the receiving instance in the MASTER DB as a remote call is made to it.
--
-- 14/09/2010 -- Haden Kingsland -- Added generic restore functionality, to enable any database passed in the @databases parameter to be 
--									restored to the default data and log paths of the receiving instance, thus removing any hard coding.
--
-- 17/11/2011 -- Haden Kingsland -- Enhanced for %%%%%%%, as backups do not go to a central share, and reside on a local path on the
--									source server instead. To overcome this we need to strip out the backup file name, and concatenate it
--									with the passed in UNC path given to the backup share so all servers in the loop can see it. 
--									Also added command to make restored database read only, as it should not be updated.
--
--#################################################################
 (
     @DestFileName			nvarchar(1000),
     @databases				nvarchar(1000),
     @job_name				VARCHAR(256),
     @destDataDir			VARCHAR(512),
	 @destLogDir			VARCHAR(512)
     --@linked_server			varchar(20)
   -- @p_error_description				varchar(300) OUTPUT -- if a called procedure
  )

as

BEGIN

DECLARE			@SQL VARCHAR(7000),
				@SQL2 VARCHAR(7000),
				@SQL3 VARCHAR(7000),
				@SQL4 VARCHAR(7000),
				@SQL5 VARCHAR(7000),
				@SQL6 VARCHAR(7000),
				@use varchar(50),
				@DestinationFileName nvarchar(1000),
				@DBName SYSNAME,
				@BkpFileName NVARCHAR(260),
				@RowCnt INT,
				@MailProfileName VARCHAR(50),		
				@COMMAND varchar(800),
				@ERR_MESSAGE varchar(200),
				@ERR_NUM int,
				@MESSAGE_BODY varchar(2000),
				@MESSAGE_BODY_OPS varchar(1000),
				@MESSAGE_BODY2 varchar(1000),
				@MESSAGE_BODY3 varchar(1000),
				@ErrorSeverity int,
				@ErrorState int,
				@p_error_description varchar(300),
				@DELAYLENGTH			char(9),
				@DefaultFile nvarchar(512),
				@DefaultLog nvarchar(512),
				@name varchar(80),
				@file_id int,
				@extension varchar(8)

if @databases <> ''
Begin
	SET @databases = ',' + @databases + ','
end

SELECT @MailProfileName = name
	FROM msdb.dbo.sysmail_profile WITH (NOLOCK)
	WHERE name like '%<your profile name>%'

SELECT bs.database_name AS DatabaseName, 
bms.physical_device_name AS FullBackupName
INTO #Backups
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms 
ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s 
ON bs.database_name = s.name
where bs.backup_finish_date = 
	(select MAX(bs1.backup_finish_date)
	FROM msdb.dbo.backupset bs1
	INNER JOIN master.dbo.sysdatabases s1
	ON bs1.database_name = s1.name
	where s.name = s1.name
	and  bs1.type = 'D')
AND bs.type = 'D' and 
(CHARINDEX(',' + bs.database_name + ',' , @databases) > 0) -- check whether databases are in the list of those passed in
GROUP BY bs.database_name, bms.physical_device_name

-- Differential

--SELECT bs.database_name AS DatabaseName, 
--bms.physical_device_name AS FullBackupName
----INTO #Backups
--FROM msdb.dbo.backupset bs
--INNER JOIN msdb.dbo.backupmediafamily bms 
--ON bs.media_set_id = bms.media_set_id
--INNER JOIN master.dbo.sysdatabases s 
--ON bs.database_name = s.name
--where bs.backup_finish_date = 
--	(select MAX(bs1.backup_finish_date)
--	FROM msdb.dbo.backupset bs1
--	INNER JOIN master.dbo.sysdatabases s1
--	ON bs1.database_name = s1.name
--	where s.name = s1.name
--	and  bs1.type = 'I')
--AND bs.type = 'I' and 
--(CHARINDEX(',' + bs.database_name + ',' , @databases) > 0) -- check whether databases are in the list of those passed in
--GROUP BY bs.database_name, bms.physical_device_name

--select * from  #Backups

SET @RowCnt = @@ROWCOUNT 
set @DELAYLENGTH = '000:00:05' -- wait for 5 seconds

-- get default directories for instance of SQL Server for install areas if no restore directories are passed into the procedure.
-- Directories must be passed in without the trailing "\" as this is added later in the procedure!

IF (@destDataDir <> ''
and @destLogDir <> '')
	BEGIN
		set @DefaultFile = @destDataDir
		set @DefaultLog  = @destLogDir
	END
ELSE
	BEGIN
		exec [<your linked server name>].master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefaultFile OUTPUT
		exec [<your linked server name>].master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultLog OUTPUT
		SELECT ISNULL(@DefaultFile,N'') AS [DefaultFile], ISNULL(@DefaultLog,N'') AS [DefaultLog]
	END

-- start the outer loop

WHILE @RowCnt > 0
BEGIN

	SELECT  TOP 1 @DBName = DatabaseName, @BkpFileName = FullBackupName
	FROM #Backups
	ORDER BY DatabaseName

	SET @RowCnt = @@ROWCOUNT

	IF @RowCnt > 0
	BEGIN
	
		-- do this to remove each row as read from the temporary table, so always get next row from the previous top clause
	
		DELETE FROM #Backups
		WHERE DatabaseName = @DBName

-- ##########################################################################################
-- Remove only if the backup location and restore locations are now on the same share.
-- Use this command if the backup location retrived from the earlier query is NOT a UNC path
-- and is on a local drive. This command will append the backup file name only, to the 
-- passed in @DestinationFileName location, which will be a UNC path to a share that 
-- all servers in this process can see.
-- ##########################################################################################
		SET @DestinationFileName = @DestFileName + 
			SUBSTRING(@BkpFileName, LEN(@BkpFileName) - CHARINDEX('\', REVERSE(@BkpFileName)) + 2, 1000)

-- ##########################################################################################
-- Removed as the backup location and restore locations are now NOT on the same share.
-- only use this command IF the backups are on a UNC share, and this UNC path is returned
-- in the initial check for backups at the start of this procedure.
-- ##########################################################################################

		--set @DestinationFileName = @BkpFileName

-- ##########################################################################################

-- build up restore command string using the default instance file locations

		SET @Sql = 
		'RESTORE DATABASE [' + @DBName + '] ' + 
		'FROM DISK = ''' + @DestinationFileName + ''' ' +
		'WITH REPLACE, '
		
-- loop through the sys.master_files view to get all original file names for the database being restored to 
-- help build up the restore string
		
		select name, file_id
		into #dbfiles
		from sys.master_files
		where DB_NAME(database_id) = @DBName
		order by file_id

		--select * from #dbfiles

		DECLARE restore_db CURSOR 
		FOR 
		SELECT * FROM #dbfiles

		-- Open the cursor.
		OPEN restore_db;

		-- Loop through the partitions.
		FETCH NEXT
		  FROM restore_db
		  INTO @name, @file_id;

		WHILE @@FETCH_STATUS <> -1 -- Stop when the FETCH statement failed or the row is beyond the result set
		BEGIN

			IF @@FETCH_STATUS = 0 -- to ignore -2 status "The row fetched is missing"
			BEGIN

				IF @name not like '%Log%'
				BEGIN
					if @file_id > 1
					BEGIN
						SET @extension = '.ndf'', '
					END
					ELSE
						IF @file_id = 1
						BEGIN
							SET @extension = '.mdf'', '
						END

				SET @Sql = @sql + ' MOVE ''' + @name + ''' TO ''' + @DefaultFile + '\' + @name + @extension

				END
				ELSE
					BEGIN
						SET @Sql = @sql +
						'MOVE ''' + @name + ''' TO ''' + @DefaultLog + '\' + @name + '.ldf'', '
					END	

				FETCH NEXT
				  FROM restore_db
				  INTO @name, @file_id;
			END
		END
		
		CLOSE restore_db;
		DEALLOCATE restore_db;

		DROP TABLE #dbfiles;
	
		-- remove the last comma once you have finished building the restore string
	
		set @Sql = Left(@Sql,Len(@Sql)-1)
		
		BEGIN TRY

-- ######################################################################
-- Kill all users on receiving side of the restore
-- ######################################################################
-- ######################################################################
-- LINKED SERVER [%%%%%%%%%%%%] MUST EXIST WITHIN THE SOURCE INSTANCE
-- ######################################################################

		exec [<your linked server name>].[<your admin DB>].[<your admin schema>].[kill_users_other_than_system_sp] @dbname, @p_error_description output
		
		WAITFOR DELAY @DELAYLENGTH
		
		IF (@p_error_description <> '' or @p_error_description IS NOT NULL)
			BEGIN
				set @ERR_MESSAGE = @p_error_description
				-- using RAISERROR forces the execution to the CATCH block for this iteration of the loop
				RAISERROR (
					'The remote call to <your admin DB>.<your admin schema>.kill_users_other_than_system_sp failed ', -- Message text.
					16, -- Severity.
					1 -- State.
               );
               
			END
		ELSE
			BEGIN

				-- put databases in single user mode prior to the restore
				set @SQL2 = 'alter database ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
				exec (@SQL2) AT [<your linked server name>]
				
				WAITFOR DELAY @DELAYLENGTH -- then wait for 2 seconds in case of any outstanding locks form previous command
				
				-- then restore the databases at the linked server site
				EXEC (@SQL) AT [<your linked server name>]-- linked server to <your linked server name>.
				
				-- put databases back in multi user mode once restore in complete
				set @SQL3 = 'ALTER DATABASE ' + @dbname + ' SET MULTI_USER'
				exec (@SQL3) AT [<your linked server name>] 
				
				set @SQL4 = 'alter database ' + @dbname + ' set recovery SIMPLE with no_wait'
				exec (@SQL4) AT [<your linked server name>]
				
				-- change the database owner to be "sa" before putting it into read only mode
				set @SQL6 = 'exec [' + @DBName + '].dbo.sp_changedbowner @loginame = N''sa'', @map = false'
				-- print @sql6
				exec (@SQL6) AT [<your linked server name>]
			END

		END TRY
		
		BEGIN CATCH
			
			-- check whether the @ERR_MESSAGE parameter already has a value from an earlier error
			If @ERR_MESSAGE <> ''
			BEGIN
				select @ERR_NUM = ERROR_NUMBER();
			END
			ELSE
				BEGIN
					SELECT @ERR_MESSAGE = ERROR_MESSAGE(), @ERR_NUM = ERROR_NUMBER();
				END
				
			IF @ERR_NUM in (5061,3101)	-- Error Message ALTER DATABASE failed because a lock could not be placed on database
										-- Error Message Exclusive access could not be obtained because the database is in use.
				BEGIN
					set @SQL3 = 'ALTER DATABASE ' + @dbname + ' SET MULTI_USER'
					exec (@SQL3) AT [<your linked server name>] 
				
					set @SQL2 = 'alter database ' + @dbname + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
					exec (@SQL2) AT [<your linked server name>]
				
					WAITFOR DELAY @DELAYLENGTH -- then wait for 2 seconds in case of any outstanding locks form previous command
				
				-- then restore the databases at the linked server site
				EXEC (@SQL) AT [<your linked server name>]-- linked server to <your linked server name>.
				
				-- put databases back in multi user mode once restore in complete
				set @SQL3 = 'ALTER DATABASE ' + @dbname + ' SET MULTI_USER'
				exec (@SQL3) AT [<your linked server name>] 
				
				set @SQL4 = 'alter database ' + @dbname + ' set recovery SIMPLE with no_wait'
				exec (@SQL4) AT [<your linked server name>]
				
				-- change the database owner to be "sa" before putting it into read only mode
				set @SQL6 = 'exec [' + @DBName + '].dbo.sp_changedbowner @loginame = N''sa'', @map = false'
				exec (@SQL6) AT [<your linked server name>]
				
				END
			ELSE 
				IF @ERR_NUM NOT IN (5061,3101) 
					BEGIN
				
						IF @ERR_NUM = (3201) -- Description from sys.sysmessages is ... Cannot open backup device '%ls'. Operating system error %ls.
						BEGIN
							set @SQL3 = 'ALTER DATABASE ' + @dbname + ' SET MULTI_USER'
							exec (@SQL3) AT [<your linked server name>] 
							
							set @SQL4 = 'alter database ' + @dbname + ' set recovery SIMPLE with no_wait'
							exec (@SQL4) AT [<your linked server name>]
							
							-- change the database owner to be "sa" before putting it into read only mode
							set @SQL6 = 'exec [' + @DBName + '].dbo.sp_changedbowner @loginame = N''sa'', @map = false'
							exec (@SQL6) AT [<your linked server name>]
								
						END
					
						SET @MESSAGE_BODY='Error restoring' + @DestinationFileName + ' to [<your linked server name>] ' + '. Error Code ' + RTRIM(CONVERT(CHAR(10),@ERR_NUM)) + ' Error Message ' + @ERR_MESSAGE
						SET @MESSAGE_BODY2='Failure of ' + @job_name + ' within ' + LTRIM(RTRIM(cast(@@SERVERNAME as VARCHAR(30)))) 

						EXEC msdb.dbo.sp_notify_operator 
							@profile_name = @MailProfileName, 
							@name=N'<your operator name>',
							@subject = @MESSAGE_BODY2, 
							@body= @MESSAGE_BODY
					END
		END CATCH

	END
END

DROP TABLE #Backups

END

Rate

Share

Share

Rate