Corruption recovery using DBCC WRITEPAGE

A couple of week ago I kicked off a survey about the extent of your experience with the DBCC WRITEPAGE command. Here are the results: The “Other” values are: 9 x “Read your post on it, may practice it one day, but it’ll always be last resort to use.” 6 x “I read your post […]

More on using Transaction SID from the transaction log

Back in 2012 I blogged about using fn_dblog and fn_dump_dblog to figure out the point at which something occurred that you’d like to restore to just before (e.g. a table drop). I also mentioned that you can use the SUSER_SNAME () function on on the [Transaction SID] column for the LOP_BEGIN_XACT log record of the […]

Disaster recovery 101: restore master or rebuild master

It’s been a hectic January with client work and preparations for our 2014 classes so I haven’t blogged as much as I wanted, but I have lots of blog posts coming up over the next month! A few weeks ago I kicked off a survey on whether you’ve ever tried or had to restore or […]

New online course: Detecting and Correcting Database Corruption

My latest Pluralsight online training course went live today: SQL Server: Detecting and Correcting Database Corruption It’s 4 hours long and is the first in a two-part series, with a more advanced course coming in a few weeks. The modules in this course are: Introduction Causes of Database Corruption Detecting Page Corruption Consistency Checking DBCC […]

The Accidental DBA (Day 11 of 30): Backups: Backup Storage and Retention

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

The Accidental DBA (Day 10 of 30): Backups: Backup Testing for Validation

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

The Accidental DBA (Day 9 of 30): Backups: Essential BACKUP Options

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

The Accidental DBA (Day 8 of 30): Backups: Planning a Recovery Strategy

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

The Accidental DBA (Day 7 of 30): Backups: Recovery Models and Backup Types

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

The Accidental DBA (Day 6 of 30): Backups: Understanding RTO and RPO

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

DBCC WRITEPAGE: an introduction

(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.) SQL Server’s undocumented commands, options, and trace flags are sometimes carefully-guarded secrets. I’ve been a major secret-keeper of lots of these, having written/re-written a bunch of the undocumented DBCC commands. I’ve been […]

SQL Server disaster-recovery poster now available

A while back I wrote a comprehensive disaster-recovery flow chart in poster form for SQL Server Magazine, and I’m very happy that it’s now being made available! Edit 2/7/13: SQL Mag pulled the poster from their site again, but you can download it from here. The poster has links to a page with more explanations […]

Corruption demo databases and scripts

I originally blogged a series of corruption demos and associated databases back in 2008, for use with SQL Server 2005 and 2008. Since then the releases have changed which databases and corruptions work and I’ve had to rework some of the databases for you. This is an update that takes into account SQL Server 2008R2 […]

Importance of how often you take full backups

A couple of weeks ago I kicked off a survey asking how often you perform full backups of your databases. Here are the results: The ‘Other’ values are: 20 x ‘Daily for small dbs, weekly (with daily diff) for large dbs.’ 16 x ‘Depends. Some daily, some weekly.’ 5 x ‘Daily on small, weekly on […]

Importance of where you store your backups

A couple of weeks ago I kicked off a survey about where you store your backups. Here are the results: The’Other’ responses are: 8 x ‘Stored on same SAN, different LUN as databases, with off-site copies.’ 6 x ‘Copy 1 stored on same SAN (1-2 days) for quick restore in non-physical disaster. Copy 2 stored […]

When the going gets tough, the tough hide under the table

That's a quote from one of my favorite comedy shows, Blackadder.  With Hurricane Sandy bearing down on the North East Coast of the US today, whether you're in its path or not, it's a good time to consider what your reaction will be when faced with a disaster that affects the data you're responsible for. […]

Multiple log files and why they’re bad

About a month ago I kicked off a survey with some code to run to figure out how many log files your databases have (see here). There are all kinds of misconceptions about transaction logs and how to configure them (and how they work) and I'm amazed at the misinformation I continue to see published. For […]

Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN

I’ve blogged a bunch about using the undocumented fn_dblog function I helped write (and I’ve got a lot more to come :-) but here’s one I haven’t mentioned on my blog before: fn_dump_dblog (although I have talked about it at conferences last year). Here’s a scenario: someone dropped a table and you want to find […]

