question

vivekchandra09 avatar image
vivekchandra09 asked

Upgrade from sql server 2005 to SQL server 2008r2

I have a server on which the application and sql server 2005 database resides. I need to upgrade on a different server 2008R2. What steps I need to take?
sql-server-2005upgradesql 2008r2upgrading-access-to-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
If you're upgrading on a different server, you have a couple of choices. It really depends on the size of your databases and the amount of downtime you can afford. Let's assume the databases are small (less than 300gb) and you can afford downtime. 1. Shut down your application 2. Backup the database on the 2005 server 3. Restore the database on the 2008 server You're done. Now, let's assume the database is large, or, you need to avoid downtime 1. Ensure your database is in Full Recovery mode 2. Take a backup of the old server 3. Start capturing log backups on the old server 4. Set up [log shipping][1] on the new server using the backup and the log backups 5. When you're finally ready to switch over, shut down your app 6. Take a final tail log backup 7. Restore that to the new server 8. Recover the database on the new server. 9. Restart your app. Downtime in this approach is going to be very minimal. Just the amount of time it takes to do the last log backup and the last log restore. [1]: http://Start+capturing+log+backups+on+the+old+server
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I would make sure you include and test all your server objects like logins, linked servers, SQL Agent jobs etc..
3 Likes 3 ·
Great point John. Wasn't thinking it all the way through.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.