A popular quote by T.E. Ronnebergâ€“”Being too busy to worry about backup is like being too busy driving a car to put on the seatbelt”. So backup is like a seatbelt that helps us to recover the corrupt or deleted database. Some data recovery companies claim that they can recover the database without backup but they will charge for this and the price varies from company to company. So just forget about data Recovery Company; you just need to take backup of your database and regularly update it. Wait, wait! Your job is not finish here. You need to test your backup also.
I believe that you are not just maintaining but regularly updating the backup of your database. Most of the database administrators do this as it is a part of their daily task. But, there is a little correction in this; your backup is only as useful as successful restore. The simplest way to test your backup is restoring it on another server, but there are some additional commands that can be useful in testing the SQL database backup.
Experience: We have scheduled our backup on every Friday of week; personally I restore all my backups to ensure that I can recover the database from any kinds of corruption.
Letâ€™s checkout all of the other commands that will be helpful in validating the databaseâ€™s backup.
RESTORE VERIFYONLY: It is the second best choice to test the database backup that has been completed. It checks the database backup and returns a message that mentions your database backup is valid or not. If it returns your backup is valid that means you can use it to restore the database in case of any disaster strikes to the database otherwise you need to create another backup of same database. Run following command to use this:
1 2 3
RESTORE VERIFYONLY FROM DISK = 'C:\AdventureWorks.BAK' GO
Note: If you have multiple backup in a single backup file then it just checks the first backup.
DBCC CHECKDB: It checks all the logical & physical integrity in the database. To run this on backup; first you need to restore your backup then after run following command:
DBCC CheckDB('database-name') WITH NO_INFOMSGS, ALL_ERRORMSGS
If error messages are receiveÂ after running this command then it specifies that there is something wrong with the database. You canâ€™t rely on this backup.
Tips: Maintain more than one backups of your important database at different locations. As you are relying on the hardware to take backup of your database but as we know hardware is also prone to failure so it is recommended to maintain backup of database on tape or other removable media.
Sum-up: As you are maintaining data for your organization and you know business data is very critical so you need to protect it. Take some further responsibility and test your backup regularly. Donâ€™t forget successful restore is the best way to test every database backup.