Id lost my ldf and mdf files...

Question

Id lost my ldf and mdf files...

Smoke

Old Hand

Points: 308
More actions
May 30, 2004 at 12:30 pm

#107892

G'd Evenning !!!
First of all i don't know if this is the right place to make this thread, so please feel free to move it wherever it belongs.
Due to incredible situations we lost the PC where we had our data. Now all what we have left is the cd with the FE and a our last back up file.
Can some one please tell me if is posible to get our data back just from the backup file?
For any help deeply thank you.
Regards
Estuardo

Manifest plainness,Embrace simplicity,Reduce selfishness,Have few desires.
Lao-t'ze.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 1

Yes. You will need to install SQL Server on a new machine and run the RESTORE DATABASE command to restore your database from the backups file. Here is an example script:

RESTORE DATABASE databasename

FROM DISK = 'D:\backup\databasename.bak'

You will also need to restore the users the database was using. Hopefully, you had the master and msdb databases backed up as well, so you don't lose your users, dts packages, jobs, etc. If not, you will have to recreate these.

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.

Alamir SSCertifiable Points: 6085 More actions · Answer 2

you can make this also from Enterprise Manager..

1- make a new database ..(and name it the same as the old crashed one)

2- right click on this DB and choose "All Tasks" > "restore db"

3- then browse to the file location of your backup

I hope this help u.

Alamir Mohamed
Alamir_mohamed@yahoo.com

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 3

you can make this also from Enterprise Manager..

1- make a new database ..(and name it the same as the old crashed one)

2- right click on this DB and choose "All Tasks" > "restore db"

3- then browse to the file location of your backup

4- Click on the Options Tab and choose Force Restore over excisting database

Smoke Old Hand Points: 308 More actions · Answer 4

Thank You All!!!

I really appreciate your quick reply's.... but i guess we wont we able to recover our data... i did exactly as you said, but when i click ok to restore my DB a dlgbox pop up to saying:

The file on divice "c:\temp\backup\control.dat" is not a valid Microsoft Tape Format backup set. Restore Database is terminating abnormally

I guess it means that the backup file is corrupt, because as you can see we create a folder in the C:\ drive to copy the backup file, there is no tape at all. i also create and import some other dbs from other PC to verify that our SQL installation was ok, and it is, it works fine restoring and backing up from other dat files. Also i tryed with DMO in vb to restore it, with no luck.

One more time thank you very much for your kind help.

Regards

Estuardo

Manifest plainness,Embrace simplicity,Reduce selfishness,Have few desires.
Lao-t'ze.

Victor J Ramos SSC-Addicted Points: 497 More actions · Answer 5

When you attempt to restore your database SQL Server will perform a safety check to eliminate the possibility of overwriting your database unintentionally and fail the operation if the database already exits. Because of this safety check you have two alternatives to restore you database:

1- Let the restore operation of SQL Server re-create your database from the backup in one step. If you take this alternative you don’t need to create the database in your new server. SQL Server automatically will create and restore your database.

Use Enterprise Manager as follows:

Right click on “Databases”

Select “ALLTasks”

Select “Restore”

On “Restore Database” dialog box

type your database name

select “From Device” radio button

in Parameters select “Select Devices”

in “Choose Restored Devices” dialog box, add the complete path and the file name of your backup

select “OK”

select “OK” again

2- Create your database before the restore operation and disable the safety check to complete the restore operation successfully.

Use Enterprise Manager as follows:

Right click on your database

Select “ALLTasks”

Select “Restore”

On “Restore Database” dialog box select “Options” and then select checkbox “force Restore over existing database”

Go back to “General” on “Restore Database” dialog box

select “From Device” radio button

in Parameters select “ Select Devices”

in “Choose Restored Devices” dialog box, add the complete path and the file name of your backup

select “OK”

select “OK” again

Finally, as Derrick suggested if you have backups for your master and msdb databases use them to recover the logins, DTS packages, jobs, alerts, etc. If not you have to recreate them manually as follows:

- Create the logins

- Synchronize these logins with the database users

- Create DTS packages, jobs, etc.

Smoke Old Hand Points: 308 More actions · Answer 6

Hello Victor!!!

Thank you for your very descriptive help i really need that!!

