Deleted Transaction Log
-
Hello!
I accidently deleted the transaction log to one of my SQL 2000 databases. I do not have a backup of the transaction log and the database backup is over a week old. The .MDF file is still available. Is there a way for me to get this database up and running?
--David
www.KeychainDefense.com -- Self-Protection on your keychain
www.Keychain-Tools.com -- Tool kits that fit on your keychain or in your wallet -
Use the single file attach stored proc and you should be good to go!
EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
DavidB,
I executed the following command as you suggested:
EXEC sp_attach_single_file_db @dbname = 'Billing',
@physname = 'F:\SQL Server Data\Billing_Data.MDF'
AND received the following error message:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Billing'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'F:\SQL Server Data\Billing_Log.LDF' may be incorrect.
www.KeychainDefense.com -- Self-Protection on your keychain
www.Keychain-Tools.com -- Tool kits that fit on your keychain or in your wallet -
My bad, I assumed that the database already existed and that the files were detached. So, being that you are going to create a "new" database with just an .mdf file you would want this example;
CREATE DATABASE Billing
ON PRIMARY (FILENAME = 'F:\SQL Server Data\Billing_Data.MDF')
FOR ATTACH
GO
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
DavidB,
I tried:
CREATE DATABASE Billing
ON PRIMARY (FILENAME = 'F:\SQL Server Data\Billing_Data.MDF')
FOR ATTACH
GO
AND received the following error message:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Billing'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'F:\SQL Server Data\Billing_Log.LDF' may be incorrect.
Do you have any other ideas?
www.KeychainDefense.com -- Self-Protection on your keychain
www.Keychain-Tools.com -- Tool kits that fit on your keychain or in your wallet -
Did you have multiple log files?
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
I copied the MDF file to my notebook and tried to attach the database to local SQL Server and received the following error:
Error 823: I/O error (torn page) detected during read at offset 000000000000000000 in file 'C:\temp\CFC\Billing_Data.MDF'.
Any ideas?
It looks like I am having to restore from a week-old back-up. --David
www.KeychainDefense.com -- Self-Protection on your keychain
www.Keychain-Tools.com -- Tool kits that fit on your keychain or in your wallet -
DavidB,
I only have one log file -- the one that I deleted.
--David
www.KeychainDefense.com -- Self-Protection on your keychain
www.Keychain-Tools.com -- Tool kits that fit on your keychain or in your wallet -
Well it definitely sounds as if the .mdf has problems but it is certainly odd that it did not report similarly when attaching to the server. Hmmm. Anyone else?
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
It should have worked with the single file attach - I've done this many times to quickly get rid of a huge log file (detach, delete log file, reattach). Torn page is bad but not fatal, run dbcc Checkdb with the repair option.
I'm not clear on how you deleted the log file or why it matters. You should NOT be able to delete if SQL is running, if SQL is stopped cleanly you shouldn't be missing many if any transactions, if you detached first then all open transactions would have been applied. Just curious!
Andy
-
Now, what you can do it is
1. Detach the database and get the mdf file
2. move the mdf file to a different location
3. Attach the file, now the sql server will create a log file
-
Hi,
Picking up on the previous conversation, I too am having the same problem. Unfortunately it's with a client database for which we have been sent the MDF, which we assume was detached from the database. The only confirmation for that is that the database is no longer visible in Enterprise Manager.
I have tried all the suggested approaches, both on our server and over the phone on the client's SQL Server
- CREATE DATABASE Billing ON PRIMARY (FILENAME = 'F:\SQL Server Data\Billing_Data.MDF') FOR ATTACH
- Attach DB using sp_attach_single_file_db
Both of them give the same error "Device Activation Error.The physical file name '......' may be incorrect
So why is it not working? Any suggestions greatly appreciated.
Mauro
-
hmm...I got the same, but there was something else :
New log file 'd:\MSSQL\Data\TEST_log.LDF' was created and the database was attached...
I am thinking - I got once the database was detached in the middle of log backup or some other process, and the LOG file was not good + Data file did not work too... So, the way the DB was detached I think the key for this problem...
I used then the backup to restore DB.
-
Yes I agree, I suspect that the detach process might have actually caused the problem. Unfortunately the customer did not have a regular backup job running...and so I would suspect there is no solution to be found.
Any other suggestions?
-
I hit a similar situation once. SQL Server was stopped, I manually deleted the (single) .ldf file, and when I started up SQL Server again the file was marked "suspect" and could not be recovered. (I ended up having to restore a backup--which, fortunately, had been made 5 minutes ago since we were doing a Production deployment.) This may have been in SQL Server 7.0.
(Yes, detaching, deleting the log file, and reattaching is no problem as a new log file will be generated.)
I can recreate this now with 2000 -- stop SQL Server, delete a log file (make a backup copy first!), restart, and the database is marked suspect. However, if I copy that .mdf file to another server and sp_attach_db, it generates a new log file.
Some guesses:
- Maybe it's a SQL 7.0 issue?
- Maybe the file is corrupt?
- Maybe they somehow made a "dirty copy" of the file while it was open?
Philip
It looks like I am having to restore from a week-old back-up. Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply