627
Chapter 23: Transferring Databases
23
If you want to reattach a database with code, the counterpart to sp_detach_db is the sp_
attach_db system stored procedure. Attaching a database requires specifying the fi les’
locations as well as the database name, as follows:EXEC sp_attach_db @dbname = 'AdventureWorks2012',
@filename1 = ' D:\MSSQL11.MSSQLSERVER\MSSQL\
Data\AdventureWorks2012_Data.mdf',
@filename2 = 'E:\MSSQL11.MSSQLSERVER\MSSQL\
LOG\AdventureWorks2012_Log.ldf'Best Practice
Use CREATE DATABASE database_name FOR ATTACH instead of sp_attach_dbstored procedure
because it will be removed in the future version of SQL Server. Following is an example:
CREATE DATABASE AdventureWorks2012 ON
(FILENAME = ' G:\MSSQL10.INST1\MSSQL\Data\AdventureWorks2012_
Data.mdf'),(FILENAME = 'H:\MSSQL10.INST1\MSSQL\LOG\AdventureWorks2012_Log.ldf')
FOR ATTACH;Special Instructions for Moving System Databases
Detaching and attaching systemdatabases result in error by default. To move system databases, you
must follow a strict set of procedures outlined at http://msdn.microsoft.com/en-us/library/
ms345408.aspx.Import and Export Wizard
On many occasions SQL DBAs need to do the following:■ (^) Copy only a few tables from one SQL Server database to another SQL Server
database.
■ (^) Import data from a fl at fi le or a Microsoft Offi ce Excel fi le.
■ (^) Copy data from one table to another with different collations.
To easily and quickly achieve these DBA tasks, Microsoft has provided another powerful
wizard: called SQL Server Import and Export Wizard. This wizard enables copying data to
c23.indd 627c23.indd 627 7/31/2012 9:25:22 AM7/31/2012 9:25:22 AM
http://www.it-ebooks.info