July 13, 2010 at 6:55 am
Last month we upgraded from SQL Server 2000 to SQL Server 2008, someone from our ERP provider (remotely)moved the data and created the new Maintenance Plan (backups, checks, etc.). He did a very good job. However, one of the tasks I had added in the 2000 Maintenance Plan ran at 7:00 PM each night and restored the 6:00 PM backup of the live db into a TEST database. It was great being able to get into the ERP system on the TEST database to explore and learn.
The person did not create a similar task in the Maintenance Plan. His reasoning was that we could manually perform a restore into the TEST database when we needed it. We were unable to convince him that the impact was we would have to wait until the restore completed instead of being able to immediately work in TEST.
Can someone steer me to a resource that explains the steps to follow? Or, if it's straight forward, provide us with the steps.
One change I'll make this time is to call it SANDBOX, instead of TEST.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
July 13, 2010 at 8:19 am
If you are able to do it manually you are able to script/schedule it.
Just do your manual thing, script every step, create a job and schedule it.
It should be a one step job, force-restore.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 13, 2010 at 8:27 am
If the SQL Server 2000 instance still exist or is accessible, simply copy the job over to the SQL Server 2005 instance.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 16, 2010 at 12:12 pm
Thanks for the replies.
Dang! They shut down the old server just over a week ago.
Great! I was able to fumble around and create a job by performing the manual steps It wasn't easy... Help wasn't much help.
On the good side...
Restoring the database from a backup was easier than in SQL Server 2000, actually straight forward.
I didn't see the 'Script' option until I had started a restore. So, restored again and saw that I could turn on the script and chose to save it to a job.
Fumbled around trying to figure out how to schedule the Job.
Like I said, Help wasn't much help. I searched 'Index', 'Contents' for "Schedule", "Jobs", "Script" and couldn't get any hits. I don't think I'm using Help correctly. It appears I'm not even in the right place when I click on Help > Index or Contents.
Anyway, I see a Scheduled Job is in SQL Server Agents > Jobs. I will check TEST Monday for data created today.
Have a great weekend everyone!
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
July 29, 2010 at 7:35 am
Houston, we have a problem...
The job is failing. It ran the first couple nights - I checked to see if a record created the previous day was in the Test db and they were. So, I assumed everything was working OK. Today I had to use the Test db and discovered data wasn't there. Checked the log and here is the message (names have been changed to protect the innocent):
Executed as user: DOMAIN\admin1. Cannot open backup device '\\OURServer1\Amtech\SQLBackups\0600_PM\ERPDB_backup_2010_07_15_180001_2922648.bak'. 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.
I understand the problem. The script captured the specific backup, dated, and those backups are deleted after a few days.
Seems to me the script needs to build a filename using some wildcarding, example:
"ERPDB_backup_" & {today's date formatted as yyyy-mm-dd} & *wildcard* & ".bak"
Is this possible?
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
July 29, 2010 at 8:25 am
How are you grabbing that backpup file name now to restore from?
Your initial post stated the job was run at 7pm to restore hte 6pm backup from prod to test. Is the error you are showing from a current run of the job or is this the type of error you are seeing each time the job ran?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 29, 2010 at 11:50 am
The error occurs each time the job is run, nightly at 7:00. (Except for the first 2 nights when the backup file that is referenced still existed).
The way I created the job was by first proceeding through the steps manually with the 'Script' turned on. The restore wizard selected the most recent backup (no others were even displayed). After the restore completed I scheduled it, the script that was created. Now, I looked at the command in the job and the RESTORE explicitly names the backup file (that was used for the restore when the script was created).
Hang on... OK, I found a script that I think I can use, with some editing.
The webpage is http://qa.sqlservercentral.com/Forums/Topic865866-338-3.aspx
The command in my script is (once again, names have been changed to protect the innocent):
RESTORE DATABASE [TEST] FROM DISK = N'\\Server1\Amtech\SQLBackups\0600_PM\OURERPDB_backup_2010_07_15_180001_2922648.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
Here's the script from that link. Let's see if I can figure out the correct changes. I edited the script below this script:
This is from the posting:
- - - - - - - - - - - - -
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @dbName = 'abc'
SET @backupPath = 'D:\Backups\restoreabc\'
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''
+ @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE,' +
'move ''abcdata'' to ''d:\SQLData\abc.mdf'',
move ''abclog'' to ''d:\SQLData\abclog.ldf'''
PRINT @cmd
insert into test.dbo.lastrestored values(@lastFullBackup)
EXECUTE sp_executesql @cmd
-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH NORECOVERY'
PRINT @cmd
insert into test.dbo.lastrestored values(@backupfile)
EXECUTE sp_executesql @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
/*
-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd
*/
- - - - - - - - - - - - -
This is my edited script: <---- these are my comments and questions
- - - - - - - - - - - - -
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @dbName = 'OURERPDB' <-------------------------------------------------------------------our live db name
SET @backupPath = N'\\Server1\Amtech\SQLBackups\0600_PM\' <---------why does our have an N before the path?
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE 'OURERPDB%.bak' <------------------------- I combined their two lines into one. Is this OK?
SET @cmd = 'RESTORE DATABASE [TEST] FROM DISK = ''' <---------------------------------------------------
+ @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE,' +
'move ''abcdata'' to ''d:\SQLData\abc.mdf'', <-------------------------------- Are these needed? It's only a TEST db
move ''abclog'' to ''d:\SQLData\abclog.ldf''' <-------------------------------- Needed?
PRINT @cmd <------------------------------------------------------------- Needed?
insert into test.dbo.lastrestored values(@lastFullBackup) <------------------- Needed?
EXECUTE sp_executesql @cmd
-- 5 - check for log backups <---------------------------------------------- What does this step do?
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH NORECOVERY'
PRINT @cmd
insert into test.dbo.lastrestored values(@backupfile)
EXECUTE sp_executesql @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
/*
-- 6 - put database in a useable state <---------------------------- What's this do? I don't want to touch OURERPDB
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY'
PRINT @cmd
*/
I'm so confused. :hehe:
Thanks!
Ed
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
July 29, 2010 at 12:44 pm
Option I have considered (although never implemented at current employer) is after each backup job runs have it generate the restore script. If you did something like this you could just code the job to generate the restore script to always have the TEST database name in the RESTORE command.
I went to the SQL Saturday #7 in Birmingham, AL and Ken Simmons did a session on DBA task. In his download he has a SQL Agent job that does a backup and generates the script, you can find that here
Then another good one is on MSSQLTips.com by Greg Robidoux that is very good. You can find that here (Auto generate SQL Server restore script from backup files in a directory)
Thomas LaRock put out a tip on the same site on checking if the backup file exist, you can find that here (Script to check that backup files still exist for SQL Server). Which Tom uses xp_fileexist to check the directory, so you could just plug this right into your restore script by just checking for the date/time to match up since you want it to pull the most recent backup of production.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 30, 2010 at 7:02 am
Thanks Shawn.
I checked the suggested sites and Robidoux's appears to be the most straight forward one - in fact, the script I found looks to be the same.
At the end he says:
If you run the above code in a query window, assuming the listed files above existed, you will get the following output. At this point you can copy and paste this code into another query window and run the query to do the actual restore.
Things are getting hairy for me - I am learning admin stuff as needed...
Edited the script, changing @dbName & dbbackupPath values and @CMD = 'RESTORE TEST ...'.
Testing... Attempted to run the script in a query and got the following error:
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...
Uh, oh. This is beyond what I know. I am reading MSDN's SQL Server Online Books - Surface Area Configuation, but, I observe caution in new areas that may cause damage.
Step 1 - Changing the configuration. How straight forward are the instructions to change the configuration and be able to run 'xp_cmdshell'? In other words - If they aren't complicated would someone please provide the instructions/advise?
If that works OK...
Step 2 - I will run the script in a query window, then Copy/Paste the result and run that command.
If that works OK...
Step 3 - What do I change to make it execute the result itself? i.e. How is @cmd executed?
- - - Here's the edited script - - -
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @dbName = 'OURERPdb'
SET @backupPath = '\\Server1\Amtech\SQLBackups\0600_PM\'
-- 3 - get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
SET @cmd = 'RESTORE DATABASE TEST FROM DISK = '''
+ @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd
- - - - - - - - - - - - -
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
July 30, 2010 at 7:33 am
I found a 'How To' for changing the xp_cmdshell configuration, courtesy of Mangal Pardeshi ...
A. Enable Xp_Cmdshell from Management Studio.
For enabling Xp_CmdShell from Management Studio you need to execute following code.
-- 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
This appears to be straight forward.
BIG QUESTION - What are the risks of changing this setting? Could we add this to the beginning of the Restore job's script, than another one at the end with a 0 value (guessing) to turn it off?
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
July 30, 2010 at 7:41 am
Uh, oh. This is beyond what I know. I am reading MSDN's SQL Server Online Books - Surface Area Configuation, but, I observe caution in new areas that may cause damage.
Rightly so...you should understand everything about a script before you implement it. So ata boy to you for wanting to understand it.
Yes it can be turned on and off at your descretion. The event is logged to the event log each time.
You don't actually need to use the xp_cmdshell to get the list of files. If you are just wanting to restore the last backup taken of the database (at the time you run the script) you can just query the backup tables in [msdb] to grab the device name, which will give you the full path and backup filename. This tip has a good script on it, check the section titled "Most Recent Database Backup for Each Database". Then just add in the msdb.dbo.backupmediafamily.physical_device_name in the query to grab the backup file.
This method assumes the backup file is there, without checking.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 30, 2010 at 7:50 am
Create a job on the ERP server to create a backup of the database and move the backup file to the test machine. Your SQL Server Agent account should have read/write permission on the remote share.
Then create a job to run the restore on the test machine.
Here is a link for your reference.
Hope, this may help.
Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly
July 30, 2010 at 8:52 am
Sudeepta (7/30/2010)Here is a link for your reference.
That solution assumes the backup is named the same thing. In EdA's position he will not know what the backup is called each time, since he has the timestamp.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 30, 2010 at 12:58 pm
Phew! This is fun.
You don't actually need to use the xp_cmdshell to get the list of files...
This method assumes the backup file is there, without checking.
You don't have to tell me twice, I'm doing it this way! 🙂
If the backup file isn't there I've got other problems to deal with.
I got the script working, at least it displays the command to run the RESTORE (see below).
Questions:
How do I get it to execute the command rather than print it? (What statement replaces Print @cmd)
Something tells me I should change the 'NO RECOVERY' to 'RECOVERY'. I'm not completely sure what the options mean/do.
I can manually run this. What steps do I take to get from here to a scheduled job?
Thanks for your help so far! I really appreciate it.
Here's the latest script:
-- RESTORE TEST from OURERPDB
-- Jul3010 EdA v001 - Build & XEQ command to RESTORE TEST from OURERPDB
-- Based on script by Tom Ford
-- 1 - Variable declaration
DECLARE @cmd NVARCHAR(500)
DECLARE @lastFullBackup NVARCHAR(500)
-- 4 - Find latest full backup
SELECT @lastFullBackup =
( SELECT
MAX(msdb.dbo.backupset.name) as BAKFilename
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D' and database_name = 'OURERPDB'
GROUP BY
msdb.dbo.backupset.database_name ) + '.bak'
--5 - Create and execute the command
SET @cmd = 'RESTORE DATABASE TEST FROM DISK = '''
+ @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
July 30, 2010 at 1:46 pm
Change the
PRINT @cmd
TO
sp_executesql @cmd
See here for reference (BOL)
Ain't it fun to learn new stuff...unless you are an MCM :w00t: you can always learn something new with SQL Server 😉
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply