In my last post in this ongoing series on SQL Server database corruption I mentioned that my next post would be to provide a ‘soup to nuts’ sample or example of how you can test corruption and recovery in your own environment – as a means of better getting familiar with exactly what corruption is, what it looks like, and how to address it.
To that end, this post closely follows on a previous post where I showcased, in step-by-step fashion, how to simulate corruption in a SQL Server database by using a hex editor to go in and ‘hack’ some of the raw bits of data stored in a copy of an AdventureWorks database.
Of course, that previous post just showcases how to ‘trash’ a SQL Server database – and doesn’t show how to recover from that ‘trashing’. As such, if you’d like to walk through the process of ‘recovering’ from corruption (in a simulated environment or setting), here’s what you’ll want or need to do.
Of course, the list of operations I’ve provided above are a BARE MINIUMUM set of steps that you’ll want or need to get your hands dirty with corruption. As such, I’m hoping that you’ll run through the steps listed above until you get comfortable with what’s going on. Then I’m hoping you’ll get a bit reckless and start ‘adding’ to the steps above by playing around with many of the different questions you’re bound to have about what happens when you do X or Y or don’t have Z and so on. Because, again, the whole point of this post is to provide you with a ‘skeleton’ framework (if you will) that will let you try out dealing with corruption in a controlled environment – so that you’re THAT much more comfortable and capable of dealing with it in production WHEN it occurs as corruption is always a case of WHEN not IF.
And, to that end, I realize that some of the steps outlined above MIGHT be a bit cryptic if you’re just getting started with SQL Server or haven’t played with some of the commands, options, arguments listed above. As such, what follows is a full-blown script that I’ve used to demonstrate the whole process outlined above in previous demos/presentations:
-- set recovery to FULL: ALTER DATABASE AdventureWorks SET RECOVERY FULL GO -- PURGE suspect_pages as part of the demo: TRUNCATE TABLE msdb..suspect_pages GO -- Get a new Backup: BACKUP DATABASE AdventureWorks TO DISK = 'D:\SQLBackups\AdventureWorks.BAK' GO -- and a t-log backup: BACKUP LOG AdventureWorks TO DISK = 'D:\SQLBackups\AdventureWorks.TRN' GO -- Turn SQL Server off to start simulating corruption: SHUTDOWN WITH NOWAIT -- Use a hex editor (HxD) and go, for example, to offset 02177190 -- to the AW00xxx keys, replace a bunch of them with 000000s. -- Then restart SQL Server -- show that the DB is online... SELECT * FROM AdventureWorks..sysobjects -- check for corruption: DBCC CHECKDB(AdventureWorks) WITH NO_INFOMSGS, ALL_ERRORMSGS -- Talk through demo and stuff... about how to repair/recover and so on. -- then, talk about backups and stuff (show that we took them earlier) -- Get a report on Corruption: SELECT * FROM msdb..suspect_pages -- Recover: USE master GO ALTER DATABASE AdventureWorks SET RESTRICTED_USER GO -- TODO: replace the page #(s) here with data from suspect_pages. RESTORE DATABASE AdventureWorks PAGE = '1:xxxx' FROM DISK = 'D:\SQLBackups\AdventureWorks.BAK' WITH NORECOVERY GO -- previous log (from before corruption): RESTORE LOG AdventureWorks FROM DISK = 'D:\SQLBackups\AdventureWorks.TRN' WITH NORECOVERY GO -- CURRENT log: BACKUP LOG AdventureWorks TO DISK = 'D:\SQLBackups\AdventureWorks_TAIL.TRN' GO RESTORE LOG AdventureWorks FROM DISK = 'D:\SQLBackups\AdventureWorks_TAIL.TRN' WITH RECOVERY GO -- now query it: SELECT * FROM AdventureWorks..sysobjects GO -- and check it: DBCC CHECKDB(AdventureWorks) WITH ALL_ERRORMSGS, NO_INFOMSGS GO -- bring it back online for users: ALTER DATABASE AdventureWorks SET MULTI_USER GO -- and truncate/cleanup suspect_pages: TRUNCATE TABLE msdb..suspect_pages GO
Just make sure to pay attention to the comments and follow all instructions and you should be fine.
Part XIII: Recap