Copy Databases from one SQL Server to Another

SQL Server provides you with several techniques you can use to copy a database from one server to another. For example, you can use the Data Transformation Services (DTS) wizards. Another technique you can use is to manually copy the database's files (including its transaction log) to another server, and then attaching the database to the server by using the sp_attach_db stored procedure. This stored procedure enables you to make a server's master database aware of a new database. The syntax is:

sp_attach_db 'logical_database_name', 'path\database_file_name'

For example, if you want to attach a database named "Sales" with a path and filename of C:\MSSQL7\DATA\sales_data.mdf to a server, you should type:sp_attach_db 'sales', 'C:\MSSQL7\DATA\sales_data.mdf'

Source: Rozanne Murphy Whalen
Viewed 6906 times