Thanks for the info. I've been doing this for some time and never thought to kill connections as I'm doing it at 1am. Guess I've been lucky. I'll have to add that to my process.
For any RedGate users out there:
I use RedGate's SQL Backup for backup, and restore to a server without a SQL Backup License. So my steps are to:
-Back up the database
-Convert the .sqb to an MTF file with a static file name
-Restore using Move and Replace
I use Mon_dd_yyyy_Dbname_FULL.sqb for backup file names. So I know what the current backup file name should be every night. Here's the procedure I use to convert the .sqb to MTF:
create procedure convert_sqb_sp
as
/*
Convert the .sqb backup file created from Red-Gate's SQL Backup
to a MTF .bak file to restore on the MyRptDB database.
*/
set nocount on
declare @cmd varchar(500)
declare @f1 varchar(200), @f2 varchar(200), @rtn int
declare @exitcode int, @sqlerrorcode int
select @f1 = 'c:\sql_backup\' + replace(replace(convert(varchar(12),getdate(),107),' ','_'),',','') + '_MyLiveDB_FULL.SQB'
select @f2 = 'c:\sql_backup\MyRptDB_Restore.bak'
select @cmd = 'del ' + @f2
exec @rtn = master..xp_cmdshell @cmd
if @rtn <> 0
begin
raiserror('MyRptDB Convert - Delete .bak failed', 16,1)
return 1
end
else
begin
select @cmd = '-SQL "CONVERT ''' + @f1 + ''' TO ''' + @f2 + '''"'
exec master..sqlbackup @cmd, @exitcode OUT, @sqlerrorcode OUT
if (@exitcode >= 500) or (@sqlerrorcode <> 0)
begin
raiserror('MyRptDB Convert - Convert .sqb failed', 16,1)
return 1
end
end