How to move SQLServer Database

For a DBA, it is one of the common activities to move the database to new folder or location or drive.

We have couple of options available to move the database in SQL Server.

1. Backup and Restore the database by using WITH MOVE option
2. Detach and Attach database using SQLServer Management Studio
3. Detach and Attach database using Transact-SQL

If you choose any option, I always recommend running DBCC CHECKDB after you move the database to check the database is structurally consistent.

Backup and Restore the database by using WITH MOVE option

Using Backup and Restore the database option you will be creating a new database at destination server and you can perform this action during online. If the database that you are moving is a production database then I advise to stop the application and take the backup.

When you use WITH MOVE option, the destination folder or driver must be available

Do not keep Backups on the same device where database is sitting. In case of device failure you should be able to restore the database using backup. Also keeping on separate drive enhances the I/O performance for both backups and database use.

Detach and Attach database using Server Management Studio or T-SQL 

When you use Detach and attach database you are moving the database instead of copying and you must perform this task during offline only.

When you detach a database, it removes the data and transaction files from the instance. You can attach these files to any other SQL Server instance

You CANNOT detach a database if any of the following is true

1. The database is replicated and published – Before you detach you must disable publishing by running sp_replicationdboption or remove replication by running sp_remove dbreplication.
2. Database snapshot exists– Before you detach you must drop all database snapshots
3. Database is being mirrored – Before you detach remove the Database Mirroring
4. A suspect Database – Before you detach you must put it into emergency mode
5. System Database – You cannot detach a System Database from Instance

When you attach a database, all f MDF and NDF files must be available. 

Make sure that no data files or file groups are in read-only mode. If primary data file being attached is ready only then database engine assumes that database is read-only.

Regards,

Satishbabu Gunukula

http://www.sqlserver-expert.com