I am SCREWED!

[Jesus4u]

OK, I am NOT on our production server, thank God!

BUT...

Our transaction log was full today so I did some research on M$ and they instructed me to backup the logfile and then to delete the ldf file. Well this wacked the sql2000 db. So, I recreated the DB and tried restoring from the transaction log's backup but it says it can not restore because it is from the BACKUP LOG.

Whaz up? Can I restore or am I reduced to rebuilding the DB from scratch?



Edited by - jesus4u on 03/12/2003 10:21:06 Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[sguslan]

Restore the last backup then restore the transaction logs .. ________________________________________________________________________________
If you do not like change, get out of the IT business...

 

[Jesus4u]

thanks but I don't have the original logs. Misrosoft article told me to backup the log file then delete the original and restore from the backup, which I can not do. Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[SQLBill]

How did you backup the log file? Did you use the BACKUP LOG command? Or did you just copy the .ldf file?

-SQLBill

 

[Jesus4u]

I backed up from the Enterprise Manager Exams Passed: 70-152, 70-175, 70-176, 70-100

 

[SQLBill]

But did you do a database backup or a log backup...they are two different things. If you backed up the database and the log, you just need to restore the database and the log. If you only backed up the database, then you can't restore the log, because you never backed it up.

Backing up the database and/or the log creates a .bak file. You can do a file search to find any .bak files (search for *.bak), then compare the file names to your database and log names.

Or am I missing something worse? Did you backup the DATABASE also, or only the LOG? To restore any database you first need to restore a FULL BACKUP.

-SQLBIll

 

[tlbroadbent]

Does the MDF file still exist? If it does, you can attch it without the LDF file and your database will be intact. See sp_attach_single_file_db in SQL BOL. If you don't have the MDF, you will need to restore. As has already been explained, the restore process must start with a full database restore. Then you can restore the transaction backup if it is the only transaction backup since the last full backup. Otherwise, you must restore all transaction backups after the full restore.

I recommend some additional reading.

faq183-1534 - Shrinking Databases and Logs - SQL 7 and Higher

faq183-1784 - How Do I Develop a Backup and Restore Strategy?
If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SemperFiDownUnda]

every once and awhile you'll want to truncate your transaction log. Normally this happens with backups. I recomend getting a good book on administration of SQL server. This type of stuff is covered in the MOC.