March 30, 2008 at 10:15 pm
I need to find out the correct T-SQL to correctly to do an automatic restore of a database from a .bak file every night.
This is for use on a web site where a demo database needs to be able to wipe out any changes that potential clients may have made during the testing of a front end application. Now obviously this is a very easy manual task within Enterprise Manager as you would basically right click on the existing database and go to Restore and pick the .bak file from a specified location.
However I wish to automate the task using a job & schedule that has a bunch of other T-SQL commands that need to be run on this database after the restore is complete (minor field changes that are required in order for the database to be operational with process servers and such).
Can anybody tell me what the T-SQL would be if the .bak file location is constant, the file name differs because it is part of a maintenance package and includes date and time variables. The database name that is being restored is always the same which makes things simple. I also have one other weird thing that happens even if I do it manually, restoring always gets rid of the database ownership credentials, I end up having to keep assigning ownership everytime, whether it's restoring over an existing database or restoring to a brand new one ???
March 31, 2008 at 5:27 pm
Like this:
declare @file Nvarchar(max)
set @file = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MyTests.bak'
RESTORE DATABASE [MyTests] FROM DISK = @file
WITH FILE = 1, NOUNLOAD, STATS = 10
FYI, you can get Management Studios SQL commands for just about everything by Scripting it out. For this RESTORE command, I just went through the right-click Restore Database dialog, and just before I clicked "OK", I clicked the "Script" button at the top of the dialog. (then I edited it a little to demonstrate the file name as a variable).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 1, 2008 at 8:53 am
Thank you very much for this however this script looks like it will only restore from a specified filename.
The filename of the .bak in my case is being created daily by a maintenance task and is of course named MyDB_backup_200803312300.bak as it inserts the date and time stamp in the filename, this means that everyday the filename is going to change.
So perhaps I'm not understanding the syntax in the SQL you provided but it looks pretty static to me. Is there a way to use a variable that would pull the .bak from a specified location without defining the filename or something, or maybe by using a wildcard in the filename?
April 1, 2008 at 9:53 am
Yes, but if you look at it, you will see that the file name is a variable: [font="System"]@file[/font]. You can write SQL procedure code to change it as needed.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 3, 2008 at 6:33 am
So this is the exact syntax I'm using:
declare @file Nvarchar(max)
set @file = N'D:\BKUP\Demo\Demo_backup_200804022315.bak'
RESTORE DATABASE [Demo] FROM DISK = @file
WITH FILE = 1, MOVE N'Demo' TO N'D:\SQLData\Demo.mdf',
MOVE N'Demo_log' TO N'D:\SQLData\Demo_log.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
So I managed to find some time to test this and this is the error I received back:
Executed as user: NT AUTHORITY\SYSTEM. Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\||||||'. Operating system error 2(The system cannot find the file specified.). [SQLSTATE 42000] (Error 3201) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Why is it looking in that directory for the backup file when I've declared the backup directory already.
Can you enlighten me?
April 3, 2008 at 7:59 pm
Jonathan Beck (4/3/2008)
declare @file Nvarchar(max)set @file = N'D:\BKUP\Demo\Demo_backup_200804022315.bak'
RESTORE DATABASE [Demo] FROM DISK = @file
...
Executed as user: NT AUTHORITY\SYSTEM. Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\||||||'. Operating system error 2(The system cannot find the file specified.). [SQLSTATE 42000] (Error 3201) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Hmm, this was a tough one. Turns out that the file name variable must be a Varchar and cannot be an NVarchar (which really surprises me). So, my mistake on the example that I gave you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 4, 2008 at 7:30 am
So by your reply I gather this is still an open topic?
April 6, 2008 at 12:13 am
Jonathan Beck (4/4/2008)
So by your reply I gather this is still an open topic?
Uh, no. Just change the Nvarchar to Varchar.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 10, 2008 at 7:25 am
I do thank you very much for your assistance with this one but alas this does not work for me.
It does not allow me the flexibility of the actual .bak file name to be variable.
The db name portion of the file name remains constant but the suffix which contains the date and time stamp of when the .bak was created changes everyday so I need a T-SQL that will look in a particualr directory for any .bak file and restore from it (this directory only ever has one .bak file in it, the most recent). Or, to be able to state that the file name is like "filename" instead of specifically "filename_backup_200804092315.bak".
As it stands right now I keep getting an error message stating the file cannot be found, until I change the first line to the current filename, making this a manual process, precisely what I was trying to avoid.
April 10, 2008 at 11:08 pm
Jonathan Beck (4/10/2008)
I do thank you very much for your assistance with this one but alas this does not work for me.It does not allow me the flexibility of the actual .bak file name to be variable.
Well, no it does ALLOW that flexibility, because the file name is in fact a variable. The problem is that you haven't followed the directions that I left for you:
Yes, but if you look at it, you will see that the file name is a variable: @file. You can write SQL procedure code to change it as needed.
If you do not know how to do this all you had to do was say so:
Create table #BakDir(FileName varchar(255))
Insert into #BakDir exec xp_cmdshell 'dir /B'
Select @File = FileName from #BakDir
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 14, 2008 at 11:05 am
I am not as experienced as perhaps a majority of users on here so I well definitly need some hand holding on this.
Is this how the syntax is supposed to look from start to finish to achieve my objective, I'm not familiar with these functions at all:
Insert into #BakDir exec xp_cmdshell 'dir /B'
declare @file varchar(max)
Select @File = FileName from #BakDir
set @file = N'D:\BKUP\Demo\Demo_backup_200804022315.bak'
RESTORE DATABASE [Demo] FROM DISK = @file
WITH FILE = 1, MOVE N'Demo' TO N'D:\SQLData\Demo.mdf',
MOVE N'Demo_log' TO N'D:\SQLData\Demo_log.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
I get the following error:
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
Msg 3201, Level 16, State 2, Line 8
Cannot open backup device 'D:\BKUP\Demo\Demo_backup_200804022315.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 8
RESTORE DATABASE is terminating abnormally.
I appreciate your assistance with this.
April 14, 2008 at 1:24 pm
Jonathan Beck (4/14/2008)
I get the following error:Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
Msg 3201, Level 16, State 2, Line 8
Cannot open backup device 'D:\BKUP\Demo\Demo_backup_200804022315.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 8
RESTORE DATABASE is terminating abnormally.
I appreciate your assistance with this.
In this case the Error message has it exactly right. You will need to enable xp_CmdShell using spConfigure:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 14, 2008 at 1:29 pm
As for you SQL code, I think that you need one change:
Insert into #BakDir exec xp_cmdshell 'dir /B'
declare @file varchar(max)
Select @File = FileName from #BakDir
-- you do not need the following line, it is superceded by the proceeding line
--set @file = N'D:\BKUP\Demo\Demo_backup_200804022315.bak'
RESTORE DATABASE [Demo] FROM DISK = @file
WITH FILE = 1, MOVE N'Demo' TO N'D:\SQLData\Demo.mdf',
MOVE N'Demo_log' TO N'D:\SQLData\Demo_log.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 14, 2008 at 1:50 pm
Sorry to be a pain, I'm now getting this error:
Msg 3044, Level 16, State 2, Line 4
Invalid zero-length device name. Reissue the BACKUP statement with a valid device name.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.
after the running the adjusted query:
Insert into #BakDir exec xp_cmdshell 'dir /B'
declare @file varchar(max)
Select @File = FileName from #BakDir
RESTORE DATABASE [Demo] FROM DISK = @file
WITH FILE = 1, MOVE N'Demo' TO N'D:\SQLData\Demo.mdf',
MOVE N'Demo_log' TO N'D:\SQLData\Demo_log.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
April 14, 2008 at 4:43 pm
Add the print statements and tell us what they say:
Insert into #BakDir exec xp_cmdshell 'dir /B'
declare @file varchar(max)
Select @File = FileName from #BakDir
Print 'Restoring from file:'
Print @file
Print '--======'
RESTORE DATABASE [Demo] FROM DISK = @file
WITH FILE = 1, MOVE N'Demo' TO N'D:\SQLData\Demo.mdf',
MOVE N'Demo_log' TO N'D:\SQLData\Demo_log.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply