Occasionally an SQL database can lock-down in suspect mode. There are many causes, but the main is an expected shot-down or power loss. I had a slightly loose fitting CPU fan (and I mean ightly, one of the pins was a little less secure than the rest) in a server running SQL server 2005. It took a morning of stripping back the chassis to find it. Since re-seating the fan pins all is well.
To cut a long story short, the following script allowed me to restore the suspect database in its entirety. SQL server 2005 comes with the DB status of Emergency. This can be used to alter the status from Suspect mode (in which the database is inaccessible), to Emergency mode where you can gain access to the database and retrieve data from the tables along with those all important stored procedures and other scripts.
Once in Emergency mode you can re-run DBCC checkDB to check the integrity of all the pages and structures that make up the tables.
This script uses the REPAIR_ALLOW_DATA_LOSS argument which tries to repair all reported errors. These repairs can cause some data loss. Hopefully you have a recent back up, if not take backups from the database while you can access it in emergency mode!
REPAIR_ALLOW_DATA_LOSS is used to ensure the database is returned to a structurally and transitionally consistent state
This cannot be undone.
Here is the script:
EXEC sp_resetstatus DATABASENAME;
ALTER DATABASE DATABASENAME SET EMERGENCY
DBCC checkdb(DATABASENAME)
ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (DATABASENAME, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DATABASENAME SET MULTI_USER
If you have backed-up a database with password protection such as:
BACKUP DATABASE [DATABASE] TO DISK = N‘\\NETWORK_STARAGE_LOCATION\database.BAK’ WITH PASSWORD = ‘PASSWORD’,
NOFORMAT, NOINIT, NAME = N‘DATABASE-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS=10
GO
As outlined in a previous post http://robbamforth.wordpress.com/2008/11/07/sql-server-2005-database-backup/
Then you will be required to restore the database programmatically as both Enterprise Manger and SQL Server Management Studio do not support the password option when restoring with the GUI wizard.
To view the files available in the backup:
RESTORE FILELISTONLY FROM DISK='\\NETWORK_STARAGE_LOCATION\database.BAK'
WITH PASSWORD = 'password'
To run a full simple backup:
RESTORE DATABASE Northwind FROM DISK='\\NETWORK_STARAGE_LOCATION\database.BAK’
WITH PASSWORD = 'password'
This post shows how to restore a backup to a different location to where it was taken: http://robbamforth.wordpress.com/2009/11/18/sql-%e2%80%93-how-to-restore-a-database-backup-to-a-different-location/
You may be required to restore a database backup to a location different from the location of the original backup. If you backed up with a password then this will need to be done programmatically.
The following example shows how to use a backup taken from a database called Database and restore it to a new location on a new server with the name Database2.
RESTORE DATABASE [NEW_DATABASE]
FROM DISK = N’\\NETWORK_STARAGE_LOCATION\database.BAK’
WITH password = ‘password’,
FILE = 1,
MOVE N’database’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\database2.mdf’,
MOVE N’database_log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\database2.log,
NOUNLOAD, STATS = 10
GO
