| Author |
Topic |
|
vvkp
Starting Member
12 Posts |
 Posted - 2004-04-15 : 15:17:22
|
| Hi friends,I have the data files both primary and secondary. Unfortunately we missed the logfiles. Is there any way to restore the database from the data files without logfiles?Thanks in advance. |
|
|
surefooted
Posting Yak Master
188 Posts |
Posted - 2004-04-15 : 15:56:21
|
If it was just a single file you could use sp_attach_single_file_db, but since its not, use this:CREATE DATABASE dbnameONPRIMARY ( NAME = Primaryfilename, FILENAME = 'X:\primary.MDF', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20),( NAME = SecondaryFilename, FILENAME = 'X:\Secondary.ndf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20)FOR ATTACH |
 |
|
|
vvkp
Starting Member
12 Posts |
Posted - 2004-04-15 : 16:52:38
|
| Thanks for your suggestion...but unfortunately I am getting the error...Server: Msg 5173, Level 16, State 2, Line 1Cannot associate files with different databases.Any advice please. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-15 : 16:55:58
|
| Is the database down, meaning has it crashed? IOW, why are you needing to do a RESTORE? It sounds like your MDF and NDF file are already attached to the server.Tara |
|
|
|
surefooted
Posting Yak Master
188 Posts |
Posted - 2004-04-15 : 17:01:01
|
| What caused this situation? |
|
|
|
vvkp
Starting Member
12 Posts |
Posted - 2004-04-15 : 17:01:44
|
| Thanks for your posting...Here are the deatails...Actually somebody detached the database. The database has two data files and two logfiles. Unfortunately we missed the logfiles. Also we don't have the database backup. In the present scenario how can I restore the database with the remained two datafiles? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-15 : 17:04:39
|
| What do you mean you missed the log files? If the database was detached, then the files will be on disk. Did someone delete the LDFs?Tara |
|
|
|
vvkp
Starting Member
12 Posts |
Posted - 2004-04-15 : 17:09:49
|
| Yea...it seems somebody deleted the log files erroneously. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-15 : 17:16:32
|
| How much data was on the secondary file? If not a lot, you can try:EXEC sp_attach_single_file_db @dbname = 'pubs', @physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'Tara |
|
|
|
vvkp
Starting Member
12 Posts |
Posted - 2004-04-15 : 17:18:49
|
| The file size of secondary one is 2160 MB. |
|
|
|
surefooted
Posting Yak Master
188 Posts |
Posted - 2004-04-15 : 17:25:32
|
| Do you have a current backup? If the sp_attach_db works, you could try a partial restore of the secondary file group.-JonJust a starting member. |
|
|
|
vvkp
Starting Member
12 Posts |
Posted - 2004-04-15 : 17:31:26
|
| Nope...we don't have the current backup. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-15 : 17:31:46
|
| How would you do a partial restore of the secondary filegroup though? He doesn't have a backup of the filegroup, just the NDF file. So once you attach the MDF, he'd have lost the info from the NDF cuz you now can't attach it.When you ran the CREATE DATABASE statement that Jon posted, did you change the command to what fits your environment such as dbname changed to the name of your database? Does the database show up when you run SELECT name FROM master.dbo.sysdatabases in Query Analyzer?Tara |
|
|
|
surefooted
Posting Yak Master
188 Posts |
Posted - 2004-04-15 : 17:39:58
|
| I was hoping he had a current backup. He could have then tried a partial restore to a different db and copied over the data from the secondary file group.-JonJust a starting member. |
|
|
|
surefooted
Posting Yak Master
188 Posts |
Posted - 2004-04-15 : 17:41:31
|
| Also, you may want to look into some delete recovery programs if the data was truly important and turns out to be unrecoverable. Not sure how well they work. I've never used one.-JonJust a starting member. |
|
|
|
vvkp
Starting Member
12 Posts |
Posted - 2004-04-15 : 17:45:58
|
| When I run SELECT name FROM master.dbo.sysdatabases the database name is not coming. Also I rechecked the Jon statement once again to our environment and tried....but no luck ...same error. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-15 : 17:50:35
|
| Do you have any backups for this database even if it is a bit old? If not, I think your database is lost at this point.Tara |
|
|
|
vvkp
Starting Member
12 Posts |
Posted - 2004-04-15 : 17:56:38
|
| Yep..we have database backup some ten days old. But after that many changes were taken place. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-15 : 17:58:35
|
| I'd RESTORE that and then seriously look at your backup plan. We perform full backups every night and transaction log backups every 15 minutes.Tara |
|
|
|
vvkp
Starting Member
12 Posts |
Posted - 2004-04-15 : 18:01:37
|
| its purely some trial run database and hence we didn't have that much backup plan and hence I didn't concentrate on taking fresh backups and hence now we are paying for that :) |
|
|
|
lss
Starting Member
1 Post |
Posted - 2007-04-03 : 17:13:37
|
| I'm having this error as well "Cannot associate files with other databases" All the backups are there. When i did a point to recovery, it gave me this error and it automatically point to the latest backup which have problem. |
|
|
|
Next Page
|
Restoring Database without logfiles?