SQL Server – Handling Existing Table `dbo.cmd` in New Database

The transaction log file can't make up for a corrupted database file. Its just going to try and rollback or roll forward transactions on the database file as it is recovered.

One more possible thing to try although I doubt it will work. Even if it does, with a corrupt mdf, you're probably not going to get back everything you need.

As a preliminary step, and being sure that you have a copy of your mdf and ldf files somewhere safe, remove any entries of the original database

DROP DATABASE mydatabase

1. Create a new database

Use the the same physical file names and logical file names as was defined by the original corrupt database

--create the database
CREATE DATABASE mydatabase
  ON(NAME=’mydatabase,
        FILENAME='C:\my\path\to\file.mdf')
  LOG ON(NAME='mydatabase_Log',
        FILENAME='C:\my\path\to\file.ldf')

GO

2. Shutdown MSSQL

SHUTDOWN

Now copy in the corrupt mdf and ldf files over the newly created ones

3. Start MSSQL

You should now see the database in suspect mode

4. Put database into emergency mode SQL Server 2000

USE master
GO
sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

UPDATE sysdatabases SET status= 32768 WHERE name = 'mydatabase'

5. restart MSSQL

5a. (optional) Since you're data is now accessible via Query Analyzer, you could just export your important data into a new database.

-- create the new database
CREATE DATABASE mynewdatabase

-- import data from the old database into the new
SELECT * INTO mynedatabase..sometable FROM mydatabase..sometable.

This won't help though with your triggers and metadata, but at least you'd have your data accessible in case the repair fails

6. Repair the database

This is the method I've used for repairing the database once its in emergency mode.

USE mydatabase
GO 

-- put database into single user mode
sp_dboption 'mydatabase', 'single_user', 'true'
GO 

-- repair the database, please be sure you have an extra copy somewhere of
-- your two db files
DBCC CHECKDB ('mydatabase' repair_allow_data_loss)
GO 

-- turn off emergency mode
USE master
sp_configure 'allow updates', 1
UPDATE sysdatabases SET status= 0 WHERE name = 'mydatabase'
GO

-- check space allocation consistency
DBCC CHECKALLOC ('mydatabase')

-- verify db integrity
DBCC CHECKDB ('mydatabase')

-- take the database out of single user mode
sp_dboption 'mydatabase', 'single_user', 'false'
GO 

-- disallow updates to the system tables
USE master
GO 
sp_configure 'allow updates', 0
GO

In order to make PowerShell errors cause a SQL Agent step to fail you have to make an adjustment to the $erroractionpreference setting. By default this value is set to Continue so the steps will show successful because no error is raised up for SQL Server Agent to detect an issue in the actual PowerShell command occurred.

You can put this at the top of your script that should fix that: $erroractionpreference="stop". This will allow the error PowerShell is returning to trickle up to SQL Server Agent process.