Day 31 of 31 Days of Disaster Recovery: Backup and Restore of the Resource Database

It has been a long journey to the final day my 31 Days of Disaster Recovery series, but we have finally reached the final post, day 31. My final topic for the series is born out of a conversation I had today with my good friend and fellow DBA, Ed Watson (blog|@SQLGator). today, I want to talk about backing up and restoring the resource database. The resource database stores critical system objects safely separated away from the master database. It
read more

Day 30 of 31 Days of Disaster Recovery (T-SQL Tuesday #40): Using Partial Availability and Initialize from Backup to Replicate a Partial Database

It’s been a tough and long road to 31 Days of Disaster Recovery. It’s been very difficult coming up with quality topic ideas for the series as we near the end. For day 30 of the series, I am combining a post on performing piecemeal restores with a post on filegroups for T-SQL Tuesday #40 and a post on replication. In case you’re not familiar with T-SQL Tuesday, let me enlighten you. This blog entry is participating in T-SQL Tuesday
read more

Day 29 of 31 Days of Disaster Recovery: Using Database Snapshots to Restore Replicated Databases in Test

For day 29 of my 31 Days of Disaster Recover series, I want to talk about restoring replicated databases from database snapshots. Someone asked me recently if I had any recommendations for speeding up their unit testing on replicated databases. He has to run unit tests over and over on replicated databases to ensure that they deploy successfully in a replicated environment. His current process was adding too much time to the process and automating it was difficult for him
read more

Day 28 of 31 Days of Disaster Recovery: Recovering SQL if the Tempdb Drive Dies

Welcome to day 28 of my series 31 Days of Disaster Recovery. Today I want to talk about recovering SQL Server if your tempdb drive suddenly dies. SQL Server won’t start if it can’t start up tempdb, so it poses a challenge if you can’t get the drive up and running again. If you missed any of the earlier posts in my DR series, you can check them out here: 31 Days of disaster Recovery Does DBCC Automatically Use Existing
read more

Day 27 of 31 Days of Disaster Recovery: Restoring Part of a Database

Today is day 27 of my series 31 Days of Disaster Recovery, and I want to talk about restoring a partial database to a server. If you have a very large database with many filegroups, and you need to restore just part of the database, then you can perform a partial restore (Enterprise Edition required) to only restore minimum amount of filegroups online to get access to the part you need. A good use case for this would be if
read more

Day 26 of 31 Days of Disaster Recovery: The Mysterious Case of the Long Backup

Welcome back for day 26 of my series 31 Days of Disaster Recovery. Today I want to share a tale of a mysterious backup that was running too long, and as the SAN admin reported, nothing had changed in terms of configuration of the SAN or our LUNs. We eventually tracked down the issue, and it was something none of of us had even considered. Likewise, it was something we never even thought to look for at the time we
read more

Day 25 of 31 Days of Disaster Recovery: Improving Performance of Backups and Restores

My series 31 Days of Disaster recovery has been on hiatus due mostly to illness. I’ve been battling a chest cold that became bronchitis. I’m still fighting cough, but even that has improved to the point that I’m now sleeping longer at night than night. It wasn’t so much that I was too sick to write a blog post as it was that I was too sick to think up good ideas and put them into words. Rather than deliver
read more

Day 24 of 31 Days of Disaster Recovery: Handling Corruption in a Clustered Index

Welcome to day 24 of my 31 Days of Disaster Recovery series. Previously, I’ve talked about several different forms of corruption: Nonclustered Index, Allocation Pages, and Tempdb. these were all fairly simple to fix. Today I’m going to dive into a scenario that is a little more complex, clustered indexes. Clustered indexes are the base data. This means we have to go to a restore scenario. Hopefully, the corruption isn’t wide spread. if it’s just 1 or a few pages,
read more

Day 23 of 31 Days of Disaster Recovery: Restoring Differential Backups With New Files

It’s day 23 of my 31 Days of Disaster Recovery series, and today’s blog post is inspired from an email i received in response to day 20’s blog post The Case of the Backups That Wouldn’t Restore. A friend shared a story with me via email about a partial restore scenario that was very complex. These are the complexities he described: Weekly full backups and daily differential backups New data files are added daily for their partitioned tables 1508 total
read more

Day 22 of 31 Days of Disaster Recovery: Which DBCC CHECK Commands Update Last Known Good DBCC

The end of the day is quickly approaching as I finish this blog post. This is day 22 in my series 31 Days of Disaster Recovery, and I want to examine which DBCC CHECK commands update the last known good DBCC check that is tracked in the header of the database. To check this value, I could either dump the header page using DBCC PAGE() or I could just output he header info using DBCC DBINFO(). Both of these functions
read more