Scripting a Restore - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Scripting a Restore

Author

Topic

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-11-29 : 03:27:43

I am trying to set up a daily restore to a dev server. This is being done from a 3 file SQL Backup backup.

Exec master..sqlbackup N'-SQL "RESTORE DATABASE [thedb]
FROM DISK = '@filename1',
DISK = '@filename2',
DISK = '@filename3'
WITH RECOVERY,

The filenames are assigned just fine, but when I add this to the
The job runs fine with the full path in place of the variables. The above code is giving a Syntax error at @filename1.

Using double apostrophe gives me a syntax error at FROM

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-29 : 03:56:46

Try

DECLARE @sql varchar(1000)
SET @sql='
Exec master..sqlbackup N''-SQL "RESTORE DATABASE [thedb]
FROM DISK = '''+@filename1+''',
DISK = '''+@filename2+''',
DISK = '''+@filename3+'''
WITH RECOVERY'
EXEC(SQL)

Madhivanan

Failing to plan is Planning to fail

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-11-29 : 04:25:53

It looked like it was going to work and then threw this:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '\'.
Msg 105, Level 15, State 1, Line 7
Unclosed quotation mark after the character string '"'.

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-29 : 04:28:10

Post the result of

DECLARE @sql varchar(1000)
SET @sql='
Exec master..sqlbackup N''-SQL "RESTORE DATABASE [thedb]
FROM DISK = '''+@filename1+''',
DISK = '''+@filename2+''',
DISK = '''+@filename3+'''
WITH RECOVERY'
PRINT SQL

Madhivanan

Failing to plan is Planning to fail

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-11-29 : 04:34:59

Exec master..sqlbackup N'-SQL "RESTORE DATABASE [theDB]
FROM DISK = '\\server\SQL_Backups\Database\anotherfolder\backupfile01.sqb',
DISK = '\\server\SQL_Backups\Database\anotherfolder\backupfile02.sqb',
DISK = '\\server\SQL_Backups\Database\anotherfolder\backupfile03.sqb'
WITH RECOVERY,

PASSWORD = '**********'"

"Database" is the actual name of one of the dir in the path to the backup files.

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-11-29 : 04:38:17

The status bar states "Query executed successfully" when I got the errors under Exec

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-29 : 05:01:39

DECLARE @sql varchar(1000)
SET @sql='
Exec master..sqlbackup N''''-SQL "RESTORE DATABASE [thedb]
FROM DISK = '''+@filename1+''',
DISK = '''+@filename2+''',
DISK = '''+@filename3+'''
WITH RECOVERY,
PASSWORD = ''**********'''
EXEC(SQL)

Madhivanan

Failing to plan is Planning to fail

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-11-29 : 05:17:41

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.
Msg 103, Level 15, State 4, Line 2
The identifier that starts with 'RESTORE DATABASE [theDB]
FROM DISK = '\\server\SQL_Backups\Database\anotherfolder\backupfile0' is too long. Maximum length is 128.

Exec master..sqlbackup N''-SQL "RESTORE DATABASE [theDB]
FROM DISK = '\\server\SQL_Backups\Database\anotherfolder\backupfile01.sqb',
DISK = '\\server\SQL_Backups\Database\anotherfolder\backupfile02.sqb',
DISK = '\\server\SQL_Backups\Database\anotherfolder\backupfile03.sqb'
WITH RECOVERY,
PASSWORD = '**********'"

It errored on the missing close " so I added it '**********''"'

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-11-29 : 05:18:23

that is hard to see...it should look more like ' ' " '

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-11-29 : 05:27:49

Also, my declaration is:

DECLARE @sql nvarchar(4000)

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-03 : 02:36:01

In case anyone stumbles across this thread later.

I resolved it:

set @restorecmd = 'exec master..sqlbackup N''-SQL "RESTORE DATABASE [myDB]'
+ ' FROM DISK = '''''
+ @filename1
+ ''''','
+ 'DISK = '''''
+ @filename2
+ ''''','
+ 'DISK = '''''
+ @filename3
+ ''''''
+ ' WITH RECOVERY,'
+ 'REPLACE, PASSWORD = ''''thepassword''''"'''

Exec(@restorecmd)

Thanks for your help, madhivanan...I just needed more '''''''''''

---------------------------

Working until "the morning sun sets the midnight sky on fire"!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-03 : 07:35:22

Well. Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources