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 […]

Easy automation of SQL Server database maintenance

A while back I kicked off a survey asking what mechanism you use for running your regular SQL Server database maintenance. Here are the results: The “Other” responses were: 7 x “A combination of maintenance plans generated from SSMS wizard and a home-grown index maintenance script” 5 x “Combo of own and modified scripts of […]

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 […]

Survey: how often do you perform a full backup?

In this survey, I'm interested in how often you perform a full database backup of your production databases. I'll editorialize the results in a week or two. Thanks!

Survey: where do you store your database backups?

In this survey I'd like to know where you store your SQL Server backups. If a disaster occurs, where do you get them from? It doesn't matter what method you use to create the backups (native SQL Server, 3rd-party backup solution, DPM) but we're talking about database backups here, not OS-level backups. I'll report on […]

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: July 2011 SQL Q&A column

The July edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Deferred log truncation from concurrent data and log backups Database mirroring monitoring Multiple transaction log files Best use of SSDs in a SQL environment (high-level) Check it out […]

SQL Server Magazine: feature article on advanced BACKUP and RESTORE options

The June SQL Server Magazine articles are now available on the web and include my latest feature article on Advanced BACKUP and RESTORE Options. Rather than covering how BACKUP and RESTORE work or how to perform common backup/restore operations I decided to show you some of the less commonly used options that can be very […]

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 […]

Presenting for PASS DBA virtual chapter June 9th

Next Wednesday, June 9th, I'll be presenting at the monthly meeting of the PASS DBA virtual chapter. The Live Meeting starts at 11am PST and lasts for an hour (hopefully I can try to speak for that short a length of time :-) Title: Building the Right Backup Strategy Abstract: In many situations, database backups […]

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 […]

A SQL Server DBA myth a day: (27/30) use BACKUP WITH CHECKSUM to replace DBCC CHECKDB

(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.) A short one today as I'm up to my eyeballs […]

A SQL Server DBA myth a day: (24/30) twenty six restore 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.) One area I haven't touched on yet in the series […]

A SQL Server DBA myth a day: (20/30) restarting a log backup chain requires a full database backup

(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.) This myth is one of the most common and I've […]

TechNet Magazine: March 2010 SQL Q&A column

The March edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Distributed transactions and database mirroring – why they don't work together Background processes that can cause I/Os even with no connections to the server How to restore backups […]

TechNet Magazine: January 2010 SQL Q&A column

Happy New Year!  The January edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Using backups for corruption recovery Why snapshot isolation is required when using change tracking, and its performance implications Is DBCC CHECKDB a really comprehensive integrity […]

What can cause log reads and other transaction log questions

Earlier today there was a question on SQL Server Central where someone wanted to know what could be causing so many reads on their transaction log. I was asked to chime in by fellow MVP Jonathan Kehayias (who also sent me some questions that I've answered in this post – thanks Jon!), so I did, with […]

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 […]

New script: is that database REALLY in the FULL recovery mode?

One of the perennial problems facing both experienced and involuntary DBAs is how to tell whether a database is really in the Full recovery model or not? This is complicated by the fact that when you switch a database into the Full recovery mode, it actually behaves as if it’s in the Simple recovery mode […]

SQL Server 2008 High Availability whitepaper published on MSDN

The 35-page whitepaper on high availability I wrote for the SQL team over the summer has been published on MSDN. It’s a 2-300 level whitepaper that describes the various high-availability technologies in SQL Server 2008 and how they can be used to mitigate disasters. It’s chock-full of links to other whitepapers, technical articles and Books […]

TechNet Magazine: Feature article on backups and backup strategy

The July 2009 edition of TechNet Magazine is available on the web and include a feature article I wrote explaining backups. It's the first in a 3-part series, with parts 2 and 3 being on recovering from disasters using backups and recovering from disasters without backups. The backup article covers: Full backups Differential backups Log […]

Backup monitoring and reporting

Chad Miller over on SQLServerCentral has put together a really great collection of scripts that allow you to easily monitor and report on what backups are happening on your system. These should be especially useful for involuntary DBAs, rather than having to dig into the backup history tables themselves. I'll be including a link to […]

Misconceptions around the log and log backups: how to convince yourself

There’s still a widely held misconception that when properly in the FULL or BULK_LOGGED recovery models that full or differential backups can truncate the log. No. It *NEVER* happens. This is one of the reasons why I’m doing a whole spotlight session on this at PASS this year – the transaction log and its behavior […]

New script: how much data will the next log backup include?

About a year ago, I blogged a cool script that would work out how much of a database has changed since the last full backup – i.e. how big will the next differential backup be. You can find that script at New script: How much of the database has changed since the last full backup?. […]

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 […]

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 […]

Why could restoring a log-shipping log backup be slow?

A short post to start the day (in India) and then at lunchtime I'll do how it works: FILESTREAM garbage collection. This is a question that came up recently on an MVP discussion list: why does a 200MB log backup take 40 minutes to restore on my log-shipping secondary? First answer I thought of was […]

Importance of proper transaction log size management

Last week's survey was on how *you* manage the size of your transaction log (see here for the survey). Here are the results as of 4/10/2009 – the most popular survey yet: In my opinion (guarantee that I'm going to say stuff that some of you won't agree with!), there are only two valid answers in […]

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 […]

Msg 602, Level 21, State 50, Line 1

(Yes, Kimberly's lecturing again…) Here's something that I've seen crop up a lot recently on corruption forums: Server: Msg 602, Level 21, State 50, Line 1 Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. This isn't corruption - it comes from trying to […]

Measuring ‘churn’ in a SharePoint content database using SQL Server

After teaching some of the MCM-SharePoint class last week, one of the attendees pointed me at a blog post about measuring churn in SharePoint databases. The poster gave code to measure how large full backups are, which really only measures how much data there is in the database, not whether existing data has changed. If […]

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 […]

How can a log backup fail but a full backup succeed?

This is an interesting case that cropped up yesterday – the transaction log is damaged so a log backup doesn't work (with the error below): Backup detected log corruption in database FakeDBName. Context is FirstSector. LogFile: 2 'F:\SQLLOGS\XYZ\FakeDBName_Log.ldf' VLF SeqNo: x502e VLFBase: x2ce40000 LogBlockOffset: x2d0a9000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x4ee3 LogBlock.StartLsn.Blk: x1348 Size: x200 PrevSize: x400 […]

Testing a new survey method: backup validation

I'm toying with the idea of having a weekly survey that'll highlight an interesting facet of database management. I've signed up with SurveyPopups.com, which is free and allows you to see the results as you vote on them. If you think this is a cool idea, vote in the survey using the options below. If […]

TechNet Magazine: February 2009 SQL Q&A column

OK – last content post today. I forgot that the February TechNet Magazine also has the latest edition of my regular SQL Q&A column. This month's column covers: Should backup compression be enabled at the instance level? Client redirection during database mirroring failovers Partition-level lock escalation in SQL Server 2008 Is it ever safe to […]

Conference Questions Pot-Pourri #10: Shrinking the database before taking a backup

Here's a question I got from someone who attended our database maintenance workshop at PASS last week (paraphrased): I attended your pre-conference session on database maintenance and found it to be very informative.  From what you told use though, I think I need to change my nightly backup procedure.  I like to get my databases […]

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 […]

TechNet Magazine: December 2008 SQL Q&A column

It's really scary how quickly time flies – seems like it was just last week when I last blogged about TechNet Magazine (actually it was a month ago when I blogged about my 2008 Change Tracking article - see here). Anyway, a new issue of TechNet Magazine has just come out and this one has the […]

Conference Questions Pot-Pourri #9: Q&A around compression features

Today's post is based on a bunch of questions I've had around the various compression features in SQL Server 2008. Does turning on data compression or backup compression compress the transaction log files on disk? No. The transaction log is not compressed in any way under any circumstances. Rows from tables and indexes that have […]

High-end backup compression numbers

On Tuesday we had a look around our customer's data centers here in Austria – hidden away in the labryinthine bowels of a very large building in Vienna. Typical data centre with a halon fire extinguishing system but exceptional in its neatness and organization. The star of the show was their new HP Superdome – […]

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). […]

TechNet Magazine: October SQL Q&A column

It’s been almost two weeks since my last post as we’ve been offline in Scotland (another photo post to follow – I owe you two now…) but now we’re back for the crazy Fall conference and teaching season. The latest installment of my regular Q&A column in TechNet Magazine is available at http://technet.microsoft.com/en-us/magazine/cc895648.aspx. This month […]

Search Engine Q&A #25: Why isn’t my log backup the same size as my log?

I woke up this morning and someone had replaced my wife with someone who likes to blog :-). Kimberly’s turned over a new leaf and is going to blog much more often – in fact she’s blogged 4 times today already. Check out her blog here. Continuing on the transaction log theme of the last few […]

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 #23: My transaction log is full – now what?

There's been a spate of problems on the forums and in my inbox with people's transaction logs filling up. With the increase in "involuntary DBAs", there are more people being thrust into the role of database administration without being given the chance to learn the skills, tips, and tricks necessary. One of the most common […]

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 […]

TechNet Magazine: Effective Database Maintenance article and August SQL Q&A column

Well, we’re just back from vacation (photo blog post to follow) and I’ve heard that the feature article on Effective Database Maintenance I wrote for the August issue of TechNet Magazine is live on the web. It also includes a 5 minute long screencast I recorded where I demo the effect of database shrink on index […]

Search Engine Q&A #19: Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking)

Over the last few weeks I've seen (and helped correct) quite a few myths and misconceptions about index rebuild operations. There's enough now to make it worthwhile doing a blog post (and it's too hot here in Orlando for us to go sit by the pool so we're both sitting here blogging)… Myth 1:  index […]

New script: How much of the database has changed since the last full backup?

Over the weekend there was a question on one of the internal aliases at MS: how can I tell what percentage of a database has changed since the last full backup, so I can choose between a differential or full backup? No such code exists as far as I know – until now! I happened […]

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 […]

Search Engine Q&A #16: Concurrent log and full backups

Here’s a question that came in – what changed in SQL Server 2005 that allows concurrent log and full backups? First a little background, in case you didn’t know about the change in behavior. In SQL Server 2000, a concurrent log backup with either a full or diff backup (I’ll just say ‘full’ from now […]

SQL Server 2008 JumpStart

Phew – last week Kimberly and I spent 3 days teaching the ins-and-outs of SQL Server 2008 for DBAs/IT-Pros to about 130 Microsoft SQL Server experts and MVPs (like Kalen Delaney, Adam Machanic and Ron Talmage). This was the (95% complete) Beta delivery of a course we’ve been developing for the last six months for Microsoft […]

Search Engine Q&A #15: Mirrored backups

This post is based on one from my old MSDN blog but the topic has come up a few times in recent days so I want to revamp it and re-post. There are two things that confuse people about mirrored backups – can you mix-n-match backup devices from the mirrors, and what exactly do the […]

More on how much transaction log a full backup includes

In a previous post (see here) I debunked a myth about how much transaction log a full backup would include. I had a question in the blog post comments that asked (paraphrasing): The full backup has to include all the transaction log from the begin LSN of the oldest active transaction at the time the […]

SQL Server 2008: Backup Compression CPU Cost

Back in September last year I blogged about the native Backup Compression that is in SQL Server 2008 (see here) and promised to blog more when CTP-5 came out and I ran some tests. Well, it’s here and I have so here’s some data for you. I expanded the AdventureWorks database to be 322Mb (random […]

Patents and parties

In the mail today I received notice that my first software patent has been granted by the US Patent Office :-) (after being filed 3.5 years ago while I was still writing DBCC CHECKDB code!) It’s basically a way to run DBCC CHECKDB on a database stored in a backup without actually having to restore the […]

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 – […]

Fed up with BACKUP success messages bloating your error logs?

There’s a well known problem that every time a backup operation succeeds a message is written into the error log and Windows event log. If you’re taking very frequent log backups (say every 5 minutes) of multiple databases, that’s a significant amount of clutter in the logs. Well – now there’s a fix! Kevin Farlee, […]

Debunking a couple of myths around full database backups

I’ve been involved in a few conversations today that have highlighted some big misconceptions about how backups work. I’d like to use this blog post to debunk them. I checked everything I say here with my friend Steve Schmidt, the developer on the Storage Engine team responsible for BACKUP/RESTORE for the last ten years. Myth 1: […]

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 […]

SQL Server 2008: What’s been removed in this version? BACKUP LOG WITH NO_LOG for one!

All the razzamatazz about new releases go on about what's new but hardly ever is there discussion of what's been removed. So that's the topic of this short post. In the Books Online that comes with the July CTP of SS2008 (here's a link to the download page), the topic Discontinued Database Engine Functionality in […]

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 […]

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 […]

Search Engine Q&A #6: Using fn_dblog to tell if a transaction is contained in a backup

Here’s a really interesting question that was in my search engine logs yesterday - if I have a transaction that runs and completes while a backup is running, will the complete transaction be in the backup? The answer is…. it depends! In terms of what gets backed up, the way a full backup works is: Note […]

Inside the Storage Engine: Anatomy of an extent

This one's a quickie. In the previous post I explained about database pages – their structure and some page types. Now I'd like to explain how pages are grouped into units called extents. An extent is a group of eight physically consecutive pages in a data file. Extents are always aligned on 64KB boundaries (i.e. […]

SQL Server 2008: Backup Compression

Over the next few months I’ll be blogging a lot about new features that are coming in SQL Server 2008 for DBAs and ITPros. First up is Backup Compression. This has been one of most heavily requested features since before I joined the SQL team back in 1999 – for me it’s really cool that […]

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 […]