May 26, 2010 at 10:39 pm
Hi all,
We are setting up a database with a manual (synchronus) mirror to a remote site. From time to time the project wants to 'roll over' this database (ie - archive the existing and mirror the new database).
At present it'd be a manual process - ie stop mirroring on old DB, create new Db, copy backup of new DB to remote site, Mirror new DB. This would cause us an outage whilst we are syncing the DBs.
My question is - can this be automated?? Has anyone else had to deal with this scenario?
Many thanks in advance.
Moss
June 2, 2010 at 10:17 am
I apologize for not being able to explain in detail how to accomplish this, but it seems like you might be able to do it with SSIS.
Create a package that contains multiple steps.
1. create database
2. backup database
3. restore backups to mirror server
4. establish mirroring
Again, sorry for not being able to go into great detail. This would be something I would look into if I were in your shoes.
June 2, 2010 at 10:31 am
copy only backup?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 2, 2010 at 7:43 pm
Thanks for the advice guys, appreciated. I am thinking SSIS is the way to go as advised. Will let you know how it turns out. Cheers.
June 3, 2010 at 2:00 am
Hi Moss
Try these three sp's
ABC_Mirror_Create
ABC_Mirror_Sync
ABC_Apply_TransactionLogs
They will allow you to backup and restore databases from one server to another and then start the mirroring.
We use these to Synchronize whole servers (up to 40 db's) before we go home at night and come in the morning and every thing is done. Easy!!
USE [ABCDBA]
GO
/****** Object: StoredProcedure [dbo].[ABC_Mirror_Create] Script Date: 06/03/2010 19:51:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[ABC_Mirror_Create]
@database varchar(255),
@primary_instance varchar(255),
@partner_instance varchar(255),
@portno int,
@sync int=0
AS
DECLARE @this_servervarchar(255)
, @partner_servervarchar(255)
, @primary_servervarchar(255)
, @domainvarchar(255)
, @cmdvarchar(8000)
, @ncmdnvarchar(4000)
, @BakNodeVARCHAR(500)
, @TargetDirvarchar(255)
, @BakNodeShareName varchar(255)
-- machinename
SELECT @this_server=convert(varchar,serverproperty('ComputerNamePhysicalNetBIOS'))
SELECT @partner_server=substring(CONVERT(VARCHAR,@partner_instance),1,PATINDEX('%\%', CONVERT(VARCHAR,@partner_instance))-1)
SELECT @primary_server=substring(CONVERT(VARCHAR,@primary_instance),1,PATINDEX('%\%', CONVERT(VARCHAR,@primary_instance))-1)
-- domain
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'
,N'SYSTEM\ControlSet001\Services\Tcpip\Parameters\'
,@value_name='Domain',@value=@domain OUTPUT
--Each database mirroring endpoint listens on a unique TCP port number
--make sure the proposed port does not exists if no endport exists.
--results should return no rows.
/**
IF NOT EXISTS(select * from sys.database_mirroring_endpoints where name = 'Mirroring')
BEGIN
exec xp_cmdshell 'netstat -o -n -a | findstr 5023'
/** create endpoint **/
--CREATE ENDPOINT [Mirroring]
--AS TCP (LISTENER_PORT = 5022)
--FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = REQUIRED ALGORITHM RC4);
--ALTER ENDPOINT [Mirroring] STATE = STARTED;
END
**/
IF @sync = 1
BEGIN
EXEC ABC_Mirror_Sync @database, @primary_instance, @partner_instance
END
--Initiate the mirroring on The Mirror server:
IF @partner_server = @this_server
BEGIN
-- APPLY TRANSACTION LOGS.
EXEC [ABC_Apply_TransactionLogs] @database, @primary_instance
SELECT @ncmd = 'ALTER DATABASE ' + char(91) + @database + char(93) + ' SET PARTNER= N'+ char(39) + 'TCP://' + @primary_server + '.' + @domain + ':' + convert(varchar,@portno) + char(39)
EXEC dbo.sp_executesql @ncmd;
END
--Initiate the mirroring on The Primary server:
IF @primary_server = @this_server
BEGIN
EXEC ABC_WhichBakNode @BakNode OUTPUT
EXEC ABC_WhichBakNodeShare @BakNodeShareName OUTPUT
SELECT @TargetDir = @BakNode + @BakNodeShareName
--perform transaction log backup
EXEC ABC_DoBackups
@BackupType='L'
,@dbname=@database
,@BackupDir = @TargetDir
,@BackupProduct = 2,@DoVerify = 0
,@Debug = 0,@EncryptionKey = NULL
,@SLSThreads = NULL,@SLSThrottle = 85
,@SLSAffinity = 0,@SLSPriority = NULL
,@RetainDays = NULL,@InitBackupDevice = 0
,@PerformDBCC = 0,@ExcludedDBs = NULL
,@CreateSubDir = 1,@CreateSrvDir = 0
,@Files = NULL,@FileGrps = NULL
SET @cmd = 'sqlcmd.exe -S "' + rtrim(@partner_instance) + '" -d ABCDBA -E -Q "EXEC dbo.ABC_Mirror_Create ' + QUOTENAME(@database,'''') + ',' + QUOTENAME(@primary_instance,'''') + ',' + + QUOTENAME(@partner_instance,'''') + ',' + convert(varchar,@portno) + ',' + convert(varchar,@sync) + ';"';
EXEC master.dbo.xp_cmdshell @cmd;
SELECT @ncmd = 'ALTER DATABASE ' + char(91) + @database + char(93) + ' SET PARTNER= N'+ char(39) + 'TCP://' + @partner_server + '.' + @domain + ':' + convert(varchar,@portno) + char(39)
EXEC dbo.sp_executesql @ncmd;
--ABC Standard: High Performance Mode (asynchronous)
SELECT @ncmd = N'USE master; ALTER DATABASE ' + char(91) + @database + char(93) + ' SET SAFETY OFF;'
EXEC dbo.sp_executesql @ncmd;
-- Display Summary
PRINT N'On the principal server instance, ' + CONVERT(VARCHAR,SERVERPROPERTY('servername'))
PRINT N'This Modify the following properties of the mirroring endpoint:'
PRINT N'Name: Mirroring'
PRINT N'Listener Port: ' + convert(varchar,@portno)
PRINT N'Encryption: Yes'
PRINT N'Role: Partner'
PRINT N'On the mirror server instance, ' + @partner_instance
PRINT N'Create the mirroring endpoint with the following properties:'
PRINT N'Name: Mirroring'
PRINT N'Listener Port: ' + convert(varchar,@portno)
PRINT N'Encryption: Yes'
PRINT N'Role: Partner'
END
GO
USE [ABCDBA]
GO
/****** Object: StoredProcedure [dbo].[ABC_Mirror_Sync] Script Date: 06/03/2010 19:53:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[ABC_Mirror_Sync]
@database varchar(255),
@primary_instance varchar(255),
@partner_instance varchar(255)
AS
DECLARE @CmdResult INT
,@Command VARCHAR(2000)
,@exec_query varchar(2000)
,@restore_cmd varchar(4000)
,@BakNode VARCHAR(500)
,@TargetDir varchar(255)
,@filename varchar(255)
,@BakNodeShareName varchar(255)
,@this_servervarchar(255)
,@partner_servervarchar(255)
,@primary_servervarchar(255)
SELECT @this_server=convert(varchar,serverproperty('ComputerNamePhysicalNetBIOS'))
SELECT @partner_server=substring(CONVERT(VARCHAR,@partner_instance),1,PATINDEX('%\%', CONVERT(VARCHAR,@partner_instance))-1)
SELECT @primary_server=substring(CONVERT(VARCHAR,@primary_instance),1,PATINDEX('%\%', CONVERT(VARCHAR,@primary_instance))-1)
-- ======================================
-- primary server
-- ======================================
if @this_server = @primary_server
BEGIN
EXEC ABC_WhichBakNode @BakNode OUTPUT
EXEC ABC_WhichBakNodeShare @BakNodeShareName OUTPUT
SELECT @TargetDir = @BakNode + @BakNodeShareName
--perform initial database backup
EXEC ABC_DoBackups
@BackupType='C'
,@dbname=@database
,@BackupDir = @TargetDir
,@BackupProduct = 2,@DoVerify = 0
,@Debug = 0,@EncryptionKey = NULL
,@SLSThreads = NULL,@SLSThrottle = 85
,@SLSAffinity = 0,@SLSPriority = NULL
,@RetainDays = NULL,@InitBackupDevice = 0
,@PerformDBCC = 0,@ExcludedDBs = NULL
,@CreateSubDir = 1,@CreateSrvDir = 0
,@Files = NULL,@FileGrps = NULL
--perform transaction log backup
EXEC ABC_DoBackups
@BackupType='L'
,@dbname=@database
,@BackupDir = @TargetDir
,@BackupProduct = 2,@DoVerify = 0
,@Debug = 0,@EncryptionKey = NULL
,@SLSThreads = NULL,@SLSThrottle = 85
,@SLSAffinity = 0,@SLSPriority = NULL
,@RetainDays = NULL,@InitBackupDevice = 0
,@PerformDBCC = 0,@ExcludedDBs = NULL
,@CreateSubDir = 1,@CreateSrvDir = 0
,@Files = NULL,@FileGrps = NULL
SET @Command = 'sqlcmd.exe -S "' + rtrim(@partner_instance) + '" -d ABCDBA -E -Q "EXEC dbo.ABC_Mirror_Sync ' + QUOTENAME(@database,'''') + ',' + QUOTENAME(@primary_instance,'''') + ',' + + QUOTENAME(@partner_instance,'''') + ';"';
EXEC master.dbo.xp_cmdshell @Command;
end
-- ======================================
-- mirror database
-- ======================================
if @this_server = @partner_server
begin
--perform database restore on mirror sql server
--perform database log restore on mirror sql server
set @exec_query = 'EXEC ABC_BuildRestoreScript ''' + @database + ''', 0, NULL,0,1'
select @filename = 'mirror_restore_' + @database
exec @filename=ABC_CreateSafeFileName @filename
select @filename = 'D:\' + @filename + '.log'
SELECT @Command = 'SQLCMD -E -S ' + @primary_instance + ' -h-1 -dABCDBA -t15 -w8000 -Q "'+ @exec_query + '" -o'+ @filename
EXEC @CmdResult = master.dbo.xp_cmdshell @Command
CREATE TABLE #Results (Data varchar(8000))
SELECT @Command = 'BULK INSERT #Results FROM "' + @filename + '"'
EXEC (@Command)
PRINT @Command
alter table #Results add physical_device_name VARCHAR(2000)
-- cleanup table
delete from #Results where len(Data) < 10 and data is null
delete from #Results where substring(Data,1,2) = 'go'
delete from #Results where Data like '%-- Restore All databases%'
DECLARE dbcursorRestoreLog cursor for
SELECT Data FROM #Results
OPEN dbcursorRestoreLog
FETCH NEXT FROM dbcursorRestoreLog INTO @restore_cmd
WHILE @@fetch_status = 0
BEGIN
PRINT 'Processing :' + @restore_cmd
EXEC (@restore_cmd)
FETCH NEXT FROM dbcursorRestoreLog into @restore_cmd
END
CLOSE dbcursorRestoreLog
DEALLOCATE dbcursorRestoreLog
DROP TABLE #Results
SELECT @Command = 'DEL "' + @filename + '"'
EXEC master.dbo.xp_cmdshell @Command, NO_OUTPUT
end
GO
USE [ABCDBA]
GO
/****** Object: StoredProcedure [dbo].[ABC_Apply_TransactionLogs] Script Date: 06/03/2010 19:55:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ABC_Apply_TransactionLogs]
@dbname varchar(255)
,@PrimaryDBServer varchar(255)
as
DECLARE @CmdResult INT
,@Command VARCHAR(2000)
,@exec_query varchar(2000)
,@restorelog_cmd varchar(4000)
,@tranlog_apply_date datetime
,@filename varchar(512)
SET NOCOUNT ON
DECLARE @server_name sysname
SET NOCOUNT ON;
-- get last backup time
SELECT top 1 @tranlog_apply_date=a.backup_start_date
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.type = 'L' and a.database_name = @dbname
order by backup_finish_date desc
PRINT 'Restore from ' + convert(varchar,@tranlog_apply_date,120)
IF @tranlog_apply_date IS NULL
BEGIN
set @exec_query = 'EXEC ABC_BuildRestoreScript ''' + @dbname + ''', 1, NULL,0,1'
END ELSE
BEGIN
set @exec_query = 'EXEC ABC_BuildRestoreScript ''' + @dbname + ''', 1, ''' + convert(varchar,@tranlog_apply_date,120) + ''',0,1'
END
select @filename = 'applytran_' + @dbname
exec @filename=ABC_CreateSafeFileName @filename
select @filename = 'D:\' + @filename + '.log'
SELECT @Command = 'SQLCMD -E -S ' + @PrimaryDBServer + ' -h-1 -dABCDBA -t15 -w8000 -Q "'+ @exec_query + '" -o'+ @filename
--INSERT INTO #Results(Data)
EXEC @CmdResult = master.dbo.xp_cmdshell @Command
CREATE TABLE #Results (Data varchar(8000))
SELECT @Command = 'BULK INSERT #Results FROM "' + @filename + '"'
EXEC (@Command)
alter table #Results add physical_device_name VARCHAR(2000)
PRINT @Command
-- cleanup table
delete from #Results where len(Data) < 10 and data is null
delete from #Results where substring(Data,1,2) = 'go'
-- extract filename (NATIVE)
if exists(select * from #Results where substring(Data,1,11) = 'RESTORE LOG')
begin
update #Results
SET physical_device_name = substring(Data,charindex('\\',Data), CHARINDEX('.TRN',Data)-charindex('\\',Data)+4)
end
-- extract filename (LITESPEED)
if exists(select * from #Results where Data like '%xp_restore_log%')
begin
update #Results
SET physical_device_name = substring(Data,charindex('\\',Data), CHARINDEX('.TLS',Data)-charindex('\\',Data)+4)
end
DECLARE dbcursorRestoreLog cursor for
SELECT Data FROM #Results
where physical_device_name
not in (SELECT b.physical_device_name FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.type = 'L' and a.database_name = @dbname
AND a.backup_finish_date > DATEADD(DD,-2,getdate()))
OPEN dbcursorRestoreLog
FETCH NEXT FROM dbcursorRestoreLog INTO @restorelog_cmd
WHILE @@fetch_status = 0
BEGIN
PRINT 'Processing :' + @restorelog_cmd
EXEC (@restorelog_cmd)
FETCH NEXT FROM dbcursorRestoreLog into @restorelog_cmd
END
CLOSE dbcursorRestoreLog
DEALLOCATE dbcursorRestoreLog
DROP TABLE #Results
SELECT @Command = 'DEL "' + @filename + '"'
EXEC master.dbo.xp_cmdshell @Command, NO_OUTPUT
June 3, 2010 at 3:57 am
Thanks mate. This looks awesome. Will give it a go. Many thanks to all!!
June 3, 2010 at 4:08 am
You'll need this sp too.
USE [ABCDBA]
GO
/****** Object: StoredProcedure [dbo].[ABC_BuildRestoreScript] Script Date: 06/03/2010 22:06:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ABC_BuildRestoreScript]
@dbname sysname = '' --restrict restore statements to 1 database
,@NoDBbackup int=0
,@tranlog_backup_start_date DATETIME =NULL
,@lineformat smallint=1
,@ignorego int=0
as
begin
-- Date: 12/16/2001
--
-- Description:
-- This stored procedure generates TSQL script that will restore all the databases
-- on the current SQL Server. This stored procedure takes into account when the last
-- full and differential backups where taken, and how many transaction log backups
-- have been taken since the last database backup, based on the information in
-- the msdb database.
--
-- Modified:
-- Date Who Description
declare @cmd nvarchar (4000)
declare @cmd1 nvarchar (4000)
declare @db nvarchar(128)
declare @filename nvarchar(256)
declare @cnt int
declare @num_processed int
declare @name nvarchar(256)
declare @physical_device_name nvarchar(256)
declare @backup_start_date datetime
declare @type char(1)
-- Turn off the row number message
set nocount on
-- SECTION 1 ----------------------------------------------
-- Define cursor to hold all the different databases for the restore script will be built
IF @dbname = ''
declare db cursor for
select name from master.dbo.sysdatabases
where name not in ('tempdb', 'model')
ELSE
declare db cursor for
select name from master.dbo.sysdatabases
where name = @dbname
-- Create a global temporary table that will hold the name of the backup, the database name, and the type of database backup.
create table ##backupnames (
name nvarchar(256),
database_name nvarchar(256),
type char(1) )
-- Open cursor containing list of database names.
open db
fetch next from db into @db
-- Process until no more databases are left
WHILE @@FETCH_STATUS = 0
BEGIN
-- Subsection 1A --------------------------------------------
-- initialize the physical device name
set @physical_device_name = ''
-- get the name of the last full database backup
select @physical_device_name = physical_device_name , @backup_start_date = backup_start_date
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where type='D' and backup_start_date =
(select top 1 backup_start_date from msdb..backupset
where @db = database_name and type = 'D'
order by backup_start_date desc)
-- Did a full database backup name get found
if @physical_device_name <> ''
begin
-- Build command to place a record in table that holds backup names
select @cmd = 'insert into ##backupnames values (' + char(39) +
@physical_device_name + char(39) + ',' + char(39) + @db + char(39) + ',' +
char(39) + 'D' + char(39)+ ')'
-- Execute command to place a record in table that holds backup names
exec sp_executesql @cmd
end
-- Subsection 1B --------------------------------------------
-- Reset the physical device name
set @physical_device_name = ''
-- Find the last differential database backup
select @physical_device_name = physical_device_name, @backup_start_date = backup_start_date
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where type='I' and backup_start_date =
(select top 1 backup_start_date from msdb..backupset
where @db = database_name and type = 'I' and backup_start_date > @backup_start_date
order by backup_start_date desc)
-- Did a differential backup name get found
if @physical_device_name <> ''
begin
-- Build command to place a record in table that holds backup names
select @cmd = 'insert into ##backupnames values (' + char(39) +
@physical_device_name + char(39) + ',' + char(39) + @db + char(39) + ',' +
char(39) + 'I' + char(39)+ ')'
-- Execute command to place a record in table that holds backup names
exec sp_executesql @cmd
end
-- Subsection 1C --------------------------------------------
--
IF @tranlog_backup_start_date IS NOT NULL
BEGIN
SELECT @backup_start_date = @tranlog_backup_start_date
END
-- Build command to place records in table to hold backup names for all
-- transaction log backups from the last database backup
set @cmd = 'insert into ##backupnames select physical_device_name,' + char(39) + @db + char(39) +
',' + char(39) + 'l' + char(39) +
'from msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id join ' +
'msdb..backupmediafamily c on a.media_set_id = c.media_set_id ' +
'where type=' + char(39) + 'L' + char(39) + 'and backup_start_date > @backup_start_dat and' +
char(39) + @db + char(39) + ' = database_name ' +
' ORDER BY backup_start_date '
-- Execute command to place records in table to hold backup names
-- for all transaction log backups from the last database backup
exec sp_executesql @cmd,@params=N'@backup_start_dat datetime', @backup_start_dat = @backup_start_date
-- get next database to process
fetch next from db into @db
end
-- close
close db
-- Section B ----------------------------------------------
open db
-- Get first recod from database list cursor
fetch next from db into @db
-- Generate Heading in Restore script
print '-- Restore All databases'
-- Process all databases
WHILE @@FETCH_STATUS = 0
BEGIN
-- define cursor for all database and log backups for specific database being processed
declare backup_name cursor for
select name,type from ##backupnames where database_name = @db
-- Open cursor containing list of database backups for specific database being processed
open backup_name
-- Determine the number of different backups available for specific database being processed
select @cnt = count(*) from ##backupnames where database_name = @db
-- Get first database backup for specific database being processed
fetch next from backup_name into @physical_device_name, @type
-- Set counter to track the number of backups processed
set @num_processed = 0
-- Process until no more database backups exist for specific database being processed
WHILE @@FETCH_STATUS = 0
BEGIN
-- Increment the counter to track the number of backups processed
set @num_processed = @num_processed + 1
-- Is the number of database backup processed the same as the number of different backups
-- available for specific database being processed?
-- If so, is the type of backup currently being processed a transaction log backup?
if UPPER(@type) = 'L'
-- Litespeed for SQL Server Backup ....
-- build restore command to restore the last transaction log
-- If extension is 'tls' assume backup is compressed
if LOWER(RIGHT(@physical_device_name,3)) ='tls'
IF @lineformat = 1
begin
select @cmd = 'EXEC master.dbo.xp_restore_log ' +
' @database = ' + char(39) + '[' + rtrim(@db) + ']' + char(39) + char(13)
+ ' , @filename = ' + char(39) +
RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) +
+ char(39) + char(13) + ' , @with = ' + char(39) + 'NORECOVERY' + char(39)
end else
begin
select @cmd = 'EXEC master.dbo.xp_restore_log @database = [' + rtrim(@db) + '] , @filename = ' + char(39) + RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) + char(39) + ' , @with = ''NORECOVERY'''
end
else
IF @lineformat = 1
begin
-- Native SQL Server Backup Transaction Log
select @cmd = 'RESTORE LOG [' + rtrim(@db) + ']' + char(13) +
' FROM DISK = ' + char(39) +
RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) +
char(39) + char(13) + ' WITH NORECOVERY'
end else
begin
select @cmd = 'RESTORE LOG [' + rtrim(@db) + '] FROM DISK = ' + char(39) + RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) + char(39) + + ' WITH NORECOVERY'
end
else
-- Litespeed for SQL Server Backup ....
-- Last backup was not a transaction log backup
-- Build restore command to restore the last database backup
-- If extension is 'sls' assume backup is compressed
if @NoDBbackup = 0
begin
if @lineformat = 1
begin
if LOWER(RIGHT(@physical_device_name,3)) ='sls'
begin
select @cmd = 'EXEC master.dbo.xp_restore_database ' +
' @database = ' + char(39) + '[' + rtrim(@db) + ']' + char(39) + char(13)
+ ' , @filename = ' + char(39) +
RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) +
+ char(39) + char(13) + ' , @with = ' + char(39) + 'NORECOVERY' + char(39)
END else
BEGIN
-- Native SQL Server Backup Database
select @cmd = 'RESTORE DATABASE [' + rtrim(@db) + ']' + char(13) +
+ ' from disk = ' + char(39) +
RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) +
char(39) + char(13) + ' WITH REPLACE, NORECOVERY'
END
end else
begin
If LOWER(RIGHT(@physical_device_name,3)) ='sls'
select @cmd = 'EXEC master.dbo.xp_restore_database ' + ' @database = ' + char(39) + '[' + rtrim(@db) + ']' + char(39) + ' , @filename = ' + char(39) + RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) + char(39) + ' , @with = ' + char(39) + 'NORECOVERY' + char(39)
else
-- Native SQL Server Backup Database
select @cmd = 'RESTORE DATABASE [' + rtrim(@db) + '] from disk = ' + char(39) + RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) + char(39) + ' WITH REPLACE, NORECOVERY'
END
end else
begin
select @cmd = null
end
if @cnt <> @num_processed -- add norecovery clause if not last statement
select @cmd = REPLACE(@cmd, 'NORECOVERY', 'NORECOVERY')
-- if it is master comment line out
if @db = 'master'
set @cmd = '/* ' + char(13) + @cmd + char(13) + '*/'
-- Generate the restore command and other commands for restore script
print @cmd
if @ignorego = 0
begin
print 'go'
print ' '
end
-- Get next database backup to process
fetch next from backup_name into @physical_device_name, @type
end
-- Close and deallocate database backup name cursor for current database being processed
close backup_name
deallocate backup_name
-- Get next database to process
fetch next from db into @db
end
-- Close and deallocate cursor containing list of databases to process
close db
deallocate db
-- Drop global temporary table
drop table ##backupnames
end
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply