• 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