Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2010-04-11 : 04:27:48
|
| Which method is the best and safe methods for migrating SQL Server from one version to another??Is the dettach/attach or backup and restore??I know either of the method can used for migrating the sql server but would like know more which is the safest and advantages and disadvantages for the both the methods.Just want to know from the horse's mouth!Thanks, |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-04-11 : 06:31:01
|
| I prefer to use Full Back Up and Restore method - the advanatage is the lack of downtime. This of course will depend on the circumstances , such as , will activity continue while BACKUP is occuring - which would then mean you would also need a differential.The detach\attach method is quicker - as you don't need to create a new file , the main time consumer being the file transfers and attach.On most migrations - I try and negotiate some downtime for the application relying on the database. But this is not always possible , as it could be a 24 x 7 operation - with little room for downtime.Jack Vamvas--------------------http://www.ITjobfeed.com |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-11 : 12:55:22
|
| A BACKUP file will be smaller than the MDF + LDF, so that may influence the speed with which it can be transferred to a new machine. I normally put the Backup file across, restore, run DBCC CHECKDB and all the other steps for upgrading to the new version, if all is well then I re-restore, WITH NORECOVERY, copy across any TLog backups newer than the Full backup I first restored from, restore them, then put the source database into READONLY mode / DBO only (to stop any possible updates), take a final "tail" Tlog backup, and restore that on the new server WITH RECOVERY, perform the DBCC CHECKDB and all other migration steps, and bobs-your-uncle! |
|
|
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
Posted - 2010-04-11 : 14:50:20
|
Thank You |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-04-12 : 01:45:26
|
| Another point to consider is, if you detach a database and there is some corruption during the detach, you are left with very little options.. since the file wont attach and you dont/may not have a backup file.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-12 : 02:09:43
|
| Yup, house rule here never to DETACH unless a backup has been taken first ... at which point there is no preparation cost for using a Backup file over a Detatched file. |
|
|
|
|
|
|
|
|
Which is one is good either DETTACH/ATTACH or BACK