Q. What is the best way to move a number of SQL Server databases to another storage device, such as a storage area network (SAN) disk?

A. There are several ways to move SQL databases between storage devices. Remember, a database consists of the database and its transaction logs. All the approaches mentioned here require some database downtime.

One solution with minimal downtime would be to take a volume shadow copy, restore the copy to the new location, stop the service, copy over and replay the transaction logs, then start the service.

If there’s a maintenance window during which some downtime is OK, and everything being moved relates to SQL Server, then you can detach and reattach the database. First, make sure you’ve backed up all the databases being moved.

1.Stop the SQL Server, SQL Agent, and MS Search services.
2.Copy the system database files to the new storage device. If the SQL Server environment is a clustered environment, you need to ensure that the folder structure and the drive letter are kept the same.
3.Copy user database files to the new storage device.
4.Restart the SQL Server, SQL Agent, and MS Search services.
5.Attach the user databases.
6.Move other folders (e.g., backups, Repldata) to the new storage drive. If the drive letter for the backups and Repldata changes, the associated scheduled SQL Server jobs need to be modified to reflect the change.
7.Copy any other folders or files that will be accessed by the jobs (e.g., DTS packages, stored procedures) to the new storage drive.
8.Run DBCC CHECKDB to verify database consistency.

You’ll need to provide new drive letters for the new storage site to the server that SQL Server is on. More detailed information about detaching and reattaching databases is available in the Microsoft article “How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server” ( support.microsoft.com/kb/224071).

You can also use drive-letter swapping to move the databases to a new storage site. Make sure you have backups for all the databases being moved.

1.Stop the SQL Server, SQL Agent, and MS Search services.
2.Replicate the data to the new storage drives through a utility.
3.Swap the new storage drive letters with the corresponding old storage drive letters.
4.Modify the stopped services’ startup type to "manual."
5.Reboot the database server.
6.Restart the SQL Server, SQL Agent, and MS Search services.
7.Run DBCC CHECKDB to verify database consistency.
8.Modify the stopped services’ startup type to "automatic."