March 12, 2008 at 6:58 am
Hello All!
I have just went thought many articles available on internet about backup and recovery and most of them write how important it is to test backups. However, they do not explain what is the best practice to do that.
Could you advise me what is the best way to check if my backup is correct. And after restore, how I can check if nothing is missing?
March 12, 2008 at 7:09 am
The only way to test the backup is to test restore it to another database. You should periodically, perhaps monthly at least, restore a database from a backup that is on tape or disk to ensure that it is working correctly.
Plenty of people have assumed their tapes work, only to find out that there was a hardware or media issue for every single one of their tapes.
March 12, 2008 at 7:13 am
OK, so I need only make restore and that is all. I thought that after restore I need to make some extra tests on databases.
Thank you 🙂
March 12, 2008 at 7:27 am
we restore most db to test servers and every once in a while, we even restore the full sqlinstance (second split network).
and if the business wants it, we can arrange connection for the desired applications.
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
March 12, 2008 at 8:58 am
thank you 😀
March 13, 2008 at 8:44 pm
Another point to verify restore is to get how long it takes to complete the restore, 2 minutes or 2 hours. Better to know than no idea.
You can also ask the application/business user to verify the restored database and sign off as part of restore verification process, depends on your scope.
March 13, 2008 at 11:11 pm
In SQL Server 2005, you can create backups with the CHECKSUM option. This generates a checksum for the backup streams. Then instead of running a full restore, you can run a RESTORE VERIFYONLY statement on the backup file, instead of restoring the entire database. Any inconsistencies/errors will be picked up by mismatched checksums. This does impose a higher load during the backup process, but from our experience, it is marginal.
Note that you can use SQL Server 2005 Express Edition to perform a RESTORE VERIFYONLY on backup files of any size. You are not limited to 4 Gig files.
It is still good practice to periodically perform an actual database restore to your standby server, if only to check that the standby servers' hardware can cope with the growth of the production database and your organization's disaster recovery requirements (speed of restore, disk space availability etc).
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 14, 2008 at 6:51 am
I would say that you should test your restores enough so that you know that you would feel comfortable restoring the db (and possibly the server) in an emergency situation where you are under extreme pressure to restore it successfully (and quickly). 🙂
Having the knowledge of the entire restore procedure "under your belt" during the time of an emergency situation is a good feeling.
Norene Malaney
March 17, 2008 at 6:18 am
and how I can create backups with the CHECKSUM option? May I do it somewhere via MP ?
March 17, 2008 at 6:28 am
checksum is not an option in a maintenance plan gui (not manual, not with the wizard).
However you can check "verify backup integrity".
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
March 18, 2008 at 7:31 am
on SQL server 2005 you have option during backup with GUI on Options "Perform checksum before writing to media" but how to see this Checksum number if you plane to compare with previous backup...?
March 18, 2008 at 8:23 am
From BOL:
CHECKSUM
Requests that the backup operation verify each page for checksum
and torn page, if enabled and available, and also to generate a checksum for the entire backup.
It isn't like generating an external MD5 checksum. The checksum is calculated and stored in the backup file. Should even 1 byte be different, SQL Server will detect this during a restore or a verification process.
This is far more reliable than the verification process in SQL 2000, where the file could be corrupted, but as long as SQL Server is able to read the file, it is deemed to be fine.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 18, 2008 at 8:31 am
the checksum has no relation with previous backups, it only validates the current backup.
You can see a backup contains checksum info by using RESTORE HEADERONLY .
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply