I have a 1.4TB SQL Server Database that's struggling massively with disk I/O. We've installed a new SSD array into the server that will solve all of our problems, we're just debating the best way to move the database across. Ideally if we can do it without downtime, that's best. But where the choice is between two days of poor performance (e.g. while copying data) versus two hours of downtime, the latter might be preferable.

So far, the solutions we've come up with are:

  • Simple copy. Take the DB offline, copy the files across, change the locations in SQL Server and bring it back online. Rough figures estimate this will take up to five hours, which is not really acceptable, but it is the easiest solution.

  • Block-level copy. Using an rsync-like utility, we copy the files across in the background while the DB is up. When we're ready to migrate, we take the DB offline, do a differential copy across using this utility, then point SQL server at the new files and bring it online. Timing here is unknown. We don't know how long it will take to do a differential analysis of 1.4TB and copy that across. Our other concern is that the block-level copy will leave the files in some state unreadable by SQL Server and we'll have waste our time.

  • SQL migration. Create a new 1.4TB SQL data file on the new disk and disable autogrowth on all of the other files. Then run DBBC SHRINKFILE(-file_name-, EMPTYFILE) on all of the other data files in turn. Once all of the data is across, I'll take a scheduled window at some point to move the MDF file over to the SSD and remove the other unused files. I like this because it minimises downtime. But I have no idea how long this will take and whether it will cause degradation in performance while it's happening.

We don't have any kind of load & performance environment to test this. I can verify that the strategies will work on our staging environment, but not the impact and not the performance.

  • Your datafiles are stored on an LVM partition?
    – Marco
    Commented Dec 15, 2015 at 11:44
  • 1
    don't know how long it will take to do a differential analysis of 1.4TB at least as long as it takes to read that data. I don't think the rsync idea saves much if anything. rsync is made to cope with slow networks.
    – usr
    Commented Dec 15, 2015 at 12:26
  • 2
    Instead of using EMPTYFILE I would rebuild all indexes over to a new filegroup that lies on the SSD. That way the indexes appear nice and defragmented. EMPTYFILE might fragment them, not sure.
    – usr
    Commented Dec 15, 2015 at 12:27

2 Answers 2


One method to move the entire database is with BACKUP and RESTORE. The database will be unavailable during the final switch but downtime should be minimal with planning. This procedure assumes the FULL or BULK_LOGGED recovery model:

1) Perform a FULL backup (or use your existing one).

2) Restore latest full backup to a different database name, specifying the WITH MOVE option to relocate files on SSD storage as desired and the NORECOVERY option so that subsequent differential and log backups can be restored.

3) Apply incremental changes to the new database until the time of the final cut-over with transaction log backups and RESTORE...WITH NORECOVERY. This will minimize downtime for the final switch to the new database.

4) To switch to the new database, take the application offline, perform a final transaction log backup, and apply to the new database WITH RECOVERY. Finally, rename the original database to a different name and rename the relocated database to the original name. Drop the old database at your convenience.

In the SIMPLE recovery model, you could use a similar process but without the step 3 transaction log backup/restores. Instead, use a differential database backup/restore in the final step. That may require more downtime, depending on the amount of changes since the initial FULL backup.

  • Yup, nothing comes to mind as simplest and fastest for same server db movement.
    – Marian
    Commented Dec 15, 2015 at 22:17
  • I've done this to migrate DBs between old/new storage sub-systems. It works beautifully. I also contracted similar method to move the SYSTEM DBs as well, as it the activity costs only an instance restart vs. configure the change, shutdown the instance, copy the files and the start it up again. with this way I moved a complete instance with under 2 minutes of service interruption (~1 minute for the restart and ~5 seconds for each DB switch).
    – Roni Vered
    Commented Sep 22, 2020 at 14:01
Good approach is to use SQL REPLICATION between two server
once all data replicated on SSD server then take current server offline 
and switch to SSD server.
  • 2
    You're getting downvotes because you're not answering the question. There's only one server in the situation under discussion.
    – AakashM
    Commented Dec 15, 2015 at 13:55
  • Also a good approach to move dbs are mirroring, log shipping, availability groups, backups and.. others. This does not solves the problems, unfortunately.
    – Marian
    Commented Dec 15, 2015 at 22:18
  • On one server we can create two instances and make replication between two instances. Commented Dec 16, 2015 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.