1: Make a new database with same name, and which uses the same files as the old one on the new server.

2: Stop SQL server, and move your mdf files over the top of the new ones you just created. Delete any log files.

3: Start SQL and run this to put the DB in emergency mode.

sp_configure 'allow updates', 1
go
reconfigure with override
GO
update sysdatabases set status = 32768 where name = 'DBName'
go
sp_configure 'allow updates', 0
go
reconfigure with override
GO

4: Restart SQL server and observe that the DB is successfully in emergency mode.

5: Run this undocumented dbcc option to rebuild the log file (in the correct place)

DBCC REBUILD_LOG(DBName,'C:\SQLLog\DBTemp_Log.LDF')

6: You might need to reset the status. Even if you don’t, it won’t do any harm to do so.

exec sp_resetstatus DBTemp

7: Stop and start SQL to see your newly restored database

SHARE

LEAVE A REPLY