Test case: SQL Server 2000 and up in Full recovery model. Simple recovery will not reacte this way as the log is utilized differently by SQL Server based on the recovery models.
Thanks to @DenisGobo and @bonskijr for the great conversation and test cases on this topic.
Again today I read a few threads and even saw on twitter where a person (DBA or not) was in panic mode because they had lost the ldf file for a database. One of the answers was the steps that involved a SQL Server restart. Personally that’s not an option. If your landscape is setup as most are, then simple restarts means you take down several data sources and not just the one in suspect. There really is a simple solution to the problem. Don’t delete the damn ldf file! It’s important you know. Kind of has a critical part to how the database and SQL Server functions. OK, that was my stab at, “How the hell did that happen in the first place?” Seriously though, there is a nicely wrapped system procedure for this very scenario. It’s called, “sp_attach_single_file_db”. The name says it all. Attach a database by means of the mdf file only. So let’s do it in a trial…
First run this on a local or development instance
CREATE DATABASE [LOGLOSS] ON PRIMARY ( NAME = N'LOGLOSS', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATALOGLOSS.mdf' , SIZE = 3048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'LOGLOSS_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATALOGLOSS_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [LOGLOSS] SET RECOVERY FULL GO
Now run this little gem of a statement…
ALTER DATABASE LOGLOSS SET OFFLINE
Now go to the directory you created the DB in and delete the LOGLOSS_log.ldf
First let’s see how bad that really made things for us. Run this…
ALTER DATABASE LOGLOSS SET ONLINE
You’ll soon see we’ve successfully blown up our database.
File activation failure. The physical file name "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATALOGLOSS_log.ldf" may be incorrect. Msg 945, Level 14, State 2, Line 1 Database 'LOGLOSS' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.
All is not lost. Let’s recover. One thing that is very important to note is anything that was in that log that was not committed is gone. After this recovery is completed, you’re next big task that no one here can really help with, is to validate your data and or more importantly, loss of data. Always run a DBCC CHECKDB on that recovered database to find errors and fix them as well. Update usage, rebuild indexes and on before you release the thing to production. And most important, back the mdf up before you start messing with it for recovery. If you corrupt the mdf to the point it is not recoverable in the process of trying to recover, you want to be able to start from scratch again.
So first, here is the documentation of the procedure we need.
sp_attach_single_file_db [ @dbname= ] 'dbname' , [ @physname= ] 'physical_name'
This is pretty basic at that. It comes down to the following in our test case
sp_attach_single_file_db @dbname='LOGLOSS' ,@physname=N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATALOGLOSS.mdf'
Before we can do this however, we need to rid our Meta data of the existence of the database LOGLOSS in the first place. In our test case we took the database offline. Sense we did that, the engine won’t allow an attachment as it is already there. So we need to remove that listing all together. Here is where that backup of the mdf. If you didn’t do that, you better now because we’re about to delete it.
So if you haven’t, go into the Data directory and copy the mdf to the backup directory.
Now run the DROP statement as
DROP DATABASE LOGLOSS
If the DROp has removed the data file, go ahead and copy it back. If it did not as the method of DROP while the DB is offline may not, then let all is good.
Now let’s attach the LOGLOSS database again
Running
sp_attach_single_file_db @dbname='LOGLOSS' ,@physname=N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATALOGLOSS.mdf'
Results in…
File activation failure. The physical file name "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATALOGLOSS_log.ldf" may be incorrect. New log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATALOGLOSS_log.LDF' was created.
Yay!!!
Don’t forget to checkdb the thing
DBCC CHECKDB('LOGLOSS')
In our test case you should get the happy days of
CHECKDB found 0 allocation errors and 0 consistency errors in database 'LOGLOSS'.
Congratulations, you just recovered from some fool deleting your LDF file or in the event of disk loss. Just remember, anything not committed is gone. Without the original there is no recovery from that.
25 Comments
can’t seem to replicate this, when i bring this db ONLINE after deleting the log file, it automatically creates a new one
What version are you running bonskijr?
bonskijr, what version of SQl Server are you running? I just tried on 2008 and got this message
Server: Msg 945, Level 14, State 2, Line 1
Database ‘LOGLOSS’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
File activation failure. The physical file name “C:LOGLOSS_log.ldf” may be incorrect.
im using Sql2005express
Microsoft SQL Server 2005 – 9.00.3077.00 (Intel X86) Dec 17 2008 15:19:45 Copyright (c) 1988-2005 Microsoft Corporation Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 3)
I tested on the same build as yours and SQL Express and to my surprise it does recreate teh log file when you simply issue the SET ONLINE. Checking the documentation now. Thanks for the cool catch and posting it bonskijr!!!
Aha express version, that explains it…
Express also supports different connection string, for example
Server=.SQLExpress;AttachDbFilename=c:mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;
Nice post…..
that is nice and easy but how do you recover a DB without the mdf file 🙂
Recover from deleting the mdf. hmmm…
option 1) restore from onsite backup
option 2) restore after a few days of waiting for tape
option 3) no backups…hopefully you have DR (replication, log shipping or something)
option 4) update resume and remove any reference to the job you just left because you couldn’t recover the DB without the mdf because you had no backups or DR points. 😛
hi sqldenis,
was able to replicate in fullblown sql2005 sp3, but why not in sqlexpress?
thanks
sqlexpress is a different kind of beast and for easy of use it creates the log file for you if it doesn’t exists is my hunch…don’t know for sure. sqlexpress users most likely won’t have DBAs who can support them……don’t remember how this was with msdb
I just tested this on SQL Server 2008 and SQL Server 2008 R2 if you are in simple recovery mode it will recreate the LOG file
CREATE DATABASE [LOGLOSS] ON PRIMARY
( NAME = N’LOGLOSS’, FILENAME = N’C:LOGLOSS.mdf’ , SIZE = 3048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’LOGLOSS_log’, FILENAME = N’C:LOGLOSS_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [LOGLOSS] set recovery simple
GO
ALTER DATABASE LOGLOSS SET OFFLINE
–Delete the log file
master..xp_cmdshell ‘del C:LOGLOSS_log.ldf’
ALTER DATABASE LOGLOSS SET ONLINE
Here is the message
File activation failure. The physical file name “C:LOGLOSS_log.ldf” may be incorrect.
New log file ‘C:LOGLOSS_log.LDF’ was created.
quick check
select name,filename from sysaltfiles
where name like ‘LOGLOSS%’
LOGLOSS C:LOGLOSS.mdf
LOGLOSS_log C:LOGLOSS_log.LDF
drop database [LOGLOSS]
make sure to check your recovery
select databasepropertyex(‘LOGLOSS’,’recovery’)
As Denis pointed out, make sure you are in Full recovery model. If your Model database is defaulting to simple for some reason, the statement in the blog that creates the DB will not set you to Full recovery. I’m going to edit that portion to ensure others don’t get into that problem following through.
Thanks
SP_ATTACH_SINGLE_FILE_DB (or the 2005 equivalent CREATE DATABASE … FOR ATTACH_REBUILD_LOG) only work if the database was shut down cleanly before the log was deleted. If it wasn’t (which can easily happen when the log is full) the attach will fail and you’ll need to hack the DB back into the server and do an emergency mode repair.
DBCC REBUILD_LOG does not exist in SQL Server 2005. The DB will not always come up as status SUSPECT. this is No longer Works on SQL 2005 or you can use ALTER DATABASE yourdb SET EMERGENCY. or you can do this by using SQL Server Management studio. Take a look on this blog http://www.sqlserverlogexplorer.com/how-to-attach-mdf-file-without-ldf-file/
Thanks Gail. I’m going to simulate that scenario and add to the blog. Should be a fun exercise as I’ve never tried to mimic it 🙂
This is an excellent post, thanks for the write up
I wrote a blog post a couple months back on recovering a DB without log file when the DB was not shut down cleanly. http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Is kinda a fun excercise, if the DB in question doesn’t mean anything, that is.
Thanks Gail. Very Good!
What if the db with missing ldf was tempdb?
Sense the TempDB is set in simple recovery, it will automatically create a new ldf file so there is no need for recovery.
Ada, tempdb is the only database that gets recreated every time SQL gets restarted
Traffic Mayhem Review- the articles on this article is really a single of the most effective substance that I?ve actually are available throughout. I really like your article, I will are available again to verify for new posts.
You should have read this Microsoft post for this type of solution its really helpful for you try it and go on here: http://community.office365.com/en-us/f/158/t/250677.aspx
[…] a look here: Recover a database without the ldf file to use the SP_ATTACH_SINGLE_FILE_DB […]