April 11, 2010 at 2:26 am
Hi,
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,
April 11, 2010 at 3:31 am
Blog entry by Kimberly Tripp:
Moving Databases Around - Options
Both methods will work - but whichever you choose, be sure that you have a fully recoverable backup before you start.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 11, 2010 at 12:31 pm
Thank you.
April 12, 2010 at 3:11 am
backup and restore is always best approach.
April 12, 2010 at 3:20 am
chanti2985 (4/12/2010)
backup and restore is always best approach.
There are very few absolutes when it comes to SQL Server - and this is not one of them.
Read Kimberly's article - each method has advantages and disadvantages.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2010 at 5:47 am
I agree each method has it's advantage and disadvantage but I prefer attach/deattach as it is faster compared to backup/restore.
April 13, 2010 at 7:45 am
sejal p gudhka (4/12/2010)
I agree each method has it's advantage and disadvantage but I prefer attach/deattach as it is faster compared to backup/restore.
If you are going to detach with the intent to reattach for upgrade, make sure you use the option to skip statistics updates to avoid wasting that time and IO load. Likewise read up on issues if you use full text indexing. See BOL.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 13, 2010 at 7:57 am
after reading the kimberly blog i'd like to add a pro for backups:
if your in full recovery mode you can make a backup of huge files a day before a migration. copy that file ahead and apply transaction log backups later. this also depends on how much time it costs to apply those log backups.
take a 500gb database. you want to release on day 2: you can make a backup on day 1, copy the 500gb to a new server. and make log backups in between like very hour. you can restore those when those are made. on the final switch you make a final log backup which may be 500 mb and apply that on the destination.
result: lower downtime.
April 14, 2010 at 12:11 am
attach/detach
Pro: Fast, and you already have access to physical files for you to port it.
Con: you have to take the database offline for you to port it to another box
backup/restore
Pro: you don't need to take the same database offline to port it to another box
Con: you will need space for this to work. (in theory if you only have a 10Gig Drive, and your database occupies 9Gig, you wont be able to do a back up and restore.)
DISCLAIMER:
these are my own views only based on my experience.
http://babolnart.wordpress.com/
April 16, 2010 at 4:01 am
There are additional points in this discussion.
April 16, 2010 at 8:26 am
I have had issues with a re-attach...
There is no guarantee on a re-attach so I would always use the bak file.
April 16, 2010 at 10:36 am
krypto69 (4/16/2010)
I have had issues with a re-attach...There is no guarantee on a re-attach so I would always use the bak file.
Like others have said, there are pro's and con's to each approach...I generally like this approach unless I have a real good reason to do otherwise...I have had the best luck with a backup and restore.
April 16, 2010 at 1:17 pm
me 2 talkto lee..
all it takes is that one time when you can't re-attach and you'll always use a bak
April 16, 2010 at 7:11 pm
krypto69 (4/16/2010)
all it takes is that one time when you can't re-attach and you'll always use a bak
Until that one time when the backup turns out to be corrupt 😉
I think we established some time ago that both methods have advantages and disadvantages. Which one is 'best' to use therefore depends on a particular definition of 'best' given the exact circumstances.
All the posts that say 'well, I would always use method x' are just (valid) personal opinion.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 17, 2010 at 3:35 am
chanti2985 (4/12/2010)
backup and restore is always best approach.
that's jumping in both feet first 😉
all this is dependant on the network performance and server performance. Remember with backup\restore you have to backup the file first (takes a while on a large database) and as the backup file is not compressed you then have to copy that file across the network. You then still gotta restore the database too (takes just as long as the backup!).
I tested this some time ago when moving a whole bunch of user databases of varying sizes from one server to another and i found detach\re attach faster for that scenario.
Incidentally you dont actually need to detach the databases at the source, you can offline them and copy the files.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply