My poetic output this week, as inspired by things I've run into at work lately.
"where'd my emails go?"
"you archived to your C: drive.
we don't back that up."
what the hell is this?!
full recovery model
but no log backups!
content in the cloud
fancy phrase but you just mean
stuff on a website
28 September 2012
25 September 2012
Let's Break Something
Here's a fun little exercise for the bored DBA. Break your database.
When I say your database, I don't mean your company's database, or anything anywhere remotely within earshot of a production machine. But I have some sample databases on an eval version of SQL Server 2012 that would be prime sacrificial lamb material. The goal is to simulate a worst case scenario and recover from it. I've done this once before, but it has been long enough that I've forgotten exactly how I did it, so we'll give it a bash.
First, take a backup.
Then, reread that first step and make sure you did it. I'll wait.
...
Backup taken? Are you sure? OK then, so next I'll need you to open You really did take a backup right? Let's just make sure of that. OK, I'll trust you at this point. So next you'll need to open SQL Server Configuration Manager and shut your instance down. Once the server is switched off (you will find it under SQL Server Services, right-click and click Stop on the relevant SQL Server instance, if more than one), you can have unfettered access to the data files, and we can do all sorts of unmentionable atrocities to them.
No time like the present, so browse to your data file, right click the database THAT YOU HAVE PREVIOUSLY TAKEN A BACKUP OF, AND THAT IS NOT AN IMPORTANT DATABASE IN ANY RESPECT, AND THAT IS NOT ON ANYTHING AKIN TO A PRODUCTION SERVER, and open in a text editor. Highlight some text (any text!) and replace with something pithy...I have chosen "My hovercraft is full of eels". Be creative, muck up as much of the data as you wish.
Now on to the fruits of our labours...restart the service in Configuration Manager. Then launch Management Studio. Look at that...our database with a "Recovery Pending" note affixed. Data is unaccessible. Hmmm! Seems different slightly than how things reacted in 2008R2, so let's play around and see how it goes.
In the error logs:
So it knows what we did. Alright, fine, SQL Server, I'll do what you say. dbcc checkdb returns "Database [name] cannot be opened due to inaccessible files or insufficient memory or disk space." Dandy! Thank you SQL Server for telling me to do what you then tell me I can't do. It's data loss acceptance time, folks. A quick restore of a full backup and we're back up.
Let's have some more "fun"! This time, I repeated the experiment, but went in and changed one single character to another character. Pretty mild change, innit? Maybe SQL Server won't notice? Same error, same severity level, same unrecoverable database. So restored from backup again, and this time, I monkey with the log file LDF instead of the actual data file. No errors reported and no loss of functionality (at least, apparently!). But, my database was in simple recovery model. Wondering if that had anything to do with it, I switched to full recovery model and corrupted the logfile again. That did it, the reassuring "Recovery Pending".
So, a slightly smaller severity number, good sign eh? So first I execute ALTER DATABASE [name] SET EMERGENCY. This puts it in an "emergency" state and turns the tan database icon to an attractive shade of pale red, indicating that SOMETHIN' IS WRONG RIGHTCHERE!!! I tried running CHECKDB with the REPAIR_ALLOW_DATA_LOSS parameter, and it complained about it needing to be in single user mode. So ALTER DATABASE [name] SET SINGLE_USER, and then DBCC CHECKDB ([name], REPAIR_ALLOW_DATA_LOSS). Here's the relevant result:
OK, so it rebuilt the log for me! Switched it out of single user mode, and voila, we're back to normal. Note that (as one might expect) the restore chain is broken, so in "real life" this would be a good time to take a full backup and start fresh with your log backups.
I've poked at SQL Server in an inappropriate manner enough this morning, so I shall give it a rest for the time being.
When I say your database, I don't mean your company's database, or anything anywhere remotely within earshot of a production machine. But I have some sample databases on an eval version of SQL Server 2012 that would be prime sacrificial lamb material. The goal is to simulate a worst case scenario and recover from it. I've done this once before, but it has been long enough that I've forgotten exactly how I did it, so we'll give it a bash.
First, take a backup.
Then, reread that first step and make sure you did it. I'll wait.
...
Backup taken? Are you sure? OK then, so next I'll need you to open You really did take a backup right? Let's just make sure of that. OK, I'll trust you at this point. So next you'll need to open SQL Server Configuration Manager and shut your instance down. Once the server is switched off (you will find it under SQL Server Services, right-click and click Stop on the relevant SQL Server instance, if more than one), you can have unfettered access to the data files, and we can do all sorts of unmentionable atrocities to them.
No time like the present, so browse to your data file, right click the database THAT YOU HAVE PREVIOUSLY TAKEN A BACKUP OF, AND THAT IS NOT AN IMPORTANT DATABASE IN ANY RESPECT, AND THAT IS NOT ON ANYTHING AKIN TO A PRODUCTION SERVER, and open in a text editor. Highlight some text (any text!) and replace with something pithy...I have chosen "My hovercraft is full of eels". Be creative, muck up as much of the data as you wish.
Now on to the fruits of our labours...restart the service in Configuration Manager. Then launch Management Studio. Look at that...our database with a "Recovery Pending" note affixed. Data is unaccessible. Hmmm! Seems different slightly than how things reacted in 2008R2, so let's play around and see how it goes.
In the error logs:
Error: 824, Severity: 24, State: 2.
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:9; actual 0:0). It occurred during a read of page (1:9) in database ID 8 at offset 0x00000000012000 in file '[path to data file]'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
So it knows what we did. Alright, fine, SQL Server, I'll do what you say. dbcc checkdb returns "Database [name] cannot be opened due to inaccessible files or insufficient memory or disk space." Dandy! Thank you SQL Server for telling me to do what you then tell me I can't do. It's data loss acceptance time, folks. A quick restore of a full backup and we're back up.
Let's have some more "fun"! This time, I repeated the experiment, but went in and changed one single character to another character. Pretty mild change, innit? Maybe SQL Server won't notice? Same error, same severity level, same unrecoverable database. So restored from backup again, and this time, I monkey with the log file LDF instead of the actual data file. No errors reported and no loss of functionality (at least, apparently!). But, my database was in simple recovery model. Wondering if that had anything to do with it, I switched to full recovery model and corrupted the logfile again. That did it, the reassuring "Recovery Pending".
Error: 9004, Severity: 21, State: 1.
An error occurred while processing the log for database [name]. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
So, a slightly smaller severity number, good sign eh? So first I execute ALTER DATABASE [name] SET EMERGENCY. This puts it in an "emergency" state and turns the tan database icon to an attractive shade of pale red, indicating that SOMETHIN' IS WRONG RIGHTCHERE!!! I tried running CHECKDB with the REPAIR_ALLOW_DATA_LOSS parameter, and it complained about it needing to be in single user mode. So ALTER DATABASE [name] SET SINGLE_USER, and then DBCC CHECKDB ([name], REPAIR_ALLOW_DATA_LOSS). Here's the relevant result:
Warning: The log for database [name] has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
OK, so it rebuilt the log for me! Switched it out of single user mode, and voila, we're back to normal. Note that (as one might expect) the restore chain is broken, so in "real life" this would be a good time to take a full backup and start fresh with your log backups.
I've poked at SQL Server in an inappropriate manner enough this morning, so I shall give it a rest for the time being.
24 September 2012
Nice Fluffy Backups, But Where's the Restore?
I am shirking tradition (as I am wont) (indeed, I am wonter than most) in abandoning my obstinate refusal to provide post titles. There is a tide in the affairs of men, which, taken at the flood, leads on to fortune, and such tide apparently entails providing handy titles to blog posts (not nearly so laden with euphemism as you might assume).
Call me a sys admin by trade, and a DBA at heart...since 2005 I've been the de facto DBA for the companies I've worked for (which were small enough to not justify a full time DBA), and I have a bit of a maturing interest/passion for SQL Server. This morning, spurred on by an excellent article over at SQL Server Central, I was beset with enough worry and consternation that I decided to test my backups. The whole concept of a backup is a healthy lack of trust. You take backups because you cannot trust that your data will survive in its data file containers...threatened either by data corruption, user malfeasance, or actual catastrophic happenings in the datacenter. If you trusted your database, you wouldn't bother with backups, there would be no need for them. So you take your regular full backups and constant log backups, like a good DBA ought.
Here's where so many of us, myself included, cast aside our mistrust and skepticism and become a rabble of dumbly trusting, credulous gits. We require confirmation that backups are taken, but we trust that they were taken properly and can be restored without corruption. Backup files are just another cog in the machine that can fail. Any component can fail, and we must plan for any type of failure. Simply trusting that once SQL Server Agent flashes us the A-OK sign after taking backups, that we are guaranteed the safety and security of our data, is not enough.
Couple o' links:
Referenced article: The Importance of Validating Backups
And the Mighty Ola Hallengren's db maintenance scripts: http://ola.hallengren.com/
Also, managed to snag a new (used) sitar the other day, a 7 year old Shrishti model that was worked over by the inimitable Tony Karasek with a new delrin bridge. Has a great strong sound on the baaj, and the chikaris have a very clean, closed jawari tone to them. Was originally kharaj pancham but I've restrung it to gandhar pancham.
And that's it for this week. Toodle pip!
Call me a sys admin by trade, and a DBA at heart...since 2005 I've been the de facto DBA for the companies I've worked for (which were small enough to not justify a full time DBA), and I have a bit of a maturing interest/passion for SQL Server. This morning, spurred on by an excellent article over at SQL Server Central, I was beset with enough worry and consternation that I decided to test my backups. The whole concept of a backup is a healthy lack of trust. You take backups because you cannot trust that your data will survive in its data file containers...threatened either by data corruption, user malfeasance, or actual catastrophic happenings in the datacenter. If you trusted your database, you wouldn't bother with backups, there would be no need for them. So you take your regular full backups and constant log backups, like a good DBA ought.
Here's where so many of us, myself included, cast aside our mistrust and skepticism and become a rabble of dumbly trusting, credulous gits. We require confirmation that backups are taken, but we trust that they were taken properly and can be restored without corruption. Backup files are just another cog in the machine that can fail. Any component can fail, and we must plan for any type of failure. Simply trusting that once SQL Server Agent flashes us the A-OK sign after taking backups, that we are guaranteed the safety and security of our data, is not enough.
Couple o' links:
Referenced article: The Importance of Validating Backups
And the Mighty Ola Hallengren's db maintenance scripts: http://ola.hallengren.com/
Also, managed to snag a new (used) sitar the other day, a 7 year old Shrishti model that was worked over by the inimitable Tony Karasek with a new delrin bridge. Has a great strong sound on the baaj, and the chikaris have a very clean, closed jawari tone to them. Was originally kharaj pancham but I've restrung it to gandhar pancham.
And that's it for this week. Toodle pip!
Subscribe to:
Posts (Atom)