SQL Server Magazine: feature article on using database repair

The September SQL Server Magazine articles are now available on the web and include my latest feature article on Using Database Repair for Disaster Recovery. It includes a detailed walk-through of a disaster scenario where all backups include the corruption – showing you how to run repair and then try to recover some of the […]

TechNet Magazine article: data protection and the corporate jigsaw puzzle

My latest feature article for TechNet Magazine has just been published in the April edition. It focuses on planning an HA/DR strategy within the confines of a larger corporate IT strategy, from multiple perspectives but really focusing on the IT manager role and how to interact both up (to business managers) and down (to DBAs). […]

Human nature is a significant hurdle to successful disaster recovery

A few weeks ago I kicked off a survey about disaster recovery testing and how the plan copes with human factors. You can see the original survey here. This Dilbert cartoon is a pretty accurate portrayal of most clients' disaster recovery plans when I first start working with them: http://dilbert.com/strips/comic/2000-08-15/. Here are the survey results:   […]

Surveys: DR plan testing and considering human nature

The recent earthquake in New Zealand and this morning's devastating earthquake and subsequent tsunami in Japan set off a lot of discussion on twitter about DR planning and testing. I wrote a blog post on our SQL Server Magazine blog this morning asking people to think about their testing – see here. I'm conducting two […]

Disaster recovery 101: fixing metadata corruption without a backup

(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.) Yesterday on the MVP newsgroup someone was asking how to fix some metadata corruption when their backups all had the corruption in too. There are two options: export everything out to a […]

Updated database size survey results

Last week I kicked off a survey about the size of the largest single SQL Server database in your company – see here for the survey. I was interested to see how the distribution has changed since I did the same survey a few years ago. 2011 results:   2009 results:   Very interesting! As […]

Unbelievable tale of disaster and recovery

A few days ago one of my new blog readers (a pretty smart cookie, as you'll see) sent me a tale of database catastrophe and an excellent recovery that I’d like to share with you. The story’s been made anonymous and is published with full permission of the author (highlights in bold are mine). Hey […]

Disaster recovery 101: hack-attach a damaged database

(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.) As a follow-up to my previous post Disaster recovery 101: backing up the tail of the log, I want to describe how to re-attach a damaged database if someone’s detached it accidentally while […]

Disaster recovery 101: backing up the tail of the log

(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.) One of the first things you should always check when a database has been damaged and you’re going to perform a restore operation is whether you need to back up the tail […]

Whitepaper on high-availability architectures published

The 20-page whitepaper I wrote this Spring for the SQL team has just been published. This whitepaper is titled Proven SQL Server Architectures for High Availability and Disaster Recovery and ties in with the longer whitepaper I wrote last year on High Availability with SQL Server 2008 (see here). This new whitepaper describes the five […]

A SQL Server DBA myth a day: (30/30) backup myths

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) The month is finally over so time for the grand […]

Important things for DBAs to consider

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Over the last month or so I've found myself handing […]

Sample corrupt databases to play with

The very worst piece of advice I ever saw on the Internet was in response to someone asking on a SQL newsgroup ‘how can I create a corrupt database?’. The first response was: When I want to corrupt a database to play with, I go into the data center, find a hard-drive and flick the […]

TechNet Magazine: feature article on recovering from disasters using backups

The November 2009 edition of TechNet Magazine is up on the web and contains my latest feature article, the second in a 3-part series on backups/restores/repairs. In this article I explain all about using the RESTORE command. Not much point having backups if you don't know how to use them! Topics include: The four phases […]

Importance of having a good disaster recovery plan

