Recover from a Suspect database

--Recover from a Suspect db on SQL Server 2005 Standard Service Pack2
---Error: 3414, Severity: 21, State: 1.
--During redoing of a logged operation in database 'MY_DB', an error occurred at log record ID (5006:4238:75).
--Typically, the specific failure is previously logged as an error in the Windows Event Log service.
--Restore the database from a full backup, or repair the database.
--I progressed with these steps , to get the db back to an ONLINE state
--NOTE : This is a last resort , and I only tried , once I'd exhausted recovery from backup etc

ALTER DATABASE MY_DB SET ONLINE;
GO
DBCC CHECKDB (MY_DB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE MY_DB SET EMERGENCY;

GO
DBCC CHECKDB (MY_DB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE MY_DB SET SINGLE_USER;
GO
DBCC CHECKDB (MY_DB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE MY_DB SET MULTI_USER;
GO 
SELECT state_desc FROM sys.databases WHERE name='MY_DB';

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server backup review with Powershell

The BACKUP team requested a report outlining the last backup of every database on every SQL Server instance, covering : full , incremental and log type backups. The idea was to compare this against their schedule , checking for differences .

The SQL Server inventory in the organisation is 750 SQL Server instances , using SQL server 2000 , 2005 and SQL Server 2008. The solution had to involve iterating through a list of sql server instances ,  executing some t-sql  code, adding the results to an output file. Following is the steps , with relevant details

STEP 1

Create a .txt file with a list of all relevant instances , such as

MYSERVER1\INSTANCE1
MYSERVER2\INSTANCE2
MYSERVER3\INSTANC3

STEP 2

Create a .sql file with the following code , and save as backups_last_good_backups.sql. In this case I wanted to raise an ALERT , if a backup hadn't occured in 7 days , or if no backup existed at all.:

/******************************************************************
*
* SQL Server - Last known good backup 
* 
* Run this script against a sql server instance to check when last known
* good backups were taken
* The script displays : name of db , backup type , backup set db name , last backup
*A
******************************************************************/
SELECT  (SELECT   RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as instance,
        sd.name as name,
        bs.type,
        bs.database_name,
        max(bs.backup_start_date) as last_backup,
        note = CASE 
   WHEN max(bs.backup_start_date) < GETDATE() - 7 THEN 'ALERT'
   WHEN ISNULL(max(bs.backup_start_date),0) = 0 THEN 'ALERT'
   ELSE '---'
   END
FROM    master..sysdatabases sd
        Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
        left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
        WHERE sd.name NOT LIKE '%tempdb%'
Group by sd.name,
        bs.type,
        bs.database_name
Order by sd.name,last_backup

STEP 3

Create a powershell file , and place in the following code . Note : on line 1 is the location of the file you created in Step 1 Line 2 , invokes the SQLCMD (which needs to be installed on whichever computer you are execuring this powershell code.) It accepts the the $svr variable , and runs the .sql code , saved in step 2. This outputs the results into "backupAudit_SQL_Server_output.txt" in an iterative mode.

  

 foreach ($svr in get-content "C:\instances_backup.txt"){
        invoke-expression "SQLCMD -E -S $svr -i backups_last_good_backups.sql -v inst_name=$svr -Y30 >> backupAudit_SQL_Server_output.txt"
 }  

STEP 4

Ensuring your powershell code and SQL code are in the same directory ,Run the powershell code in your Powershell command line - and then check the output , it should be something like :

instance                       name                           type database_name                  last_backup             note
------------------------------ ------------------------------ ---- ------------------------------ ----------------------- -----
MYSERVER1_INST1               master                         D    master                         2009-06-27 23:59:53.000 --- 
MYSERVER1_INST1               model                          D    model                          2009-06-28 00:03:01.000 --- 
MYSERVER1_INST1               msdb                           D    msdb                           2009-06-28 00:03:07.000 --- 
MYSERVER1_INST1               Rightdbs                       I    Rightdbs                       2009-06-26 20:30:26.000 --- 
MYSERVER1_INST1               Rightdbs                       D    Rightdbs                       2009-06-28 00:03:18.000 --- 

(5 rows affected)
instance                       name                           type database_name                  last_backup             note
------------------------------ ------------------------------ ---- ------------------------------ ----------------------- -----
MYSERVER2_INST2             master                         NULL NULL                                              NULL ALERT
MYSERVER2_INST2             model                          NULL NULL                                              NULL ALERT
MYSERVER2_INST2             msdb                           NULL NULL                                              NULL ALERT
MYSERVER2_INST2             db3nn                          D    db3nn                          2009-06-18 19:56:10.000 ALERT