I'm almost sure that my *.dat file is corrupted; after the back up file is selected i can't even switch from one tab to other the err message keeps poping up

Microsoft SQL-DMO (ODBC SQLState: 42000)

The file on divice "c:\temp\backup\control.dat" is not a valid Microsoft Tape Format backup set. Restore Database is terminating abnormally.

Do you know how to repair or software to repair this kind of files?

Please any help is truly welcome

Regards

Estuardo

Manifest plainness,Embrace simplicity,Reduce selfishness,Have few desires.
Lao-t'ze.

Alamir SSCertifiable Points: 6085 More actions · Answer 7

I wondered of something:

the file extension is .dat not .BAK (bakup) .. so .dat may be the Dat file of the DB.

So try to attach your Db .. by right click on Databases then All Taks then Attach Database

then press on buttton (...) and choose your file ... and Write a Name to it if you need .. and choose Sa as the owner

Note: there is no problem if there is NO Log .. the SQL Server will make a new one for you

may be this help u

Salam

Alamir Mohamed
Alamir_mohamed@yahoo.com

Smoke Old Hand Points: 308 More actions · Answer 8

Hello Alamir!!

First let me thank your reply and tell you that i appreciate your help. I gave a try to your suggestion.... with no luck. to attach a db to the server the file should be mdf and not dat so i get an Err saying that my dat file is not a valid file.... By the way the dat file is created by the server, and this is what we have as backup files. i had no idea that bak files can also be created by default

Regards

Estuardo

Manifest plainness,Embrace simplicity,Reduce selfishness,Have few desires.
Lao-t'ze.

Leslie I. Cairns SSC Veteran Points: 233 More actions · Answer 9

Estuardo,

If the file you have is really the db file, you may want to try the following:

- create a db on new server with the exact name as this 'backup file'

- shut down sql services and rename the newly created db to something like filename.mdf_save.

- copy over the 'backup file' to the directory where you just renamed the other file.

- start up sql services and see if the db comes up or not

Good luck

SQLBill SSC Guru Points: 51440 More actions · Answer 10

It's been awhile since you had the problem, have you solved it?

One question I have is: How was the backup done? Did you use SQL Server to do the backup (using BACKUP DATABASE commands) or did you use something like NTBACKUP? Or did you use backup software such as Backup Exec to do a backup to disk?

You could try this in Query Analyzer:

RESTORE DATABASE dbname

FROM DISK = 'c:\temp\backup\control.dat'

-SQLBill

iLoveSQL SSCrazy Points: 2888 More actions · Answer 11

I'd Like to know what is the version of the SQL server that you are using?

Smoke Old Hand Points: 308 More actions · Answer 12

G'd Evening Leslie, SQLBill & iLoveSQL

Following the same order as the replies were post:

Leslie:

i'd tryed your suggestion and didn't work. i got this err:

Database "mydbname" cannot be opened due to inaccesible files or insuficient memory or disk SQL SERVER Errolog fails.

BackUp database is terminating abnormally

Thak you very much for your attention.

No Bill, we faced the situation and accept that we lost our data.

The only way we make backUps is with enterprise manager.

When i run the code you gave me the same err pops up

Microsoft SQL-DMO (ODBC SQLState: 42000)

The file on divice "c:\temp\backup\control.dat" is not a valid Microsoft Tape Format backup set. Restore Database is terminating abnormally.

Our SQL version is 2000 with the 3rd service pack.

Thank you all

regards

Estuardo

Manifest plainness,Embrace simplicity,Reduce selfishness,Have few desires.
Lao-t'ze.

Katya-130420 Right there with Babe Points: 742 More actions · Answer 13

Just wondering - could it be that you are trying to restore an old SQL 6.5 backup?

SQL Server Microsoft Tape Format was first introduced in SQL 7, so backups are not compatible between SQL 6.5 and 7.0/2000.

Smoke Old Hand Points: 308 More actions · Answer 14

Hello Katya !!!

No, we just have the SQL server since 1 year ago, and the system was developed on this structure (after a painful data migration), before SQL we used to work with MS Access, in fact we still have some FE's in Access.

Thanks for your kind reply.

Estuardo

Manifest plainness,Embrace simplicity,Reduce selfishness,Have few desires.
Lao-t'ze.