A few weeks ago I kicked off a survey asking what kind of disaster recovery guide/run-book/plan (I'll just call it a plan from now on) you have (see here for the survey). Here are the results as of 9/15/09:   Out of all these answers, IMHO the last answer is the only acceptable one for […]

Survey: what kind of disaster recovery guide do you have?

In this survey I'd like to find out what kind of disaster recovery guide/run-book/plan you have, if any. When a disaster occurs, do you just wing-it, or is there something written-down that was worked out in advance to help you along the way. I'll report on the results in a couple of weeks. *Please* no comments […]

Importance of defining and measuring SLAs

In last week's survey I asked you two things, as a precursor to a whitepaper I'm writing for Microsoft The first question was what is your maximum allowable downtime SLA (either for 24×7 operation or not). See here for the survey. Here are the results as of 5/30/09.   The Other values were all about not having […]

Weekly survey: maximum allowable downtime – target and actual

This week's survey is inspired by this morning's Kimberly+Paul hot-tub conversation around data-dependent routing vs. network load balancing, which then turned to SLAs. Yes, we lead an exciting life . This survey is a *four*-parter. Part 1 is for each of your databases, which survey option is closest to your target maximum allowable downtime SLA (Service-Level […]

Importance of testing your disaster recovery plan

In last week’s survey I asked whether you’re ever tested your disaster recovery plan, and if so, what happened? (See here for the survey). Here are the results as of 5/25/09: The ‘other’ responses are: 2 x “restored to test env regularly. don’t know if sla would be met.” 2 x “test it regularly, most […]

Weekly survey: have you ever tested your disaster recovery plan?

This week's survey is inspired from many stories I saw on the forums and Twitter this week – mostly bad, one good (someone I'm following is spending the weekend testing their disaster recovery plan – cool!). I'd like to know whether you're ever tested your disaster recovery plan, and if so, what happened? I'll report […]

Misconceptions around database repair

This week's been a busy one on the forums and Twitter, with lots of interesting problems people are hitting. One of the things I've noticed is that there are lot's of misconceptions about running repair, so to round out Friday I'm going to run through a list of them for you. Here are the misconceptions, […]

A sad tale of mis-steps and corruption (from today)

This is a true story, and unfolded over the last few days. It's deliberately written this way, I'm not trying to be patronizing – just illustrating the mistakes people can make if they don't know what not to do. Once upon a time (well, a few days ago), there was a security person who had access […]

How to get all the corruption messages from CHECKDB

Hopefully all of you reading my blog already know to use the WITH ALL_ERRORMSGS option whenever you run DBCC CHECKDB (which is now the default in 2005 SP3, haven't checked if it made it into 2008 SP1). If you're doing some quick analysis of the results to see, for example, whether it's just nonclustered indexes […]

Importance of having the right backups

Last week's survey was on what kind of backups you take, along with the recovery model used (see here for the survey). Here are the results as of 5/2/2009.   The 'other' responses were combinations of the other answers. This survey is a bit of a pre-cursor to my article on Understanding SQL Server Backups […]

Weekly survey: what kind of backups do you take?

In this week's survey I'm interested in what kind of backups you take, and also what recovery model you mostly use. If you have multiple database with different strategies, by all means respond multiple times. The more responses the better! I'll report on the survey results some time over the weekend of 5/2/2009. A couple […]

Physical database layout vs. database size

A couple of weeks ago I kicked off the latest survey, on what the physical layout of your databases are and why you have them that way (see here for the survey). I let the survey run for a while to get a good sampling, and I wasn't disappointed, with over 1000 responses! Here are […]

Weekly survey: does size really matter – or is it what you do with it?

This week's survey is a little more complicated. I'm interested in the physical layout of your databases. I've got four surveys, for a variety of database sizes. Please vote multiple times in each survey, as you see fit – and by all means forward this link to your friends/clients/etc or re-blog/tweet it. I'm going to report […]

Is running repair on msdb safe?

Just saw this on a forum – running REPAIR_ALLOW_DATA_LOSS on msdb to cope with corruption. Yes, this will work but it's certainly not safe and something I'd only recommend as a last resort. Here's why: msdb stores your backup history and your Agent jobs. REPAIR_ALLOW_DATA_LOSS works by usually "delete what's broken and fix up all […]

Importance of validating backups

Last week I kicked off the first weekly survey – on whether you validate your backups or not (see here for the survey). The results are very interesting (as of 3/13/09): As you can see, almost 25% of respondents never validate their backups! And a further 25% only validate them occasionally, with 30% doing some […]

TechNet Radio interview on database corruption

Well, we're back from vacation finally (only for 10 days and then off to India and Thailand for 3 weeks… phew) and I've got a bunch of blogs posts to catch up on. First up – I did an interview with TechNet Radio in mid-December where I talked about database corruption and things to do […]

TechEd demo: nonclustered index corruption

This blog post describes the demo "2 – NC Indexes" from my Corruption Survival Techniques conference session from various conferences in 2008. The links to the scripts and databases to use are in this blog post. Edit 6/4/2012: Be aware that in versions of SQL Server from 2008 onwards, you may only be able to […]

TechEd: 80 minute video of Corruption Survival Techniques presentation

For those of you who couldn't make it to a conference this year where I presented my Corruption Survival Techniques session, the folks at TechEd EMEA have just posted an 80 minute long video of the presentation I did in Barcelona in early November. It walks through I/O errors, what CHECKDB does, how it works, […]

TechEd demo: corruptions fatal to DBCC CHECKDB

This blog post describes the demo “1 – Fatal Errors” from my Corruption Survival Techniques conference session from various conferences in 2008. The links to the scripts and databases to use are in this blog post. The aim of this demo is to show that sometimes a database is so corrupt that DBCC CHECKDB just […]

Corruption survival techniques – useless?

Now, I’m very thick-skinned and I know there are always some people in a conference session who don’t agree with everything I say (that’s human nature, and I’m totally cool with that) but this one I just couldn’t pass up mentioning here on the blog as I *utterly* disagree with the advice in that post, […]

Conference corruption demo scripts and example corrupt databases

This blog post explains the demo scripts and databases I've posted to cover all the Corruption Survival Techniques and DBCC CHECKDB sessions I've presented at conferences this year. There are two zip files you need to download: the example corrupt databases (36-MB zip) (and this one if you're on 2008) and the demo scripts. These […]

Planning a backup strategy – where to start?

It's been almost exactly a week since the last post – an unusually long time for me. Kimberly and I were teaching the first week of the inaugural Microsoft Certifed Masters SQL course last week in Redmond (a little intense at 8 hours of *teaching* per 10-hour day – so no energy for blogging afterwards). […]

Reminder of why hardware redundancy is a good thing

One of our customers (and friends!) sent us this last week – a stark reminder why hardware redundancy is a good idea!   The controller card in one of his servers literally blew up at 7.15am Tuesday morning. Although they didn't have redundant hardware, they were back online by 9am – pretty good going. Funnily […]

Can the hack-the-suspect-database-into-the-server trick work for corrupt file headers?

(Quickie post #3 while it's Kimberly's turn to lecture this morning – better pay attention now before I get into trouble) Gail asked a (paraphrased) question about the trick to hacking a detached suspect database into the server again – will it work for a detached database with multiple data files in the primary filegroup […]

Better behavior with suspect databases in SQL Server 2008

(Quickie post #1 while it’s Kimberly’s turn to lecture this morning…) I had a comment on my post dealing with suspect databases, saying that the 2008 behavior is much better. I hadn’t tried this so it was (really good) news to me. So, trying the same steps from that blog post on 2008 – everything’s […]

Creating, detaching, re-attaching, and fixing a SUSPECT database

(Check out my online training course: SQL Server: Detecting and Correcting Database Corruption.) This is a post I’ve been trying to get to since I started blogging a couple of years ago: how to re-attach a detached SUSPECT database. This is a pretty common scenario I see on the forums – a database goes SUSPECT […]

SQL Server 2008: How to rebuild the system databases?

When I started blogging, way back in 2006 :-), the third post I made on the old Storage Engine blog was about rebuilding the msdb database in 2005 (see here). This no longer works in 2008 (fellow MVP Tibor Karaszi explains why here), and in fact the information in Books Online about how to rebuild […]

Should Microsoft provide a transaction log reader tool?

Now this one’s sure to spark some controversy… I was checking my RSS feeds of the SQL blogs that I follow and noticed an interesting post on Kalen’s blog (the post is here). She’d been lambasted for suggesting in the weekly SQL Server Magazine newsletter that *Microsoft* shouldn’t provide a log reader tool and asked […]

SQL Server 2008: Does my database contain Enterprise-only features?

Moving databases around is pretty common, as is moving databases between servers running different Editions of SQL Server, especially during a disaster recovery situation. You may not know this, but in SQL Server 2005, if you had partitioning anywhere in a database, you could only attach/restore that database using an Enterprise or Developer instance. I […]

Search Engine Q&A #22: Can all page types be single-page restored?

I've had a few follow-ups on my two posts about boot page and file header page corruption – asking if its possible to do single-page restore operations for these pages. Let's try: CREATE DATABASE BootPageTest; GO – Single page restore is only possible using the FULL recovery model ALTER DATABASE BootPageTest SET RECOVERY FULL; GO […]

How to tell if you have instant initialization enabled?

As you may already know, instant initialization is a way to prevent data (not log) file create and grow operations having to zero-initialize the new space before allowing it to be used. This can vastly speed up these operations as zero-initialization can take a *long* time for large files. This is especially useful in disaster […]

Using the Dedicated Admin Connection to fix Msg 8992: corrupt system tables

Today I presented my brand new session Surviving Corruption: From Detection to Recovery at TechEd. I had a lot of fun putting together the demos, presenting the session, and talking to people afterwards. During the session, I promised to blog each of the demos so that everyone can run through them – here's the first one. […]

CHECKDB From Every Angle: Using DBCC PAGE to find what repair will delete

(I’m actually on-stage here at TechEd doing the  DAT track pre-con with Kimberly – she’s on now until lunch so I’m catching up on forum problems…) Here’s a question that came up on of the SQLServerCentral.com corruption forums I monitor that I think is worth blogging about. To paraphrase: I have a bunch of corruptions in […]

Do backups apply to just digital data? And how many backups should you have?

So Seattle weather went from 50 degrees to 85 degrees overnight Friday and we all went from shivering to sweating! It's too hot to be sitting outside so we're both sitting inside getting a little work done. Well, I should really say 'work' as neither of us are actually doing anything productive for the business. Both of […]

HA: Where do you start when choosing a high-availability solution?

It seems that today is going to be one of those days where I get lost in forums and blogging – I can live with that :-) One of the questions that came up on a forum today was about choosing an HA solution – based solely on the hardware that was running the database! […]

Search Engine Q&A #17: Split a VLDB into filegroups or smaller databases for backups?

Here’s an interesting question that came in to our questions line (questions@SQLskills.com - no guarantee of an answer – I check it every so often): I have seen demonstrations where a large database being broken down into smaller ones using synonym names. I think it was used on a data warehouse and allowed smaller database backups […]

Available for download: Our High-Availability hands-on labs and SQL Server 2008 JumpStart materials

A couple of weeks ago I blogged about the three tracks of the SQL Server 2008 JumpStart course that SQLskills.com taught internally for Microsoft and some MVPs – see here for details. Well, the content is now available to download! Note that this was based on CTP-5 (November 2007 CTP) and there have been *lots* […]

CHECKDB From Every Angle: Example DBCC CHECKDB run-times

Almost a year ago to the day I asked a question on my old blog – how long does *your* CHECKDB take to run? Well, I had a bunch of replies but I never got around to posting the results. I was reminded of this recently so threw together some graphs of the results. I've […]

Paul and Kimberly interview on RunAs Radio – What’s New in SQL Server 2008?

While we were in Barcelona we sat down with Richard Campbell and Greg Hughes from RunAs Radio to record a 1/2 hour interview on SQL Server 2008. We touch on a ton of different features (look at the number of Categories I’ve tagged this with!) and have a bunch of laughs along the way – […]

Conference Questions Pot-Pourri #6: How to cope with losing the mirror AND the witness in a mirroring session?

Here’s a question that came up yesterday in our chalk-talk on database mirroring at TechEd IT Forum that Kimberly and I talked about this morning (here in Barcelona). Q) I have a database mirroring session where the witness and mirror servers are in one physical location, and the principal server is in another. The mirroring session […]

CHECKDB From Every Angle: Can CHECKDB repair everything?

In my previous post on interpreting CHECKDB output, plus in my DBCC Internals session at TechEd IT Forum yesterday, I mentioned there are some things that CHECKDB can’t repair. In this post I want to go into a bit more detail – based on a post from my old Storage Engine blog. Before anyone takes […]

CHECKDB From Every Angle: How long will CHECKDB take to run?

This is a subject I posted about last year on my old blog but it came up at SQL Connections last week several times so I want to repost it for those who’ve just started following my blog. There’s only one time when you should be trying to work out how long a CHECKDB is […]

CHECKDB From Every Angle: Tips and tricks for interpreting CHECKDB output

Last week at SQL Connections someone said that CHECKDB’s output is ‘useless’.  Given that I wrote CHECKDB for SQL Server 2005 I was only mildly offended :-) But there’s a lot of truth in that statement – the error messages from CHECKDB do tell you exactly what’s corrupt in the database but they’re not exactly […]

BACKUP WITH COPY_ONLY – how to avoid breaking the backup chain

After posting last week about a BACKUP feature that I don’t like (WITH NO_LOG – see here), I thought I’d do a quick post this week about a new backup feature that was introduced in SS2005 that I DO like – the COPY_ONLY option to BACKUP DATABASE and BACKUP LOG. Here’s a situation I’ve seen […]

BACKUP LOG WITH NO_LOG – use, abuse, and undocumented trace flags to stop it

This has come up several times over the last few days, and is something that Kimberly and I discuss (and deplore) when we lecture – the use of BACKUP LOG WITH NO_LOG (or TRUNCATE_ONLY – they’re synonymous) to allow log truncation. How is it used? The common use is when the transaction log grows to […]

SQL Server 2008: Performance boost for Database Mirroring

As I mentioned in a previous post, one of the new features for database mirroring in SQL Server 2008 is log stream compression. There’s a good explanation of what this is (along with some example workloads and performance graphs) on the SQL Customer Advisory Team blog so I’m not going to duplicate all that here. Basically, the […]

Search Engine Q&A #7: Restoring from a backup file containing multiple database backups

This is another question that came up on the Disaster Recovery forum on MSDN. Paraphrasing – ‘ I have a backup file containing full backups for 45 databases. How can I restore them all using a script?’ The answer is pretty straightforward. Let’s create the situation described, using 3 databases for clarity rather than 45: […]

How does daylight savings time affect disaster recovery?

It’s common knowledge that SQL Server copes with daylight savings time (DST) correctly so why should you care? Well, it’s not so common knowledge that at the end of DST when the clocks go back an hour (always at 02:00 in the U.S.), SQL Agent essentially pauses for an hour (in at least SS2000 onwards). This […]

EMERGENCY-mode repair: the very, very last resort

(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.) This is a follow-on from two posts: Corruption: Last resorts that people try first… where I discussed the two worst things you can do (in my opinion) to a database – rebuilding […]

Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database

(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.) By far the most common search engine query leading to the blog is about fixing a suspect or unrecovered database. The very best way to do this is […]

Corruption: Last resorts that people try first…

(Check out my online training course: SQL Server: Detecting and Correcting Database Corruption.) In this post I want to describe the two worst things I think you can do to your database – rebuilding a transaction log and running REPAIR_ALLOW_DATA_LOSS – that people often try doing instead of restoring from their backups. Rebuilding a transaction […]

Common bad advice around disaster recovery

Now that I have a little more time on my hands I’ve been jumping back into some of the online forums. Last summer I posted on a few bits of bad advice I’ve seen in the forums but yesterday I was stunned by some of the terrible advice I saw being given out. So, I’d […]

Example 2000/2005 corrupt databases and some more info on backup, restore, page checksums and IO errors

This was originally posted as two posts on the SQL Server Storage Engine site. It was very popular so I’ve combined the two posts together and added a bunch more commentary – especially on page checksums and IO errors. You may also notice some color differences between the scripts from earlier in the year and today’s post […]