September 8, 2011 at 12:02 pm
Is there any reason we might not want to use the offline detach at old server /re-attach at new new server method of migrating from SQL 2000 to SQL 2008 R2? I proposed this as a faster alternative to backup and restore, but my boss wants to know if there any negatives that come along with it.
We have about 160 databases (183 GB) that take 3 hours to backup currently. All are simple recovery model and set to autoshrink (I will be looking to change both these settings), so the logs are pretty small.
September 8, 2011 at 12:20 pm
Main drawback - there's no going back. Once the files are attached to SQL 2008 R2, you cannot more them back to SQL 2000. So if something goes wrong and you don't have a copy of the files, you're in trouble.
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
September 8, 2011 at 12:24 pm
Thanks, Gail. With the SQL Server 2000 stopped, we could just copy the mdf/ldf files to the new machine and leave them in place on the 2000 server. If we had to roll back our implementation, the 2000 server would be unchanged.
September 8, 2011 at 12:28 pm
I would still take a backup before. Paranoia is good for a DBA.
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
September 8, 2011 at 1:06 pm
Just to add my 2 cents-
pay attention to versions compatibility. I recently migrated 2005 Ent to 2008 Std and because of this issue (downgrade versions) could not use detach/attach.
September 8, 2011 at 1:26 pm
Andrew Notarian (9/8/2011)
Is there any reason we might not want to use the offline detach at old server /re-attach at new new server method of migrating from SQL 2000 to SQL 2008 R2? I proposed this as a faster alternative to backup and restore, but my boss wants to know if there any negatives that come along with it.We have about 160 databases (183 GB) that take 3 hours to backup currently. All are simple recovery model and set to autoshrink (I will be looking to change both these settings), so the logs are pretty small.
nothing wrong with detach\attach. Leave a copy of the files in place and make sure you have current backups just to be safe.
Generally i found it quicker to attach\detach. Consider the time it takes to backup, copy the file and restore its quicker to detach, copy attach.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 9, 2011 at 4:01 am
Perry Whittle (9/8/2011)
Andrew Notarian (9/8/2011)
Is there any reason we might not want to use the offline detach at old server /re-attach at new new server method of migrating from SQL 2000 to SQL 2008 R2? I proposed this as a faster alternative to backup and restore, but my boss wants to know if there any negatives that come along with it.We have about 160 databases (183 GB) that take 3 hours to backup currently. All are simple recovery model and set to autoshrink (I will be looking to change both these settings), so the logs are pretty small.
nothing wrong with detach\attach. Leave a copy of the files in place and make sure you have current backups just to be safe.
Generally i found it quicker to attach\detach. Consider the time it takes to backup, copy the file and restore its quicker to detach, copy attach.
+1
...and don't forget to update your stats once you've attached the db (and run checkdb of course).
September 9, 2011 at 4:12 am
If replication is there, better to avoid detach\attach
M&M
September 9, 2011 at 5:40 am
mohammed moinudheen (9/9/2011)
If replication is there, better to avoid detach\attach
Mohammed - care to share more information on that one-liner? Why would you want to avoid detach\attach, I've always considered this an excellent way of migrating databases that are small enough to copy (note copy and not move :-P).
If the DB's are huuuuuuge I would go for the full backup\restore + diff + trn option, I've never considered replication so I'd be interested to hear your experiences of it.
September 9, 2011 at 6:24 am
Paul Duffett (9/9/2011)
mohammed moinudheen (9/9/2011)
If replication is there, better to avoid detach\attachMohammed - care to share more information on that one-liner? Why would you want to avoid detach\attach, I've always considered this an excellent way of migrating databases that are small enough to copy (note copy and not move :-P).
If the DB's are huuuuuuge I would go for the full backup\restore + diff + trn option, I've never considered replication so I'd be interested to hear your experiences of it.
Well Paul, you wouldn't be able to detach a publisher database.
In order to perform the detach operation you would need to disable the publishing option using the sp_replicationdboption stored procedure. As per Books Online, if this stored procedure does not work, you need to use sp_removedbreplication stored procedure, which removes all the replication objects in the database.
So, if replication is there, better to try other methods instead of detaching db. Of course, if you have thoroughly tested the entire setup, then you could try it. I thought, generally replication environments are quite sensitive and critical and better not to take chances in production.
M&M
September 9, 2011 at 8:13 am
After the upgrade also run dbcc updateusage on all DBS.
Valid point on replication but it was never mentioned.
Paul given the time it takes to backup, copy, restore especially for large DBS I would detach, copy, attach
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 9, 2011 at 8:36 am
If you are actually looking for minimum downtime, backup/restore and then apply diff or tran log backups is faster.
You can create the backups and restore them with NORECOVERY before you are ready to cutover, then make and apply diff or tran log backups when you are actually ready to cutover.
Even with databases that are hundreds of GB in size, you could have down time of just a few minutes. I moved a 500 GB database over a WAN from one site to another using this method, and had less than 20 minutes of application downtime.
September 11, 2011 at 4:43 pm
Detach\Attach method works well. This is much faster than Backup\Restore if you try to do everything in the same maintenance window.
We can also go with restoring Full backups well ahead of time and then restoring differential backups and Tran log backups. But thats lot of work..
The less effort method is detach\ attach in your case. Also in detach\attach method the source databases can't me modified in any way during the maintenance window because they are already detached and are not accessible.
Thank You,
Best Regards,
SQLBuddy
October 13, 2011 at 2:11 pm
I apologize for hijacking this post, but I think your technique could be used to fulfill a request I've received. My users want a copy of a remote database saved onto a local server and kept in read only mode. Once a week they'd like it updated with any changes that may've been made on the remote database. I have created the DB on the local SQL Server instance but I don't understand the steps I need to take to: 1) Keep the DB in read only mode and 2) How to apply the log backups once I download them from the remote server. Can you advise on this? I have also created a new post with this request but haven't received any replies at this point.
Thanks.
October 14, 2011 at 2:07 am
gary.dunn (10/13/2011)
I apologize for hijacking this post, but I think your technique could be used to fulfill a request I've received. My users want a copy of a remote database saved onto a local server and kept in read only mode. Once a week they'd like it updated with any changes that may've been made on the remote database. I have created the DB on the local SQL Server instance but I don't understand the steps I need to take to: 1) Keep the DB in read only mode and 2) How to apply the log backups once I download them from the remote server. Can you advise on this? I have also created a new post with this request but haven't received any replies at this point.Thanks.
Hi Gary,
What you can do there is set up log shipping and leave the target database in a non-recovered, stand-by state, which for all intents and purposes gives you a read-only copy of the database.
There are some things to note however: If you are only carrying out a restore of the changes on a weekly basis that would mean only one log-backup per week, which is not recommended. If I were to make a recommendation it would be to set up log shipping as described above with either the default setting of 15-minute backup\restores or extend that to 1 hour. Another thing to note of course is that for the transaction log to be applied to the read-only database the SQL Agent will require exclusive access. This can be achieved by allowing the job to forcibly disconnect active connections - the reason I mention this is because if you are carrying out lengthy reports on your read-only copy it can be frustrating if you keep getting booted off.
Feel free to PM me if you need any more info.
Cheers
Paul
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply