May 6, 2008 at 9:05 pm
When performing a database restore for testing purpose, what are the logs/evidences DBA needs to have as a proof of success?
From SQL errorlog, the entry saying production database ABC is restored successfully is not enough as DBA can create an empty ABC database, backup & restore to get this entry.
I am thinking to run a dbcc checkdb or verify using application side script against the database. Wondering whether anyone else has different ways of doing it.
This is not only for SQL Server. Any suggestions for Oracle/DB2/Sybase platform?
May 7, 2008 at 6:03 am
We pretty much rely on the application side checks after a successful restore. As you point out, restoring successfully doesn't mean you restored the right thing successfully. DBCC and other internal checks won't identify if you've restored last week's data or a blank database. Again, the measure you're looking for isn't successful, it's correct and that's just more of a business/application measure than an internal one that can be checked within a set of DB code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2008 at 9:29 am
Unless you're snagging rowcounts when you backup, how can you determine things are successful? How do you know you restored the right file?
It's an interesting question, and ultimately you might need someone that uses the database to sign off that it was "successful."
May 8, 2008 at 1:07 pm
I totally agree with Grant and Steve. I restore to a full test environment, log in using the various apps and do a general look around. About twice a year, it's a full DR test, including the end users. We have them run all appropriate reports in production and preserve them for testing. I'll take a full backup immediately afterwards, restore it to the test environment and they have their go to verify what was there before the backup is still there. All major functionality is processed. At the end of the drill, if all went well, they sign off that the DR process worked, the data was accurate and major functionality was still in place. My boss and the company owners are then satisified knowing we can get back on our feet in the event of disaster. A very high prority for them so it makes the end users that much more willing (not eager!) to help!
-- You can't be late until you show up.
May 9, 2008 at 6:07 pm
One of the things to check is logins. Have a couple of users try to connect. Sometimes restoring a database (especially to a different server) causes orphaned logins.
We are not the owners of the data, so we ensure the database actually restored, no errors in the logs, and the database size is correct. It is our users responsiblity to confirm the data is correct. They are the only ones who know what it is supposed to be like.
-SQLBill
May 11, 2008 at 9:15 pm
I am thinking whether SQL errorlog should reveal more restore details which includes
1) Source database name – the restored DB name may be different than the source DB name
2) Backup timestamp – backup filename (if via maintenance plan) usually has the db name with a timestamp, but the timestamp is the backup starting time not the cut off time of the data has been backed up.
3) Restore timestamp: should be the backup cut off time or the point in time if applicable.
This way, DBA will have a proof of a successful restore from SQL Server side. Backup set has this information already, and it should be exposed when restore is in place.
May 12, 2008 at 7:41 am
This goes against your first statement, however, that an empty database could be created and then restored and you'd have a log entry.
Proof to justify something would be checking the error log or some system table( might be stored somewhere and I don't know it). Proof to be sure it works means checking from an application somehow.
May 12, 2008 at 7:57 am
I still think it all comes down to a business verification. I can't tell you how many times I've gotten the word to restore the backup for a certain date. I did this. Successfully. Only to be told that's not the date that they want, even if it is the one they asked for. Verifying that you've restored the stuff you thought you restored, from our end, is just inadequate to the whole issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 12, 2008 at 2:42 pm
I agree. Seems SQL Server currently do not have the exact proof unless the business/application says successful based on their criteria.
If SQL Server can present more details about restore, DBA can get more fact /support.
May 12, 2008 at 3:05 pm
Why not use a variable as a returncode?
DECLARE @iReturnCode int
EXEC @iReturnCode = EXEC dbo.my_RestoreSP
You'll get either a 0 or an error returned. This is what I do for my backup & restore ops....
Your friendly High-Tech Janitor... 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply