Restoring DBs from files not backups?

Question

Restoring DBs from files not backups?

steven 44886

Valued Member

Points: 50
More actions
December 1, 2014 at 5:50 am

#299003

Hi,
Until yesterday I had a server running SQL Server 2008 R2 - with all the SQL Server DB files on an attached disk array.
The server died - so I attached the disk array to a new server - and all the DB data files are visible there.
I installed SQL Server 2014 on the new server and am trying to work out how to point it at the existing database files.
I also have backups of the DB's - but they will take ages to copy over and restore - so it would be much easier to just use the db files. Should I restore the master db first (easy as its small)?
Any advice would be greatly appreciated.
thanks - reddal

Viewing 12 posts - 1 through 11 (of 11 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

For master, no. You cannot use the system databases from a previous version. Try to copy them over the SQL 2014 master and all you'll succeed in doing is preventing the instance from starting.

For the user databases, you can try CREATE DATABASE ... FOR ATTACH. It might work or it might not. If it doesn't, restore the database from clean backup.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

steven 44886 Valued Member Points: 50 More actions · Answer 2

Thanks for the help.

Looks like I am out of luck. I don't have the logfile for the database (it was on local storage on the dead server - so probably lost forever). When I try to attach the mdf file it tells me it can't rebuild the log file as there were open transactions at the time the server died.

So I think restoring from backup is my only option. Shame as I don't care about losing the transactions in flight at the time of the crash - and I have to delete the original mdf to restore (not enough space). I hate to delete the original mdf and *hope* the backup works...

Thanks again.

- reddal

kevaburg SSCoach Points: 18131 More actions · Answer 3

Hhi Reddal,

if I were you I would build a 2008R2 box, attach all of your user databases with new log files, run CHECKDB and if successful move them across to the new box. Even if you can only do once database at a time it wold be better than relying on blind luck.

I really would not want to rely on *hope* in this case.

Regards,

Kev

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

kevaburg (12/1/2014)
if I were you I would build a 2008R2 box, attach all of your user databases with new log files

SQL can't create new log files because the databases were not shut down cleanly.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

steven 44886 (12/1/2014)
Looks like I am out of luck. I don't have the logfile for the database (it was on local storage on the dead server - so probably lost forever). When I try to attach the mdf file it tells me it can't rebuild the log file as there were open transactions at the time the server died.

Restore from backup.

So I think restoring from backup is my only option. Shame as I don't care about losing the transactions in flight at the time of the crash

Sure about that? You don't care if the database is logically and possibly physically inconsistent? If there might be child rows without parent rows?

It's not about losing the in-flight transactions. It's about completely losing database consistency and the atomicity of transactions as that's what the log guarantees.

For example, if one of those in-flight transactions was an order header with 6 order lines and you forced a rebuild of the log you could end up with the order and 3 of the order lines. Or 5 of the order lines and no order header. Or the order header and no order lines. Etc.

- and I have to delete the original mdf to restore (not enough space). I hate to delete the original mdf and *hope* the backup works...

Surely the backups were tested when they were made?

External drives are cheap. Buy one and copy the mdf files to it for safety.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arrjay Hall of Fame Points: 3455 More actions · Answer 6

steven 44886 (12/1/2014)

Thanks for the help.

Looks like I am out of luck. I don't have the logfile for the database (it was on local storage on the dead server - so probably lost forever). When I try to attach the mdf file it tells me it can't rebuild the log file as there were open transactions at the time the server died.

You don't need the .ldf when attaching the .mdf. There is an option in the attach screen you need to tick so SQL creates a new blank .ldf file.

--------------------------------------------

Laughing in the face of contention...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

arrjay (12/1/2014)
You don't need the .ldf when attaching the .mdf. There is an option in the attach screen you need to tick so SQL creates a new blank .ldf file.

That's only true when the database was cleanly shut down before the log file was lost. The OP is getting an error saying the log can't be rebuilt because the database was not cleanly shut down. Hence SQL cannot just create a new log file.

The log is NOT an optional part of the database that can just be discarded and recreated. It's a critical part of the database that, if lost, cannot just be recreated in most cases.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

steven 44886 Valued Member Points: 50 More actions · Answer 8

arrjay (12/1/2014)
You don't need the .ldf when attaching the .mdf. There is an option in the attach screen you need to tick so SQL creates a new blank .ldf file.

I think that option only works if there were no outstanding transactions at the time of the crash. When I try it says it can't create a new log file because of this.

As far as I can tell - given there were transactions underway - the mdf file is completely useless - no possible way to do anything with it.

Anyway - I'm restoring the backup to another server so at least get to test it before deleting the mdf. Thanks for all your help.

- reddal

arrjay Hall of Fame Points: 3455 More actions · Answer 9

Yes, true. My mad. You cannot restore the .mdf with the .ldf when the database was handling transactions.

--------------------------------------------

Laughing in the face of contention...

steven 44886 Valued Member Points: 50 More actions · Answer 10

Just an update : the restore completed without any problems and the db looks fine. So all is good.

Thanks for your help.

- reddal

Tony Savoie Mr or Mrs. 500 Points: 511 More actions · Answer 11

GilaMonster (12/1/2014)
The log is NOT an optional part of the database that can just be discarded and recreated. It's a critical part of the database that, if lost, cannot just be recreated in most cases.

THIS.

It should be a pop up message everytime you launch SSMS. The general lack of understanding of the importance of the transaction log, even among "senior" DBA's, is astounding.