August 23, 2010 at 11:49 am
I am trying to restore to a database from another database's backup. The backup file uses the current date, such as FSDBMR_backup_201008230300.bak. I am trying to put that into a string as follows:
DECLARE @SourceDB varchar(255)
set @SourceDB = 'N''D:\FS_Database\Backups\FSDBMR\FSDBMR_backup_' + cast(datepart(year, getdate()) as varchar(4))
+ replace((space(2 - len(cast(datepart(month, getdate()) as varchar (2)))) + rtrim(cast(datepart(month, getdate()) as varchar (2)))),' ','0')
+ replace((space(2 - len(cast(datepart(day, getdate()) as varchar (2)))) + rtrim(cast(datepart(day, getdate()) as varchar (2)))),' ','0')
+ '0300.bak'''
RESTORE DATABASE [FSDBSB] FROM DISK = @SourceDB
WITH FILE = 1, MOVE N'FSDBMR.dat' TO N'D:\FS_Database\Data\FSDBSB.mdf', MOVE N'FSDBMRLOG.dat'
TO N'D:\FS_Database\Data\FSDBSBLOG.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
However, when I try to do that, I get the following error:
Msg 3201, Level 16, State 2, Line 29
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\N'D:\FS_Database\Backups\FSDBMR\FSDBMR_backup_201008230300.bak''. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.).
Msg 3013, Level 16, State 1, Line 29
RESTORE DATABASE is terminating abnormally.
Can someone tell me what I am doing wrong?
Thanks.
August 23, 2010 at 12:26 pm
Hello!
I believe your issue is because you are not adding a valid backup device. The below should do the trick (I myself use it in my development environments).
DECLARE @SourceDB nvarchar(255)
set @SourceDB = 'N''D:\FS_Database\Backups\FSDBMR\FSDBMR_backup_' + cast(datepart(year, getdate()) as varchar(4))
+ replace((space(2 - len(cast(datepart(month, getdate()) as varchar (2)))) + rtrim(cast(datepart(month, getdate()) as varchar (2)))),' ','0')
+ replace((space(2 - len(cast(datepart(day, getdate()) as varchar (2)))) + rtrim(cast(datepart(day, getdate()) as varchar (2)))),' ','0')
+ '0300.bak'''
-- ADD A NEW BACKUP DEVICE
-- TYPE NAME PHYSICAL LOCATION
EXEC SP_ADDUMPDEVICE 'disk','networkdrive',@SourceDB
-- Option# 1
-- EXECUTE THE RESTORE OPERATION
RESTORE DATABASE [FSDBSB]
FROM networkdrive
WITH FILE = 1,
NOUNLOAD ,
STATS = 10,
RECOVERY ,
MOVE N'FSDBMR.dat' TO N'D:\FS_Database\Data\FSDBSB.mdf',
MOVE N'FSDBMRLOG.dat' TO N'D:\FS_Database\Data\FSDBSBLOG.ldf'
--GO
-- DROP THE DEVICE
-- Name, Physical File (OPTIONAL - if present, the file is deleted)
EXEC SP_DROPDEVICE 'networkdrive'
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
August 23, 2010 at 1:07 pm
Unfortunately, I get the same error. Is there possibly a setting that I am overlooking?
Thanks.
August 23, 2010 at 1:28 pm
Oops...sorry. Try this: (I removed the UNICODE N' from the start of the path)
DECLARE @SourceDB nvarchar(255)
set @SourceDB = 'D:\FS_Database\Backups\FSDBMR\FSDBMR_backup_' + cast(datepart(year, getdate()) as varchar(4))
+ replace((space(2 - len(cast(datepart(month, getdate()) as varchar (2)))) + rtrim(cast(datepart(month, getdate()) as varchar (2)))),' ','0')
+ replace((space(2 - len(cast(datepart(day, getdate()) as varchar (2)))) + rtrim(cast(datepart(day, getdate()) as varchar (2)))),' ','0')
+ '0300.bak'''
-- ADD A NEW BACKUP DEVICE
-- TYPE NAME PHYSICAL LOCATION
EXEC SP_ADDUMPDEVICE 'disk','networkdrive',@SourceDB
-- EXECUTE THE RESTORE OPERATION
RESTORE DATABASE [FSDBSB]
FROM networkdrive
WITH FILE = 1,
NOUNLOAD ,
STATS = 10,
RECOVERY ,
MOVE N'FSDBMR.dat' TO N'D:\FS_Database\Data\FSDBSB.mdf',
MOVE N'FSDBMRLOG.dat' TO N'D:\FS_Database\Data\FSDBSBLOG.ldf'
GO
-- DROP THE DEVICE
-- Name , Physical File (OPTIONAL - if present, the file is deleted)
EXEC SP_DROPDEVICE 'networkdrive'
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
August 23, 2010 at 1:52 pm
We are getting closer. This is the error message I get now.
Msg 3201, Level 16, State 2, Line 34
Cannot open backup device 'networkdrive(D:\FS_Database\Backups\FSDBMR\FSDBMR_backup_201008230300.bak')'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 34
RESTORE DATABASE is terminating abnormally.
August 24, 2010 at 3:57 am
Did you try using the UNC path instead of D:\. Also does the .bak file exist in the given path?
August 24, 2010 at 6:00 am
Try this: (I had removed the UNICODE N' in the query earlier, but had missed the '' at the end of the file name, which have been removed now)
DECLARE @SourceDB nvarchar(255)
set @SourceDB = 'D:\FS_Database\Backups\FSDBMR\FSDBMR_backup_' + cast(datepart(year, getdate()) as varchar(4))
+ replace((space(2 - len(cast(datepart(month, getdate()) as varchar (2)))) + rtrim(cast(datepart(month, getdate()) as varchar (2)))),' ','0')
+ replace((space(2 - len(cast(datepart(day, getdate()) as varchar (2)))) + rtrim(cast(datepart(day, getdate()) as varchar (2)))),' ','0')
+ '0300.bak'
-- ADD A NEW BACKUP DEVICE
-- TYPE NAME PHYSICAL LOCATION
EXEC SP_ADDUMPDEVICE 'disk','networkdrive',@SourceDB
-- EXECUTE THE RESTORE OPERATION
RESTORE DATABASE [FSDBSB]
FROM networkdrive
WITH FILE = 1,
NOUNLOAD ,
STATS = 10,
RECOVERY ,
MOVE N'FSDBMR.dat' TO N'D:\FS_Database\Data\FSDBSB.mdf',
MOVE N'FSDBMRLOG.dat' TO N'D:\FS_Database\Data\FSDBSBLOG.ldf'
GO
-- DROP THE DEVICE
-- Name , Physical File (OPTIONAL - if present, the file is deleted)
EXEC SP_DROPDEVICE 'networkdrive'
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
August 24, 2010 at 9:33 am
That worked! Thank you for helping me out on this. We can now automate the restore process, and I learned a valuable technique.
August 25, 2010 at 1:37 am
Not a problem at all. I am happy that you are now all set.
Have a wonderful day ahead!
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
December 30, 2010 at 7:20 am
Sorry for joining the discussion so late. I think the problem is that the following statements adds an unneeded layer of quoting:
set @SourceDB = 'N''D:\FS_Database\Backups\FSDBMR\FSDBMR_backup_' + cast(datepart(year, getdate()) as varchar(4))
+ replace((space(2 - len(cast(datepart(month, getdate()) as varchar (2)))) + rtrim(cast(datepart(month, getdate()) as varchar (2)))),' ','0')
+ replace((space(2 - len(cast(datepart(day, getdate()) as varchar (2)))) + rtrim(cast(datepart(day, getdate()) as varchar (2)))),' ','0')
+ '0300.bak'''
If you do this instead you should be fine (time stamp code left out for brevity):
set @SourceDB = 'D:\FS_Database\Backups\FSDBMR\FSDBMR_backup_'
+ ...
+ '0300.bak'
And speaking of the time stamp, you can generate it more easily like this:
set @SourceDB = 'D:\FS_Database\Backups\FSDBMR\FSDBMR_backup_'
+ CONVERT(varchar(8), GETDATE(), 112)
+ '0300.bak'
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply