The joys of DPM and the pains of SQL

Posted: March 14, 2011 in Uncategorized

Just recently, as in the weekend just gone, I had a change for an upgrade of SQL 2008 to SQL 2008 R2. The one half went like a dream.

However the other half, revoking names on purpose, was not so easy. Let’s just say  that Reporting Services is not always the easiest thing to upgrade. My upgrade went TERRIBLY wrong. It was however a great learning exercise and at the same time reminds we why i LOVE DPM so much, SQL backups through DPM are the king. Again, i will scream this “ I LOVE DPM!!!!”

In this process of the upgrade I ended up with some “Suspect” databases. I found some scripts that MIGHT allow the recovery of the data. PLEASE NOTE THAT IS A LAST DITCH EFFORT. The scripts below should only be used as a final resort as there MIGHT be DATA LOSS.

 

They should be used in the following order

1. SETEmeregency.sql (put database in emergency mode and attempt repair, PLEASE NOTE AGAIN THAT THERE MIGHT BE DATA LOSS

2. FindSPID.sql (find which SPID is locking the database)

3. KillSPID (kill the connection that is locking the database.

4. SQL SetMultiUser (put database in multi-user mode and check, and hope that there is no data loss.

In case the files do not upload correctly.

SETEmergency.sql

   1: USE master;

   2:  

   3: GO

   4:  

   5: ALTER DATABASE ReportServer SET EMERGENCY 

   6:  

   7: GO

   8:  

   9: ALTER DATABASE ReportServer SET SINGLE_USER 

  10:  

  11: GO

  12:  

  13: DBCC CHECKDB (ReportServer, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

  14:  

  15: GO 

  16:  

  17:  

  18:  

  19: USE ReportServer;

  20:  

  21:  

  22:  

  23: DBCC CHECKDB WITH NO_INFOMSGS;

  24:  

  25: GO

FindSPID.sql

   1: use master

   2: go

   3: sp_who

KillSPID.sql

   1: kill <SPID>

SQLSetMultiUser.sql

   1: ALTER DATABASE ReportServer SET MULTI_USER

   2:  

   3: GO

 

I can stress enough that is not a replacement in any way for a proper backup plan.

 

“EMBRACE YOUR INNER GEEK AND

ROCK ON”

Advertisements
Comments
  1. […] (Execs and other users crying about loosing items), recovering of databases and VHD (see some of my previous posts). So back to my point, the Protection groups were created without any hassle and now I […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s