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

--############################################################################################################################
--
--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 @RCint
--DECLARE @DestFileNamenvarchar(1000)
--DECLARE @databasesnvarchar(1000)
--DECLARE @job_nameVARCHAR(256)
--DECLARE @destDataDirVARCHAR(512)
--DECLARE @destLogDirVARCHAR(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.
--
--#################################################################
 (
     @DestFileNamenvarchar(1000),
     @databasesnvarchar(1000),
     @job_nameVARCHAR(256),
     @destDataDirVARCHAR(512),
 @destLogDirVARCHAR(512)
     --@linked_servervarchar(20)
   -- @p_error_descriptionvarchar(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),
@DELAYLENGTHchar(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