need help here pls SQL server restore database urgent

[mikee_phil]

i created a full backup FAASdatabase_db_200702251902.BCK when i tried to restore the back up set on a different database (force restore) complete i get this error

restore database faasdatabase
from disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
FAASdatabase_db_200702251902.BCK'

Server: Msg 3135, Level 16, State 2, Line 1
The backup set in file 'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\FAASdatabase_db_200702251902.BCK' was created by BACKUP LOG and cannot be used for this restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

the i try restore log

Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

i got this error

restore log Faasdatabase 
FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\FAASdatabase_db_200702251902.BCK' 
with norecovery

[szlamany]

That error is indicating to me that you did not CREATE that BACKUP as a full backup - but instead as a BACKUP LOG.

Do you have any other .BAK files?

[mikee_phil]

i think i accidentally overwritten the full back up with the transaction log backup.. i have no other prev bak files.. i have one but it is very old nov 2006, i manage to restore it then i use the bak files FAASdatabase_db_200702251902.BCK to restore it from wat it is now then im having that error <b>Server: Msg 3135, Level 16, State 2, Line 1
The backup set in file 'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\FAASdatabase_db_200702251902.BCK' was created by BACKUP LOG and cannot be used for this restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
</b>

[mikee_phil]

can i restore an .mbf file without 2 of its .ldf ... my database has 4 .ldf transaction log files i have formatted my computer i forgot to back up the 2 .ldf, i allocated it separated from the data folder of the MSSQLserver... can i still recover my database it is very important

[szlamany]

MDF files contain data

LDF files contain transactions waiting to be committed.

You cannot regenerate the full data of a MDF file using LDF backups.

Whenever you have a critical situation the first thing to do is detach the database and physically copy the MDF and LDF to a save folder. This is needed so you always get back to the "moment of the critical situation".

Then you start doing a recon of what MDF and LDF backups you have.

BACKUP FILELISTONLY will show you what is in the .BAK file.

Sometimes there are "two backup sets" in a .BAK file - you usually cannot overwrite the .BAK file - it's simply appended to.

So slow down and start doing a recon of what your .BAK files have.

Get into the HELP FILE for BACKUP FILELISTONLY and learn about backup sets - so you can analyze what you have on hand.

Also - you can sometimes tell what's in a .BAK by it's size...

[mikee_phil]

at the attachment , my .bak contains those items how can i restore it? it is the complete data and log files of my database

[szlamany]

Did you overcome your problems?

I got real busy yesterday - but from the looks of that image you have all the backup sets you need in that one .BAK file.

Did you try to restore any of this?

[mikee_phil]

when i restore the .bak file i always get the error

Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

but i got this procedure from some site and i manage to recover all the data i need:
heres the scenario , when i formatted my pc i forgot that my log file is in another directory, i only have .mdf and 3 ldf. ,the 2 .ldf was deleted becuase it is on another directory

By space corp:
1. You're log file got too big so you decided to shutdown SQL Server, then delete the log file.
2. Salvage data from a damaged SQL data (.MDF) file.
 
solution:
1. Backup the data (.mdf) file! Just in case. We take no responsibility for anything that happens following this
procedure.
2. EXEC sp_detach_db 'dbname' -- this will detach the database from the server
3. Restart SQL Server
The database may still be seen in enterprise manager, but just ignore it.
4. Create a new database with the same name or a different name. You will have to use a different physical
file name, which is fine.
5. Stop SQL Server.
6. Rename the new data file that was created to something else (ex: add.bak to the end)
7. Rename the old data file that you want to restore to the name of the newly created file (the same name as
the file you changed in the step above)
8. Start SQL Server
Now the db will still be suspect but you now have a log file.
9. Switch to emergency mode on the database. You do this by doing the following:
1. Right click on the database root node in Enterprise manager and bring up the properties.
2. Under the Server Settings tab, check of "Allow modifications to be made directly to the system
catalogs".
3. click ok
4. Now go to the master database and open the sysdatabases table.
5. Find the suspected database in here and modify the status column, setting it to: 32768. This will
put it into emergency mode.
6. stop then start sql server
10. Now here's the tricky part and I'm not sure how this will work on a single install, i was lucky enough to
have SQL Server 2000 installed. But anyways, open up the Import and Export Data (DTS) program from
the start menu. And you want to copy data from the old database to a brand new one. Just copy tables
and views.

[szlamany]

If you showed us the command you used to do the RESTORE that caused that error to be displayed we might be more helpful!

I believe that you could have restored from the backup...

I don't understand what you mean by having more than one .LDF - why would you do that? How big is this database? What is a 3 .ldf and a 2 .ldf?

I'm confused...

[mikee_phil]

scenario: i am formatting my pc so i backup the data folder of msqlserver, the two transaction log file of my database is on another directory so it was lost:
my database contains (the picture above) my mdf is 600MB+ and my ldf files is 6.5GB+, when i am attaching the database to my pc it was looking for the two ldf files.

i dont know how to properly shrink the transaction log file when it grew up.
here's how i do it: my primary ldf is limited to 3 mb file size then the others are to unlimited, when it grew ups i just back up the transaction log then shrink the database then i can delete those other transaction log bec it is empty now.. can you show me how it is properly done :

so my only option is to used my back up set
i try this first

restore database faasdatabase
from disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
FAASdatabase_db_200702251902.BCK'

heres the error:
Server: Msg 3135, Level 16, State 2, Line 1
The backup set in file 'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\FAASdatabase_db_200702251902.BCK' was 
created by BACKUP LOG and cannot be used for this restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

then i try this
restore log Faasdatabase 
FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\FAASdatabase_db_200702251902.BCK' 
with norecovery
but i think this only works if the database is intact

heres the error:

Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. 
Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

i even experiment
i used 1 back up set dated dec 2006 and used the backup set i have(thinking it it is a transacton log backup set)

restore database faasdatabase
from disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
FAASdatabase_db_900302251902.BCK'(backupset dec2006)
with norecovery
restore log Faasdatabase 
FROM DISK = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\FAASdatabase_db_200702251902.BCK' 
with norecovery

an error stating it is not a part of something....

i have my back up script of the database thankfully so i created the database using the script and the above procedure by space corp i manage to get all the data on the tables

[szlamany]

You have six backups in that .BAK file - you saw them all in the image you posted back in post #6.

Books Online has all this information - but here's my read on it - hopefully this makes sense. If you need clarification go into the HELP from ENTERPRISE MANAGER and this is where you find BOOKS ONLINE. I looked up help on RESTORE/RESTORE (described)...

There are dozens of examples and explanations here as well.

At any rate - you need to restore the database backup first. It's FILE 1 - as that image in post #6 showed us.

This should work:

restore database faasdatabase
from disk='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
FAASdatabase_db_200702251902.BCK'
with file=1, NORECOVERY

Notice the FILE=1 - this directs the RESTORE to use FILE SET #1 from the .BAK file. I believe that FILE 1 and FILE 2 will both be restored - as the backup of FILE 1 and 2 was done with the same single BACKUP command.

By the way - next time don't put several backups into a single .BAK file - it makes restores a nightmare - as you have discovered here!

Also notice the NORECOVERY. As BOL talks about - if you plan on doing subsequent LOG RESTORES you must specify NORECOVERY. If you don't then the restore will rollback any uncommitted transactions in the log file. This makes sense as if you don't have any other logs to restore you want the uncommitted transactions to be discarded as they were in-process and incomplete.

At any rate after you do the first RESTORE - with FILE = 1 and NORECOVERY - note the messages indicating what file sets were restored.

Then do RESTORE LOG's - also indicating a FILE = x, keep incrementing the x till all the backup sets are restored. Use NORECOVERY on each one until you get to the last one. The last one must specify WITH RECOVERY.

Here's some questions for you about your setup...

Do you need multiple log files? Also - read up in the link in my signature for shrinking log files. You need to have maintenance plans to keep all this in check. But let's get it restored first!