SQL Server: Moving a Database

Three mechanisms for moving a database (within the same server, or between servers):

1. Backup and Restore
2. Copy Database Wizard
3. Detach/Attach

Detaching/Attaching databases

• Data files and transaction logs can be detached and then reattached to the same or different SQL Server instance.
• You may transfer databases across 32-bit and 64-bit environments.

Note that:

• Make sure you have a backup of the database before attaching it to a newer version server.
• Once you attach a SQL Server 2000 or 2005 database to a 2008 SQL Server, you will not be able to attach it back into previous version servers.
• After attaching a database to a new server, you will need to add users from the old server, if they are to continue to have access to the database.

You CANNOT detach a dataabse when:

• The database is replicated AND published. Before detaching you need to disable publishing (run sp_replicationdboption)
• Database contains a database snapshot.
• Database is being mirrored.
• While there are database sessions.
• Database is suspect. Needs to be put into emergency mode.
• Database is a system database.

Moving a database:

(a) Detach the database as follows:

    -- (1) Set database in single_user mode
USE MASTER;
ALTER DATABASE AdventureWorks2008R2
    SET single_user;
GO
    --(2) Check access mode 
SELECT name, user_access_desc
    FROM sys.databases
    WHERE name = 'adventureworks2008r2';
    --(3) Detach database. Set option to skip update statistics for true.
    -- By default, the detach operation retains any out-of-date optimization statistics  
    -- If you want updated optimization statistics to be gathered, set the parameter to 'false'
            .
EXEC sp_detach_db 'AdventureWorks2008R2', 'true';

    --(4) check that the database is no longer listed in the catalog
SELECT name     FROM sys.databases;

(b) MOVE/COPY data and log files to the new location
(c) Re-attach the database as follows:

USE MASTER;
GO
EXEC sp_attach_db @dbname = 'AdventureWorks2008R2' ,
    @filename1 = 'c:\mssqldrive\e\AdventureWorks2008R2_Data.mdf',
    @filename2 = 'c:\mssqldrive\e\AdventureWorks2008R2_Log.ldf';
    -- (2) Check that the database is in the catalog and the new file locations:

EXEC ssp_helpdb Adventureworks2008r2; 
name                 db_size    owner           dbid  created     status            compatibility_level
-------------------- ---------  --------------  ----  ----------  ----------------- --------------------
AdventureWorks2008R2 181.88 MB  Pholder\sqltest 9     May 10 2010 Status=ONLINE,... 100

name                       fileid filename                                    filegroup size      ...  usage
-------------------------  ------ ------------------------------------------  --------- --------       ------
AdventureWorks2008R2_Data  1      g:\sqldata\..\AdventureWorks2008R2_Data.mdf PRIMARY   184192 KB ...  data only
AdventureWorks2008R2_Log   2      g:\sqldata\..\AdventureWorks2008R2_Log.ldf  NULL      2048 KB ...    log only

Attaching a database

• Use sp_attach_db only if the database was previously detached with sp_detach_db, or copied.
• If you need to specify more than 16 files, use instead
• CREATE DATABASE dbname FOR ATTACH or
• CREATE DATABASE dbname FOR_ATTACH_REBUILD_LOG

(more information: here)