Competition: What’s your favorite Redgate tool? Enter now.

use dynamic string for sqlbackup command

dterriedterrie Posts: 14
edited May 21, 2008 7:17PM in SQL Backup Previous Versions
Hi,

I need to run sqlbackup nightly to restore a backup using a dynmically constructed string inserting a date in the <AUTO> name string as below so that I can automate the restore process on a secondary server. The backup is from an EE instance and the restore is to an SE instance. What I have below errors out where I try to insert + @dt + into the command. How do I do this? This command, pulled from the script tab and altered to use the correct paths, succeeds with the date hardcoded, so I'm close, I guess.
DECLARE @dt CHAR&#40;8&#41;
SET @dt = CONVERT&#40;CHAR&#40;8&#41;,GETDATE&#40;&#41;,112&#41;

EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE &#91;CaliforniaSE&#93;  
FROM DISK = ''\\principal\backups\database\FULL_&#40;local&#41;_California_' + @dt + '_030000.sqb'' WITH RECOVERY, 
MOVE ''California'' TO ''F:\FileGroups\DataSE\CaliforniaSE.mdf'', 
MOVE ''California _Data'' TO ''F:\FileGroups\DataSE\CaliforniaSE_Data.ndf'', 
MOVE ''California_Indexes'' TO ''G:\FileGroups\IndexesSE\CaliforniaSE_Indexes.ndf'', 
MOVE ''California_Log'' TO ''H:\FileGroups\LogsSE\CaliforniaSE_Log.ldf''"'

Comments

  • peteypetey Posts: 2,358 New member
    The entire command needs to be in a single string value e.g.
    DECLARE @cmd NVARCHAR&#40;1024&#41;
    DECLARE @exitcode INT
    DECLARE @errorcode INT
    
    DECLARE @dt CHAR&#40;8&#41; 
    SET @dt = CONVERT&#40;CHAR&#40;8&#41;,GETDATE&#40;&#41;,112&#41; 
    
    SET @cmd = N'-SQL "RESTORE DATABASE &#91;CaliforniaSE&#93;  
    FROM DISK = ''\\principal\backups\database\FULL_&#40;local&#41;_California_' + @dt + '_030000.sqb'' WITH RECOVERY, 
    MOVE ''California'' TO ''F:\FileGroups\DataSE\CaliforniaSE.mdf'', 
    MOVE ''California _Data'' TO ''F:\FileGroups\DataSE\CaliforniaSE_Data.ndf'', 
    MOVE ''California_Indexes'' TO ''G:\FileGroups\IndexesSE\CaliforniaSE_Indexes.ndf'', 
    MOVE ''California_Log'' TO ''H:\FileGroups\LogsSE\CaliforniaSE_Log.ldf''"'
    
    EXEC master..sqlbackup @cmd, @exitcode OUTPUT, @errorcode OUTPUT
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Peter,

    Thanks for the help. Just to complete the loop, since I've run the command once and created the target db, I need to replace

    WITH RECOVERY with WITH RECOVERY, REPLACE

    yes?
  • peteypetey Posts: 2,358 New member
    If using SQL Server 2005, yes.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Fixed syntax errors and took seconds off default <AUT0> name, so we've got this, which works:
    DECLARE @cmd NVARCHAR&#40;1024&#41; 
    DECLARE @exitcode INT 
    DECLARE @errorcode INT 
    
    DECLARE @dt CHAR&#40;8&#41; 
    SET @dt = CONVERT&#40;CHAR&#40;8&#41;,GETDATE&#40;&#41;,112&#41; 
    
    SET @cmd = '''N -SQL "RESTORE DATABASE &#91;CaliforniaSE&#93;  
    FROM DISK = ''\\principal\backups\database\FULL_&#40;local&#41;_California_' + @dt + '_0300.sqb'' WITH 
    MOVE ''California'' TO ''F:\FileGroups\DataSE\CaliforniaSE.mdf'', 
    MOVE ''California _Data'' TO ''F:\FileGroups\DataSE\CaliforniaSE_Data.ndf'', 
    MOVE ''California_Indexes'' TO ''G:\FileGroups\IndexesSE\CaliforniaSE_Indexes.ndf'', 
    MOVE ''California_Log'' TO ''H:\FileGroups\LogsSE\CaliforniaSE_Log.ldf''" WITH RECOVERY, REPLACE' 
    PRINT @cmd
    
    EXEC master..sqlbackup @cmd, @exitcode OUTPUT, @errorcode OUTPUT
    
Sign In or Register to comment.