Id lost my ldf and mdf files...
-
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. -
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. -
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 -
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
-
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. -
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.
-
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. -
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 -
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. -
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
-
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
-
I'd Like to know what is the version of the SQL server that you are using?
-
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. -
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.
-
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.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply