Problem with my backup script - 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

SQL Server 2000 Forums

SQL Server Administration (2000)

Problem with my backup script

Author

Topic

dbist
Yak Posting Veteran

52 Posts

Posted - 2008-02-22 : 17:22:13

Hello,

I am trying to correct this script, it's giving me
____________________________________________________________
"RESTORE DATABASE HISTORYDB_chk FROM DISK = '\\PNRPTS-MMC\Dbcheck\historydb\historydb_db_200802220515.BAK' WITH MOVE HistoryDB_chk_Data TO L:\dbcheck\data\HistoryDBData.mdf , MOVE HistoryDB_chk_Log TO L:\dbcheck\data\HistoryDB_log.ldf WITH RECOVERY , REPLACE
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'HistoryDB_chk_Data'.
Server: Msg 132, Level 15, State 1, Line 1
The label 'L' has already been declared. Label names must be unique within a query batch or stored procedure.
" error.
____________________________________________________________
My script is :
____________________________________________________________
DECLARE @backup_name NVARCHAR(260)

SET @backup_name = (SELECT TOP 1 physical_device_name FROM [PNDB2-MMC].msdb.dbo.backupmediafamily AS F, [PNDB2-MMC].msdb.dbo.backupset AS S
WHERE F.media_set_id = S.media_set_id AND S.database_name = 'HISTORYDB' AND S.type='D'
ORDER BY S.backup_start_date DESC)

IF PATINDEX('%:%', @backup_name) > 0
SET @backup_name= CHAR(39) + '\\PNDB2-MMC\' + replace(@backup_name, ':', '$') + CHAR(39) + ' ' -- Backup File is on Remote Server
ELSE
SET @backup_name= CHAR(39) + @backup_name + CHAR(39) + ' '

DECLARE @sql nvarchar(1000)
DECLARE @data_location nvarchar(100)
DECLARE @data_location2 nvarchar(200)
DECLARE @log_location nvarchar(100)
DECLARE @log_location2 nvarchar(200)
DECLARE @quote nvarchar(10)

SET @data_location = 'HistoryDB_chk_Data'
SET @data_location2 = 'L:\dbcheck\data\HistoryDBData.mdf '
SET @log_location = 'HistoryDB_chk_Log'
SET @log_location2 = 'L:\dbcheck\data\HistoryDB_log.ldf '

SET @sql = 'RESTORE DATABASE ' + 'HISTORYDB_chk' + ' FROM DISK = ' + @backup_name +
'WITH MOVE ' + @data_location + ' TO ' + @data_location2 + ', MOVE ' + @log_location + ' TO ' +
@log_location2 + ' WITH RECOVERY , REPLACE'

print @sql

EXEC (@sql)
GO
____________________________________________________________

Your help is greatly appreciated!

Thank you.

Haywood
Posting Yak Master

221 Posts

Posted - 2008-02-24 : 15:27:21

Need to encapsulate the logical_file_name and the operating_system_file_name paths with single-ticks....

[CODE]
MOVE 'Foo_Data' TO 'L:\Foo_Data.mdf'
[/CODE]

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-25 : 02:00:44

RESTORE DATABASE
HISTORYDB_chk
FROM DISK = '\\PNRPTS-MMC\Dbcheck\historydb\historydb_db_200802220515.BAK'
WITH RECOVERY,
MOVE 'HistoryDB_chk_Data' TO 'L:\dbcheck\data\HistoryDBData.mdf' ,
MOVE 'HistoryDB_chk_Log' TO 'L:\dbcheck\data\HistoryDB_log.ldf' ,REPLACE

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

dbist
Yak Posting Veteran

52 Posts

Posted - 2008-02-25 : 14:21:34

yes but do you see that I cannot pass a single quote to the @sql string. What would be a character assignment for single quote? My guess was \' but that's wrong. Here's the output of my query or print(@sql):

RESTORE DATABASE HISTORYDB_chk FROM DISK = '\\PNRPTS-MMC\Dbcheck\historydb\historydb_db_200802240515.BAK' WITH MOVE HistoryDB_chk_Data TO L:\dbcheck\data\HistoryDBData.mdf , MOVE HistoryDB_chk_Log TO L:\dbcheck\data\HistoryDB_log.ldf WITH RECOVERY, REPLACE

How would I pass single quotes to encapsulate logical and physical name of the data and log files? This is my @sql string

SET @sql = ' RESTORE DATABASE ' + ' HISTORYDB_chk ' + ' FROM DISK = ' + @backup_name +
' WITH MOVE ' + @data_location + ' TO ' + @data_location2
+ ' , MOVE ' + @log_location + ' TO ' +
@log_location2 + ' WITH RECOVERY, REPLACE'

Thank you.

dbist
Yak Posting Veteran

52 Posts

Posted - 2008-02-25 : 17:04:19

I think I got it to work:

SET @sql = ' RESTORE DATABASE ' + ' HISTORYDB_chk ' + ' FROM DISK = ' + @backup_name +
' WITH RECOVERY, MOVE ' + CHAR(39) + @data_location + CHAR(39) + ' TO ' + CHAR(39) + @data_location2
+ CHAR(39) + ', MOVE ' + CHAR(39) + @log_location + CHAR(39) + ' TO ' + CHAR(39) +
@log_location2 + CHAR(39) + ' , REPLACE'

Haywood
Posting Yak Master

221 Posts

Posted - 2008-02-25 : 17:42:38

...just escape the ticks properly...

WITH MOVE '''Foo_Data''' + ' TO ' + '''L:\BAR.mdf'''

dbist
Yak Posting Veteran

52 Posts

Posted - 2008-02-26 : 11:26:48

thank you guys for helping.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources