SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Log File Deleted!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mtal18
Starting Member

4 Posts

Posted - 12/22/2006 :  18:37:41  Show Profile  Reply with Quote
I'm a network Admin so I'm new to DBA and I made a mistake. I deleted the log file (ldf) from the SQL data folder. Then while trying restore anyway I could I took the DB offline and detached hoping that would let me reattach the DB. I still have the original MDF data file but I cant find a way to restore the DB. Does anyone have any ideas?

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 12/22/2006 :  18:45:31  Show Profile  Reply with Quote
Have you tried with sp_attach_single_file_db and sp_attach_db?
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 12/22/2006 :  18:48:35  Show Profile  Reply with Quote
quote:
Originally posted by mtal18

I'm a network Admin so I'm new to DBA and I made a mistake. I deleted the log file (ldf) from the SQL data folder. Then while trying restore anyway I could I took the DB offline and detached hoping that would let me reattach the DB. I still have the original MDF data file but I cant find a way to restore the DB. Does anyone have any ideas?



we have had about 5 questions like this in the last week. what the hell is going on? How did you delete a file that was open in the first place? I am assuming you have no actual backups (using the TSQL backup command) Anyway, you are going to want to use a procedure called sp_attach_single_file_db to fix your problem.

Here is the documentation on the procedure. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_4wrm.asp
Make sure you make a copy of your MDF before you do anything.



-ec


EDIT:
fixed link

Edited by - eyechart on 12/22/2006 18:49:42
Go to Top of Page

MohammedU
Posting Yak Master

USA
145 Posts

Posted - 12/22/2006 :  18:57:13  Show Profile  Reply with Quote
You can use EM also to attach the db using single file, sql automatically creates ldf file if the db is closed properly otherwise it will not create the file and you get the error...Same thing happens with sp_attach_single_file_db procedure also...

In worst case scenario you can rebuild the log using DBCC REBUILD_LOG...


MohammedU
Go to Top of Page

mtal18
Starting Member

4 Posts

Posted - 12/22/2006 :  23:46:19  Show Profile  Reply with Quote
Ok I think the DB did not close properly. Can you explain the DBCC REBUILD_LOG procedure a little more?

Thx for the help
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 12/22/2006 :  23:57:38  Show Profile  Reply with Quote
quote:
Originally posted by mtal18

Ok I think the DB did not close properly. Can you explain the DBCC REBUILD_LOG procedure a little more?



what error do you get when you try sp_attach_single_file_db?



-ec
Go to Top of Page

MohammedU
Posting Yak Master

USA
145 Posts

Posted - 12/23/2006 :  00:10:53  Show Profile  Reply with Quote
Backup .mdf file...
1. Rename database .mdf file .mdf_old
2. Create new database with same name and same mdf and ldf files
3. Stop sql services
4. Rename new db .mdf file to .mdf_new and .ldf file to .ldf_new
5. Rename .mdf_old file to .mdf...
6. Restart sql and you db will show up as suspect...
7. Change the database context to Master and allow updates to system
tables:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

8. Set the database in Emergency (bypass recovery) mode:

-- note the value of the status column for later use
select * from sysdatabases where name = '<db_name>'
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran

If you run DBCC REBUILD_LOG without setting the database in Emergency
mode, the command does not work. You do not receive an error, but the
log is not rebuilt either.

9. Stop and restart SQL server.

If you run DBCC REBUILD_LOG without recycling the server, the following
message displays:

Server: Msg 5023, Level 16, State 2, Line 1 Database must be put in
bypass recovery mode to rebuild the log. DBCC execution completed. If
DBCC printed error messages, contact your system administrator.

10. The syntax for DBCC REBUILD_LOG is as follows:

DBCC REBUILD_LOG('<db_name>','<log_filename>')

where <db_name> is the name of the database and <log_filename> is the
physical path to the new log file, not a logical file name. If you do
not specify the full path, the new log is created in the Windows NT
system root directory (by default, this is the Winnt\System32
directory).

11. Rebuild the log with this code:

DBCC TRACEON (3604)
DBCC REBUILD_LOG('<db_name>','<log_filename>')
Go

If the command is successful, the following message appears:

Warning: The log for database '<db_name>' has been rebuilt.
Transactional consistency has been lost. DBCC CHECKDB should be run to
validate physical consistency. Database options will have to be reset,
and extra log files may need to be deleted.

After the log is successfully rebuilt, the database is placed in DBO Use
Only mode. That is, the status of the database is 2048 irrespective of
what the status was previously. You must reset the status using
sp_dboption or through the SEM.

12. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:

sp_dboption '<db_name>', 'single user', 'true'
DBCC CHECKDB('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name =
'<db_name>'
-- verify one row is updated before committing
commit tran
Go

13. Turn off the updates to system tables by using:

sp_configure 'allow updates', 0
reconfigure with override
Go

WARNING: After verifying the consistency of the database by running DBCC
CHECKDB, and fixing any errors, please make sure to check the database
for logical consistency as well. Because a new log has been built, the
transactions in the
old log are lost, hence you must also verify the logical consistency of
the data as well.

After you successfully complete the preceding steps, you may use the
database as normal.


MohammedU
Go to Top of Page

mtal18
Starting Member

4 Posts

Posted - 12/23/2006 :  02:04:39  Show Profile  Reply with Quote
quote:
Originally posted by MohammedU

Backup .mdf file...
1. Rename database .mdf file .mdf_old
2. Create new database with same name and same mdf and ldf files
3. Stop sql services
4. Rename new db .mdf file to .mdf_new and .ldf file to .ldf_new
5. Rename .mdf_old file to .mdf...
6. Restart sql and you db will show up as suspect...
7. Change the database context to Master and allow updates to system
tables:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

8. Set the database in Emergency (bypass recovery) mode:

-- note the value of the status column for later use
select * from sysdatabases where name = '<db_name>'
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran

If you run DBCC REBUILD_LOG without setting the database in Emergency
mode, the command does not work. You do not receive an error, but the
log is not rebuilt either.

9. Stop and restart SQL server.

If you run DBCC REBUILD_LOG without recycling the server, the following
message displays:

Server: Msg 5023, Level 16, State 2, Line 1 Database must be put in
bypass recovery mode to rebuild the log. DBCC execution completed. If
DBCC printed error messages, contact your system administrator.

10. The syntax for DBCC REBUILD_LOG is as follows:

DBCC REBUILD_LOG('<db_name>','<log_filename>')

where <db_name> is the name of the database and <log_filename> is the
physical path to the new log file, not a logical file name. If you do
not specify the full path, the new log is created in the Windows NT
system root directory (by default, this is the Winnt\System32
directory).

11. Rebuild the log with this code:

DBCC TRACEON (3604)
DBCC REBUILD_LOG('<db_name>','<log_filename>')
Go

If the command is successful, the following message appears:

Warning: The log for database '<db_name>' has been rebuilt.
Transactional consistency has been lost. DBCC CHECKDB should be run to
validate physical consistency. Database options will have to be reset,
and extra log files may need to be deleted.

After the log is successfully rebuilt, the database is placed in DBO Use
Only mode. That is, the status of the database is 2048 irrespective of
what the status was previously. You must reset the status using
sp_dboption or through the SEM.

12. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:

sp_dboption '<db_name>', 'single user', 'true'
DBCC CHECKDB('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name =
'<db_name>'
-- verify one row is updated before committing
commit tran
Go

13. Turn off the updates to system tables by using:

sp_configure 'allow updates', 0
reconfigure with override
Go

WARNING: After verifying the consistency of the database by running DBCC
CHECKDB, and fixing any errors, please make sure to check the database
for logical consistency as well. Because a new log has been built, the
transactions in the
old log are lost, hence you must also verify the logical consistency of
the data as well.

After you successfully complete the preceding steps, you may use the
database as normal.


MohammedU




I made it to getting the DB to "DBO use only mode"

When I get to this code...

sp_dboption '<db_name>', 'single user', 'true'
DBCC CHECKDB('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name =
'<db_name>'
-- verify one row is updated before committing
commit tran
Go

I'm not sure what to place in the <prior value> tag and if I leave it as <prior value> it tells me this when I run the code... "Database options single user and dbo use only cannot be set at the same time."

Also how do I check the database
for logical consistency once completed?


Edited by - mtal18 on 12/23/2006 02:07:41
Go to Top of Page

sleaklight
Starting Member

11 Posts

Posted - 12/23/2006 :  04:45:17  Show Profile  Reply with Quote
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76613

try the method I got mine up and running again after only downloading the mdf and not the ldf, you're basically in the same situation as I was and got it working with the code I posted in one of my last replies
Go to Top of Page

mtal18
Starting Member

4 Posts

Posted - 12/23/2006 :  05:51:24  Show Profile  Reply with Quote
quote:
Originally posted by sleaklight

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76613

try the method I got mine up and running again after only downloading the mdf and not the ldf, you're basically in the same situation as I was and got it working with the code I posted in one of my last replies



Nice I think that got me back on track. Good code for setting up a DB with only your MDF file.

Thank You
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000