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''"'
· Share on Twitter

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
    · Share on Twitter
  • dterriedterrie Posts: 14
    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?
    · Share on Twitter
  • 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
    · Share on Twitter
  • dterriedterrie Posts: 14
    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
    · Share on Twitter
Sign In or Register to comment.