I've done a very silly thing and need help

[MattK]

After being put in a position where I had to deal with a SQL problem without very little SQL knowledge I have screwed a clients main database up.

I backed up the transaction log and then deleted the log. The database is shown as suspect and won't let me restore.

Am I F&%ked?


Any help in sorting this out will be rewarded with much kudos and thanks

[dshoaf]

this might be helpfull

http://bizforums.itrc.hp.com/cm/Ques...9cd0f9,00.html

[MattK]

I've worked out why the restore doesn't work.

The log backup is branched under what appears to be a fictious database back up, which can't be found when trying to restore. I can select the log backup seperately if I go through the from backup device option but it then says

The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITHNORECOVERY or WITH STANDBY for all but the final step.

Where do you specify that?

[rmiao]

Check syntax of restore in sql boos online.

[MAK]

restore database DB2 from disk = "c:\db1_fullbackup.bak" with norecovery,
move "DB1" to "c:\DB2.mdf",
move "DB1_log" to "c:\DB2_log.ldf"
go
restore log DB2 from disk ="c:\db1_tlogbkup1.bak" with norecovery
Go
restore log DB2 from disk ="c:\db1_tlogbkup2.bak" with norecovery
Go
restore log DB2 from disk ="c:\db1_tlogbkup3.bak" with norecovery
Go
restore log DB2 from disk ="c:\db1_tlogbkup4.bak" with recovery

[MattK]

Unfortunately because the transactionlog was so big it wouldn't let me do a full backup only back up the log.

I tried the last step but I get a similar error as before about specifying NOrecovery.

[MAK]

Choice 1
--------
without taking a full backup u cannot have a tranlog backup.

do you have your full backup somewhere?

restore that full backup and then restore the tranlog backup

choice 2
--------
if you are not worried about the tranlog. stop sql server and copy the .mdf file to a safe location.
then start sql server. delete the suspected database. do sp_attach_single_file_db pointing to the .mdf file u saved.

Choice 3
--------
I believe one of the Norton utilities can bring you back the deleted file. stop sql server and recover your log file.

[rmiao]

Don't understand what do you try to do. Do you like to do restore or backup? You can do full db backup no matter how big the log is. You said that you deleted log in your first post. Did you delete db log file? If so, have to restore db from full backup plus log backups.

[MattK]

Originally posted by MAK

choice 2
--------
if you are not worried about the tranlog. stop sql server and copy the .mdf file to a safe location.
then start sql server. delete the suspected database. do sp_attach_single_file_db pointing to the .mdf file u saved.

Get a device activation error when I try the attach.

[rmiao]

It's because the db is not detached properly.

[MAK]

Usually device activation erro occurs only when you do not pass the righ path. check the path of your .mdf file

[MAK]

BTW. you didnt answer whether you have a Full backup and a tranlog backup

[MattK]

I came clean with the customer and luckily they have a back up of their servers hard drive from last Thursday. I can rebuild the more recent data from their sites.

Honesty is the best policy after all.

Thanks for all your help.