November 30, 2012 at 7:45 am
I will need to migrate my current SQL 2005 database over to 2008R2, other than back and restore, I wonder what would be the best practice to migrate my database.
My current database size is roughly 1G, there are 100 stored procedures and functions, a few maintenance plans.
As per requirement, I probably will be requested to provide script to run the migration by a different group member, and I won't have access to the new server.
Please recommend a good and simple (if possible) way to do this.
Thank you very much in advance.
November 30, 2012 at 8:42 am
Get yourself a test server running SQL 2008 R2.
Try out all the possible ways of doing this.
Detach / Attach (make a copy if needed), SSIS, backup / restore.
Script the one you like the most and test it.
Testing it being the best practice here.
November 30, 2012 at 12:35 pm
Hi
I've never done it through Attach/Dettach, but by restoring from a fresh, perfectly good backup it went like a breeze.
But like the previous poster said, test it. Although dettaching a heavily used database during work hours is kinda traumatic and expensive experience.
Cheers.
____________________________________________________________
If you can't do things right at the first time, don't try skydiving. I won't.
November 30, 2012 at 12:48 pm
Backup and Restore is the fastest and most reliable way to do this imo. I am not sure you can even detach a 2005 db and attach it to a new version. Not sure that would even work. The big downside there is you still have to copy the file so you have backup.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply