SQL Server data loss is a very common issue and a regular user with a backup file from the time when the database was healthy can easily restore the SQL Server database to its original state. In some cases, recovering lost data is also possible as most of the times, the cause for data loss is hardware failure or some application errors that leave behind traces in form of transactions that haven’t been written into main storage. However, restoring a badly damaged database takes more effort and may even involve huge expenditure if your organization uses high-end servers hosting large databases with thousands of tables and hundreds of columns. If you are working as a DBA at an enterprise level hardware or if you work for a bank or a brokerage firm where uptime guarantee for online transactions is a must, you cannot afford to lose any of the data. In such cases, it is always better to restore lost SQL Server data from a backup instead of trying to recover it from the damaged system. If your enterprise does not have automatic backups enabled on all production environment servers or if an automated method for generating transaction logs is not in use at your organization, you can take help of Transact-SQL (T-SQL) command called RESTORE VERIFYONLY which will help you verify and validate the integrity of SQL Server database structures. This command creates copies of individual pages in an offline database or backs up entire files containing multiple databases or log files. It then restores these page images into your online/production environment and verifies the SQL Server data and structures. This command can be used to check and verify the integrity of your online production database as well as your backup file.

Luckily, you don’t need to run this T-SQL command against a production environment server as it is designed for data recovery purposes only. As an alternative, you can also take help of Microsoft Data Protection Manager (DPM) which takes automated cumulative daily backups for Exchange and SQL Servers that are kept on disk until they are backed up. The utility restore verification process validates all database pages including checksum tests for bit-level comparisons between two sets of objects using a byte by byte comparison. DPM automatically detects any corruption issues during the recovery process and takes a point in time recovery from a protected data source to a recovery point that you specify.

In this post, we will see how to use RESTORE VERIFYONLY command for checking and validating the SQL Server database structures and how to use DPM for taking a point in time recovery from a protected set of files.

Let’s Begin!

1] Take a backup of your damaged or corrupted SQL Server Database using any one of the methods listed below: Log Shipping Replication Full Database Backup Differential Backup

2] Restore the backed up copy of SQL server database on another instance by restoring from either a .bak file created from full or differential backups or from transactional logs. The command can be used against both OLTP as well as Data Warehousing databases without having any special prerequisites.

3] Once you restore the SQL Server database, open a new query window and run the below command against restored database: RESTORE VERIFYONLY FROM DISK = ‘C: \Program Files\Microsoft SQL Server\MSSQL10_50. <Instance name>.MSSQLSERVER\MSSQL\Backup\<backup files>’ WITH NORECOVERY; This will check and validate all columns, indexes, storage structures etc of your online/live production environment data.

4] The syntax for this T-SQL command is as follows: RESTORE DATABASE <name of the database> FROM <backuptype> [WITH <parameters>] [;]

5] If you want to generate a point-in-time recovery script, run the below command: RESTORE DATABASE <name of the database> WITH RECOVERY;

6] The above command will automatically create a script that enables you to restore your damaged or corrupted data into an empty copy of its original location.

7] Instead of running this T-SQL command against your production environment, you can also take help of Microsoft Data Protection Manager for taking a point in time recovery from protected set of files. To learn more about how to recover SQL Server Database using Microsoft Data Protection Manager (DPM), please refer to our detailed step by step guide.

8] For any further clarifications regarding how to use RESTORE VERIFYONLY command, please feel free to leave a comment.

Conclusion:

In this post, we learned how to use the RESTORE VERIFYONLY command for checking and validating the SQL Server database structures along with using DPM for taking a point in time recovery from protected set of files.