How to attach databases to MSSQL

  • Detaching and attaching databases is a good way of transporting data to another SQL Server instance or another SQL Server.
  • See: How to drop recalcitrant databases in MSSQL to detach a database.
  • Microsoft provides the sp_attach_db and sp_detach_db stored procedures to ease the process.
  • Make sure that you are signed on as the database owner.
  • After reattaching a database, you should:
    1. Verify that it's a valid database with dbcc checkdb.
    2. Update the statistics of the database with dbcc updateusage.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Attach database

  1. USE master;
  2. go
  3. EXEC SP_ATTACH_DB @dbname = 'sql911',
  4.    @filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\sql911.mdf',
  5.    @filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\sql911_log.ldf';
  6. go
Command(s) completed successfully.
  • Line 1: Always switch to the master database.
  • Line 4: it's the full physical path of the database file.
  • Line 5: it's the full physical path of the log file.
  • Make sure that both files are own by an administrator and that as the dba attaching the database is also in the administrators group.

Verify the databases

  1. DBCC CHECKDB ('sql911');
  2. go
DBCC results for 'sql911'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 595 rows in 6 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 88 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 99 rows in 1 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 595 rows in 5 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 88 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 99 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 120 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 65 rows in 1 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 484 rows in 8 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 202 rows in 3 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 310 rows in 3 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 200 rows in 30 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 14 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 133 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 111 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'EMP_WORK'.
There are 75 rows in 1 pages for object "EMP_WORK".
DBCC results for 'DEPARTMENT'.
There are 9 rows in 1 pages for object "DEPARTMENT".
DBCC results for 'ORG'.
There are 10 rows in 1 pages for object "ORG".
DBCC results for 'Nums'.
There are 1048577 rows in 1686 pages for object "Nums".
DBCC results for 'sales02'.
There are 32 rows in 3 pages for object "sales02".
DBCC results for 'trans01'.
There are 16 rows in 1 pages for object "trans01".
DBCC results for 'trans02'.
There are 13 rows in 1 pages for object "trans02".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'sales'.
There are 57 rows in 1 pages for object "sales".
DBCC results for 'projects'.
There are 20 rows in 1 pages for object "projects".
DBCC results for 'Emps'.
There are 32 rows in 1 pages for object "Emps".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'sql911'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The key part is the almost last line:

  • CHECKDB found 0 allocation errors and 0 consistency errors in database 'sql911'.

    It worked!
  • You should always run:
  1. DBCC updateusage ('sql911');
  2. go
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  • Are you sure that the statistics where up-to-date? This will make sure.