May 4, 2012 at 7:16 am
I'm a relative newbie here with SQL Server administratioin tasks. I have all different versions
of SQL Server - 2000, 2005, 2008 and 2008 R2, from standard edition to enterprise. My primary
focus right now is to make sure all the full and transaction log backup jobs are working properly.
Well that is just about complete.
What I want to do now is test the restore - to make sure what I have actually works.
My thinking was to copy the databases from their production server to another test box. Create a
backup job on the test server and run it against the new database. While it would be worthwhile
to test the databases where they are - alot of them are 24 x 7.
So my question - is this the best way to go? Or would there be a better method to employ.
Comments / URLs are welcome...
May 4, 2012 at 7:34 am
Yeah, you can't test this against production systems. Good first choice.
After that, just take the backups that are already running in production and test them in a restore against a different environment. No need to take a different backup of the database. In fact, that'll just test that backup. Test the ones from production.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 4, 2012 at 7:41 am
That is a way to test if your backups work.
Keep in mind, you cannot restore a higher version backup to a lower version instance !
Also keep in mind, if you didn't migrate the SQLusers including their SID, you may have to run fixing scripts on the restored database to map the sids in the database to the actual instance sid, based on:
- SQL2000 : exec sp_change_users_login @Action = 'Update_One',@UserNamePattern = ' + @username + ', @LoginName = ' + @username + '
- higher versions: 'ALTER USER [' + @username + '] WITH LOGIN = [' + @username + '] ;'
Your test instance service account needs windows auth to be able to read the backup file(s) used with the restore statements.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 4, 2012 at 8:59 am
There are scripts here on this site to help you automate this. Ideally you restore all backups every day. If not, pick a few test workstations, set up restores on a schedule so that you rotate among all databases over time.
SQL Backup Pro from Redgate will help with this, as may some other third parties. (Disclosure: I work for Redgate).
Whatever you choose, I'd try to build a repeatable, steady process. As ALZDBA mentions, users/logins might not sync, so you should have a process here as well. Perhaps an automatic execution of sp_help_revlogin and saving the script.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 4, 2012 at 9:16 am
Although, if you're simply testing the restore to validate the backup, the fact that logins don't sync correctly is not an issue you need to worry about.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 8, 2012 at 9:24 am
Information has been very helpful. Thanks guys!!
May 8, 2012 at 2:15 pm
One thing you may want to do beyond just the restore is run a checkdb against the restored database. That way you know not only that it restores but it's a fully working copy (at least as far as SQL is concerned.)
May 8, 2012 at 4:36 pm
Here's something I cobbled together as a base for something similar. May be handy for you, may not. Only works for 2005+
SELECT
'restore database [' +
msdb.dbo.backupset.database_name +'] From Disk = N''' +
msdb.dbo.backupmediafamily.physical_device_name+'''
WITH MOVE '+mf.name+' TO ''E:'+mf.name+'.mdf'',
MOVE '+mf.name+' TO ''E:'+mf2.name+'.ldf''
WITH REPLACE
GO
'
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
INNER JOIN sys.sysdatabases sd ON sd.name = msdb.dbo.backupset.database_name
INNER JOIN sys.master_files mf ON sd.dbid=mf.database_id
INNER JOIN sys.master_files mf2 ON mf.database_id=mf2.database_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
and mf2.type_desc = 'LOG'
and mf.type_desc = 'ROWS'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply