Recovering a SQL Database with only datafile available (no log or backup)
Assuming no database set up in SQL Server this should attach the
database and rebuild the log providing the MDF datafile was in a
consistent state when it was copied.
CREATE DATABASE <database>
ON (FILENAME = ‘D:\<fullpath>\<database>.mdf’)
FOR ATTACH_REBUILD_LOG
If the below message or similar occurs:
File activation failure. The physical file name “<Datafile>” may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Then method 2 will trick the database into allowing a database to attach in recovery pending mode. However this could mean some datal oss occurring as the database has to be repaired, and this is not guaranteed to work.
External Reference here: http://www.sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
CREATE DATABASE <database>
ON (FILENAME = ‘D:\<fullpath>\<database>.mdf’)
FOR ATTACH_REBUILD_LOG
If the below message or similar occurs:
File activation failure. The physical file name “<Datafile>” may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Then method 2 will trick the database into allowing a database to attach in recovery pending mode. However this could mean some datal oss occurring as the database has to be repaired, and this is not guaranteed to work.
- Create a new database (empty)
- Switch off the instance
- Rename the original MDF file to the new database new file.mdf overwriting the existing file.
- Delete the .ldf file for the new database
- Start the Instance (db will start in recovery pending mode)
- ALTER DATABASE <database> SET EMERGENCY
- ALTER DATABASE <database> SET SINGLE_USER
- DBCC CHECKDB(<database>,REPAIR_ALLOW_DATA_LOSS)
- ALTER DATABASE <database> SET MULTI_USER
External Reference here: http://www.sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Comments