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
ALTER DATABASE AdventureWorks2008R2
    SET single_user;
    --(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:
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
(more information: here)

No comments:

Post a Comment