{"id":33,"date":"2012-02-01T23:47:56","date_gmt":"2012-02-02T05:47:56","guid":{"rendered":"https:\/\/www.sqlphilosopher.com\/wp\/?p=33"},"modified":"2012-02-02T21:31:31","modified_gmt":"2012-02-03T03:31:31","slug":"perform-dbcc-checkdb-weekly-using-red-gate-sql-virtual-restore","status":"publish","type":"post","link":"https:\/\/www.sqlphilosopher.com\/wp\/2012\/02\/perform-dbcc-checkdb-weekly-using-red-gate-sql-virtual-restore\/","title":{"rendered":"Perform DBCC CheckDB Weekly using Red-Gate SQL Virtual Restore"},"content":{"rendered":"

There are many articles on the Internet explaining why it is important to check the consistency of all of your databases on a regular basis.\u00a0 If you are not made aware of corruption in your system as soon as possible, it is likely that you could miss a growing problem until you are in a crisis.\u00a0 However, even though we know consistency checking is important, it can be hard to find a time when you can afford the large performance hit upon your production machines, due to the resource intensity of CheckDB.<\/p>\n

So, rather than run CheckDB on my production boxes, I made the choice to take the latest full backup and restore it to a test server, and run CheckDB against that every week.\u00a0 While this does not avoid the possibility of corruption happening since the last full backup, it does let me know that, at the very least, my last full backup is safe.\u00a0 I also get the added built-in benefit of testing my restore strategy each week.<\/p>\n

However, this poses other problems.\u00a0 Some of my databases are quite large, and doing a full restore takes a considerable amount of time and disk space.\u00a0 This is where Red-Gate<\/a> comes in.<\/p>\n

Red-Gate recently added a tool to their arsenal called SQL Virtual Restore<\/a>.\u00a0 This tool allows the DBA to use Red-Gate’s HyperBac Filter Driver<\/a> to allow you to restore a virtual copy of a database using far less space, spending less time, and all utilizing native SQL Backup and Restore syntax.\u00a0 SQL Virtual Restore references the backups you supply to it, creates custom “indexes” on your local disk (to help Red-Gate transcend your backups quickly), and creates small empty structures for your files (virtual database files).\u00a0 This allows you to have an unaltered database backup serve your reads and the virtual database files serve your writes.\u00a0 The savings are truly amazing:<\/p>\n

\"\"<\/a>With this tool at hand, I next needed to get my scripts written.<\/p>\n

I already have a harness for this sort of maintenance – I have an instance dedicated to DBA tasks.\u00a0 Running in this instance, are different jobs that perform tasks at different intervals; every 15 minutes, every hour, day, week, so forth.\u00a0 These jobs fire off SSIS packages which collect information about my instances in my environment, such as which backups ran successfully in the last week, what security changes have occurred, which databases have datafiles which are going to grow soon, which indexes need attention, which jobs are running longer than usual, and so forth; all based off of a list of instances that I update whenever I add or modify a SQL instance to my environment.<\/p>\n

So, since I already have all of the information I will need to write my restore and CheckDB scripts, I am all set.\u00a0 I created a stored procedure that would:<\/p>\n