| Author |
Topic |
|
Eswaraiah M
Starting Member
1 Post |
 Posted - 2012-01-07 : 09:07:04
|
| Hi Experts,I m trying to restore .mdf and .ldf files of a database of 2005 to 2008R2,I tried jus copying files into DATA folder where my 2008 is installed and start,stop of SQL Server Servces.But database is not reflecting the database.can any one help in doing this indetail.Thanks in advance,Eswaraiah |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-07 : 09:10:55
|
| you need to attach the database files by connecting to server through SQL Management studio------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-08 : 03:36:58
|
| To "restore" you need to start with a backup you made on the original server.If you want to "copy" the MDF and LDF file you will then have to ATTACH the files as Visakh said.To use the "copy" method the SQL Service on the original server MUST have been stopped at the time you made the copy, or the database MUST have been Detached, otherwise you may find that the database, once attached, is corrupted.We never DETACH a database without first making a Backup, in case it fails to re-ATTACH, so on that basis we would always restore from a backup, rather than Detach, Copy and Attach; also, a Backup file is smaller than the MDF + LDF, so will copy more quickly (which can be an issue on very large databases)SQL will automatically "upgrade" the database from SQL 2005 to SQL 2008 R2 when you Restore it or Attach it. However, you may then need to change the Compatibility Level and Reindex / Update statistics to get optimum performance under SQL 2008 R2. See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230 for details on migrating from SQL 2005 to SQL 2008. Note also that you may have compatibility problems upgrading from SQL 2005 to SQL 2008 R2 so you should make a regression test of the application |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-08 : 04:51:51
|
quote:
We never DETACH a database without first making a Backup, in case it fails to re-ATTACH, so on that basis we would always restore from a backup, rather than Detach, Copy and Attach; also, a Backup file is smaller than the MDF + LDF, so will copy more quickly (which can be an issue on very large databases)
What is the reason for reattach to fail? I have seen many people refer to this as a possibility. If the database was detached, presumably it is in a consistent state, so theoretically it should always successfully reattach, shouldn'nt it? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-08 : 06:33:34
|
| Interesting point (now I've actually had to think about it!)I presume that ATTACH will do the same things as SQL Startup to check that the database is valid. So the database might be in a broken state and would thus fail to ATTACH and (I think) by inference would fail on a SQL Restart too.BACKUP is going to accurately record the corrupted data in the database so a RESTORE is going to fail too (I think).But ... having said all that!! I suppose if you BACKUP and RESTORE to the second machine, and the RESTORE fails, then at least you know WHILST the original is still "running" and could then take a final (tail) backup of the LOG file on the original server and try a restore from earlier, uncorrupted, FULL backup and all subsequent LOG backups on a second machine to see if the database can be restored without data loss, before it next gets taken off-line, and if necessary use DBCC "with data loss" etc. on the original to try to rescue it.But I'm beginning to think that IF the original is damaged all bets are off anyway, so only benefit of not detaching is to be able to know that it is bust - but there may only be a slim chance that that "knowledge" will help with rescuing the database.Perhaps Gail or someone knowledgeable on such "disaster scenarios" could comment? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-08 : 07:10:11
|
| I once had an experience where one of client dbs restore action was failing using backup but detach/attach was working.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-01-08 : 09:49:30
|
| Barring anything really unusual happening, both a backup/restore and an detach/attach will work just fine. I've seen problems with attach/detach due to file permissions being changed during the detach.I think the biggest issue with attach/detach is that people try to just attach the mdf file - and have SQL Server try to rebuild the log file. This will not always work - depending on what was going on at the time when the database was detached.Jeff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-08 : 10:52:41
|
| "I think the biggest issue with attach/detach is that ..."One I would add is where someone has copied the MDF/LDF where SQL Service was still running, and then the file was in-limbo at the time and the ATTACH was of a "corrupted" file. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-08 : 11:24:19
|
quote:
Originally posted by Kristen
I presume that ATTACH will do the same things as SQL Startup to check that the database is valid.
Not so much. A detach should cleanly shut the database down before releasing the files (should, there are cases like full log where it can't, but you get errors/warnings in that case). If the database was cleanly shut down there's nothing to be done on reopening it. SQL will check the DB header and the log header and a couple other things, but it shouldn't have to do any actual workEven if it wasn't, providing the log is intact the attach should succeed. The only time it won't is if there's nasty corruption of the active portion of the log (in which case a backup would likely fail) or some corruption of the data file that's just happened (and hence would be affected by rollback/roll forward of transactions.Detach a database that's suspect, recovery_pending, restoring or recovering however and don't expect to be able to reattach it. quote:
BACKUP is going to accurately record the corrupted data in the database so a RESTORE is going to fail too (I think).
Maybe, but a restore will only fail if it's run WITH Checksum and there's been damage to the backup or there's some severe damage to the backup that results in a useless DB once restored or damage to the backup headers, or a few other structures. Or there was some ongoing corruption of the database file at the time of backup, affecting pages involved in active transactions (corruption of the portion of the log in use by ongoing transactions would likely cause the backup itself to fail)quote:
But I'm beginning to think that IF the original is damaged all bets are off anyway, so only benefit of not detaching is to be able to know that it is bust - but there may only be a slim chance that that "knowledge" will help with rescuing the database.
Why do you think my second rule for dealing with corrupt/suspect databases is 'Do Not Detach'?--Gail ShawSQL Server MVP |
|
|
|
|
Restore .mdf and .ldf files from 2005 to 2008 data