Database Simplified Headline Animator

Database Simplified

Thursday 24 November 2011

Move Database In SQL Server With Minimum DownTime

When working with very large database and there is need to move database from one server to another, then downtime is always an issue in such cases. There are several ways to move database from one location to another. Here I am describing an efficient way to move database from one location to another location. Following option only works if database recovery modal is Full or Bulk Logged. Let’s see how to move database in following scenario.

Suppose database needs to be moved from Server-1 To Server-2, Total size of database is 50 GB or More. Database is online at Server-2.

Step 1 : Take Full Backup Of Database

Step 2 : Move Full Backup Of Database to Server – 2

Step 3 : Restore Database At Server – 2 With No-Recovery Option

---Down Time Starts Here

Step 4 : Disconnect All The User From Server – 1 Database. Make sure no user is accessing the database at Server – 1.

Step 5 : Take Transaction Log Backup of Database

Step 6 : Move Transaction Log Backup To Server – 2

Step 7 : Restore Transaction Log Backup At Server 3 With Recovery Option

Step 8 : Finish and Your Database is Up and Running at Server – 2.

So as you can see that down time start only when there is only a log backup pending to be restored, Since log backups are small in size thus can be moved and restore with small amount of time.

No comments:

Post a Comment