DBAs are always asked to move database from one instance to another, make a copy of database on the same instance, change database files location etc. This requires from dba full understanding of what a database consists of and what a database depend on. The article gives a step by step instruction of how move database, copy database or change database files locations.

How to copy a database

This describes how to create a copy of a database with a different name but the same contents on the same sql server instance.

The easiest and safest method is backup-restore using GUI - SQL Server Management Studio or Enterprise manager for SQL Server 2000 and below versions.

Right click on the database you want to copy -> Tasks -> Backup

A dialog window will show up. Leave all options default and click Add to add backup file, for instance choose C:\Backup directory and mydb.bak file name.

After the backup task is completed  click on Databases node under you sql server instance main node, right click and choose Restore Database...

In To database field type the future name of your copy databas. Under Specify the source and location of backup sets to restore choose From device radion button and click ... button

 In showed up dialog click Add and find your backup for instance C:\backup\mydb.bak, click OK.

Mark Restore Checkbox for the file you have added.

Now you may need to verify file names and locations on Options page. Click OK when finished.

How to move database on the same instance

 In other words how to change location of some or of all database files (data files, log files and full-text catalogues).

1. Make full backup of the database you want to move and of master database.

2. Get the file list for the database including physical location and logical name

USE master
GO
SELECT name, location = physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(‘dbname’)
GO

3. Set the database to SINGLE_USER mode
USE dbname
GO
ALTER DATABASE dbname
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

4. Set the database OFFLINE:

USE master
GO
ALTER DATABASE dbname
SET OFFLINE
GO

5. Physically move the files and full-text catalogues of the database (returned on step 2) to a new location.
6. Modify the file names (physical locations) running the following T-SQL query:

USE master
GO
ALTER DATABASE dbname
MODIFY FILE (NAME = logical_name, FILENAME = ‘new_path\os_file_name’)
GO

Repeat this step for all data and log files to be moved (including full text catalogues).

7. Set the database ONLINE:

USE master
GO
ALTER DATABASE dbname
SET ONLINE
GO

8. Verify the new locations for the files:

USE master
GO
SELECT name, location = physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(‘dbname’)
GO

9. Set the database to MULTI_USER mode:

USE master
GO
ALTER DATABASE dbname
SET MULTI_USER
GO

 You could simply detach database, move the files and full-tezt catalogues and then attach database. But in case you set database offline its data cache and procedure cache are not dropped so after setting it back online it will still have non-empty caches which might be critical for production databases.

Moving database to another sql server instance

You should consider such things as logins, jobs, ssis packages, replication etc when planning to move your database to a new server.

1. Make full backup of the database to be moved and master database.

2. Get the list of data/log files and full-text catalogues for the database including physical location and logical name

USE master
GO
SELECT name, location = physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(‘dbname’)
GO

3. Set the database to SINGLE_USER mode

USE dbname
GO
ALTER DATABASE dbname
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

4. Detach the database using the next script

USE master
GO
EXEC sp_detach_db ‘database_name’
GO

If  the database contains full-text catalogue then use the following query:

USE master
GO
EXEC sp_detach_db @db_name=’database_name’, @keepfulltextindexfile=’true’
GO

5. Copy data and log files and full-text catalogues (taken on step 2) to the new server location.

6. Attach the database on the new intance using CREATE DATABASE Transact-SQL statement with FOR ATTACH clause:
USE master
GO
CREATE DATABASE database_name ON
(FILENAME = ‘data_filephysicalpath’),
(FILENAME = ‘log_file_physicalpath’),
(FILENAME = ‘physicalpath\full-text_catalogue_name’)
FOR  ATTACH
GO
 If the database does not contain full-text catalogues - remove the third filename line, if it does  - it is strongly recommended that you rebuild these full-text catalogues right after attach

7. Transfer the logins and passwords to the new server. To detect orphaned users, execute the following Transact-SQL statement on the destination server:
7.1
USE database_name
GO
EXEC sp_change_users_login @Action=’Report’
GO

On the source sql server instance run the following script (for the source code of sp_help_revlogin stored procedure address KB Article):
7.2
USE master
GO
EXEC sp_help_revlogin
GO

The output of this query is the login creation script. This login creation script creates the logins that have the original Security Identifier (SID) and the original password.

Review the output script carefully and select the logins you need to move (those returned by sp_change_users_login sp). Run the revised login creation script on destination sql server instance

8. Check for orphaned users again (Repeat step 7.1)

9. You may also generate scripts to recreate all jobs, alerts and operators. Connect to the source sql server instance from SSMS Object Explorer, open SQL Server agent node and right clicking on the objects you are interested in choose script Job/Alert/Operator as -> Create To -> New Query Editor Window. Compile all of this into one script and run the resulting script on the destination sql server instance.
 
10. As it was mentioned in the beginning you may also need to copy other items such as SSIS packages, replication, log shipping, named backup devices, maintenance plans, linked servers. Examine the source server for these and recreate them manually on the destination sql server instance. 

 The described methods apply to SQL Server 2000, SQL Server 2005 and SQL Sever 2008.