December 16, 2008 at 11:02 am
Any help would be appreciated on this one.
A restore was done (today) with the latest backup of (151008), a backup wasn't taken prior to this restore and it turns out that the database has to be rolled back due to the data being to old off the backup dated (151008) there is no other backups to restore from.
Is it possible to attach the mdf and the ldf files to a new db? Does this have the latest data on it as it would if a restore never took place?
Look forward to your assistance guys.
Thx
December 16, 2008 at 11:09 am
Did you restore over the existing database, or alongside?
Did you make copies of the old mdf and ldf before the restore?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2008 at 11:12 am
If the restore was done overwriting the existing database and no backup of that database was done before the restore, I think your hosed.
December 16, 2008 at 11:16 am
Thx for replying to my post.
The restore was over the existing db 🙁 and im not entirely sure if there are copies of the mdf and the ldf files on the tivoli backup system. Apparently, the whole server is backed up not just the c:\drive where the sql backups are kept. So if this is the case then could the mdf and ldf files be attached as a new db?
Your advice is very appreciated here.
December 16, 2008 at 11:20 am
All I can say is maybe. You may luck out, or when you attach the mdf/ldf files, you may have a corrupt database.
December 16, 2008 at 11:25 am
extremenovice (12/16/2008)
So if this is the case then could the mdf and ldf files be attached as a new db?
Of the current DB, or of one of the backed up copies?
The current, sure, but it's not going to change the data in the database.
The abckups - probably not. If they were backed up by a file backup without stopping SQL or quiescing the IOs, they're very likely to be unusable, if the were backed up at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2008 at 11:38 am
The last backup of the server was last night so if the mdf & ldf files were backed up for that db, then i would want to use those. Is that what you mean by "of the current db?"?
In the past for other scenario's in order to copy a db I've attached these files and created a new name for the db. It seems to have left the data in a good condition.
sorry if im not very clear.
December 16, 2008 at 11:45 am
extremenovice (12/16/2008)
The last backup of the server was last night so if the mdf & ldf files were backed up for that db, then i would want to use those. Is that what you mean by "of the current db?"?
No. I meant the current one. ie the one attached to the server. Any other would be a prior version.
In the past for other scenario's in order to copy a db I've attached these files and created a new name for the db. It seems to have left the data in a good condition.
You're lucky. That is not the way to backup SQL databases and it is not guaranteed to work at all, or to produce a usable database when attached. You should exclude the SQL directories from the file-system backup, do regular SQL backups and have those backed up to tape/other backup location.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2008 at 12:12 pm
OK i understand you, but can you answer this for me please. If when you do a restore like in my situation are the mdf & ldf files overwritten in the restore, because if so then i am unable to use them?
The method of attaching the db has only ever been used for testing purposes when ive needed to copy the dbase to another server. As i said it seemed to have worked. Should i be practicing another method.
As of now regular backups will be scheduled, this is a one off, but as it stands i cant recover the data prior to today simply because of the last backup dating back to October, hence my asking about the Attach method.
Your help is valued.
Thx
December 16, 2008 at 1:04 pm
extremenovice (12/16/2008)
OK i understand you, but can you answer this for me please. If when you do a restore like in my situation are the mdf & ldf files overwritten in the restore.
Yes. When you restore a backup over an existing database, the data in the backup replaces the existing database completely.
The method of attaching the db has only ever been used for testing purposes when ive needed to copy the dbase to another server. As i said it seemed to have worked. Should i be practicing another method.
You should either backup and restore or explicitly detach the DB, copy and attach.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply