January 8, 2013 at 2:40 am
Hello All,
I hope and I'm sure to find an answer with all the experts on this forum.
Well this is the situation, this is in my home lab and I'm looking to restore an SQl Express 2005 DB to a 2008 SQL server is possible using just the MDF and the LDF files? (no migration no backup and restore)
I've looked around the web and what I can see is attaching these two files to the SQL 2008 server would be enough?
one other thing is that I no longer have the credentials of the user account where this SQL Express DB is created?
January 8, 2013 at 2:48 am
you need to test (as i havent tried this )but i think you cant restore database of prior versions.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 8, 2013 at 2:54 am
There shouldn't be a problem with the version difference. Have you tried it, though? That's the best way of finding out. It will depend on whether the database was cleanly shut down - it's possible that the files will be corrupt otherwise. Don't worry about the security - you can fix that once you've successfully attached the databases.
John
January 8, 2013 at 3:12 am
Thanks for that,
January 8, 2013 at 3:18 am
mourad.b (1/8/2013)
Well this is the situation, this is in my home lab and I'm looking to restore an SQl Express 2005 DB to a 2008 SQL server is possible using just the MDF and the LDF files?
Yes, absolutely it is possible
I've looked around the web and what I can see is attaching these two files to the SQL 2008 server would be enough?
Correct, that's all that's needed.
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
January 8, 2013 at 3:20 am
John Mitchell-245523 (1/8/2013)
It will depend on whether the database was cleanly shut down - it's possible that the files will be corrupt otherwise.
That's attaching without the log file. When you have both files, doesn't matter if SQL was cleanly shut down or not.
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
January 8, 2013 at 3:25 am
John Mitchell-245523 (1/8/2013)
There shouldn't be a problem with the version difference. Have you tried it, though? That's the best way of finding out. It will depend on whether the database was cleanly shut down - it's possible that the files will be corrupt otherwise. Don't worry about the security - you can fix that once you've successfully attached the databases.John
No I havent tried this yet, but will give it a try this evening.
I presume that the SQL Express was cleanly shutdown and I will give it a try later on tonight.
All the best and Thanks
January 8, 2013 at 3:40 am
GilaMonster (1/8/2013)
John Mitchell-245523 (1/8/2013)
It will depend on whether the database was cleanly shut down - it's possible that the files will be corrupt otherwise.That's attaching without the log file. When you have both files, doesn't matter if SQL was cleanly shut down or not.
Thanks Gail - I didn't realise that. Do you happen to know whether it also applies if, for example, the LDF and MDF were backed up using open file agents?
John
January 8, 2013 at 4:12 am
John Mitchell-245523 (1/8/2013)
Do you happen to know whether it also applies if, for example, the LDF and MDF were backed up using open file agents?
Yes I do, and no it doesn't.
Unless the file backup tool either quiesced the IOs prior to the backup or somehow managed to copy the files at exactly the same nanosecond, there's a chance that the files are as of different points in time (small difference, but that's all it takes), and will fail to attach. If the log file was copied a smidgen later than the data file, the resultant files will probably attach. If the data file was copied a smidgen later than the log file, the resultant files probably won't attach.
Files that are copied when SQL is stopped or when the DB is offline or detached are static. Files that are copied while the database is active are not, and that's what may well make them not 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
January 8, 2013 at 4:18 am
GilaMonster (1/8/2013)
Files that are copied when SQL is stopped or when the DB is offline or detached are static. Files that are copied while the database is active are not, and that's what may well make them not attach.
Thanks for clarifying, Gail. That's what I was getting at when I talked about the database being cleanly shut down. Since the original post didn't mention how the files were obtained, I thought it safer not to assume.
John
January 8, 2013 at 4:26 am
Ah, no, different terms.
Cleanly shut down means that SQL had time to commit or roll back all active transaction and time to write all dirty pages to disk before either the SQL service was stopped, the database detached or taken offline. It means that no recovery process is needed when the DB starts up again.
There's nothing wrong with an unclean shutdown, SQL probably does those more often than clean ones (imagine shutting down the server while there's a transaction that's been running 3 hours active, SQL won't be allowed to take 3 hours before stopping)
Any time a database has been shut down (cleanly or not), it is safe to take the files (data and log) and attach elsewhere. It is not safe to copy the files of an active, online, in-use database.
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
January 8, 2013 at 4:31 am
Brilliant - learned something else! Perhaps I should have used the word "gracefully"? Anyway, I've edited my first post by striking out the word "cleanly".
John
January 8, 2013 at 3:24 pm
DB files attached, exported to new SQL 2008 DB and now vCenter upgrade kicking and running.
Thanks all for your help and comments.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply