Trying to Recover Without an .ldf
I'm trying to recover a database that has one master data file (.mdf) and one log data file (.ldf). I got the .mdf file from a standard OS backup tape. But sp_detach_db wasn't run on the database before the .mdf backup, so I don't have the .ldf file. I know that the stored procedure sp_attach_single_file_db can recreate the log file in some cases, and I've tried to use it to simply reattach the database, but I get the following error:
Server: Msg 1813,
Level 16, State 2, Line 1
Could not open new database 'db'.
CREATE DATABASE is aborted.
Device activation error.
The physical file name
'C:Program FilesMicrosoft SQL ServerMSSQLdatadb_log.LDF' may be incorrect.
Can I recover my database?
SQL Server Books Online (BOL) clearly documents that you must run sp_detach_db on a database to let the database reattach with sp_attach_db or sp_attach_single_file_db. Using sp_detach_db ensures transactional consistency within the database and ensures data integrity. However, if complete data integrity isn't important or you know that no data has changed recently, you might be able to use the undocumented Database Consistency Checker (DBCC) REBUILD_LOG command that Listing 3 shows to attach the database. REBUILD_LOG will recreate a new log file and let you reattach a database even if a good log file doesn't exist. However, the data might not be transactionally consistent because you might have thrown away active and uncommitted transactions. Use this command only for emergency recovery when you move data to a new database.
Use caution when you apply any undocumented technique in a production environment. I strongly encourage you to contact Microsoft Product Support Services (PSS) for recovery of production data rather than use undocumented recovery techniques. But sometimes, tips such as this one are good to have in your bag of tricks.
代碼: 選擇全部
LISTING 1: Undocumented DBCC Command REBUILD_LOG
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
BEGIN TRAN
UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'MyDatabase'
IF @@ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unable to set emergency mode', 16, 1)
END
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
-- Restart SQL Server at this point.
DBCC REBUILD_LOG('MyDatabase','C:MyDatabase.ldf')
/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/
ALTER DATABASE MyDatabase SET MULTI_USER
GO
-- Set database options and recovery model as desired.
GO