You are the systems administrator for your company. The network consists of a Windows Essential Business Server 2008 environment. The network contains two instances of SQL Server 2008 named SQLl and SQL2. SQLl contains a database named Sales that has 20 data files.
You want to move the Sales database to SQL2 by using the detach and attach method. You detach the Sales database from SQLl by using SQL Server Management Studio. You want to attach the Sales database to SQL2.
What should you do?
A.
Use the sp_attach_db stored procedure.
B.
Use the CREATE DATABASE Transact-SQL statement.
C.
Use the sp_attach_single_file_db stored procedure.
D.
Use the ALTER DATABASE Transact-SQL statement.
Explanation:
You should use the CREATE DATABASE Transact-SQL statement. SQL Server 2008 allows you to move a detached database to another location and re-attach the database to the same instance or a different instance of SQL Server 2008. To move a database by using detach and attach, you should first detach the database, move the detached database file or files along with the log file to the new location, and then attach the copied files by using the CREATE DATABASE database_name FOR ATTACH Transact-SQL statement or the CREATE DATABASE database_name FOR_ATTACH_REBUILD_LOG Transact-SQL statement.You should not use the sp_attach_db stored procedure. The sp_attach_db stored procedure should only be executed on databases that were previously detached from the database server by using an explicit sp_detach_db operation or on copied databases. If you have to specify more than 16 files, you should use the CREATE DATABASE database_name FOR ATTACH Transact-SQL statement or the CREATE DATABASE database_name FOR_ATTACH_REBUILD_LOG Transact-SQL statement.
You should not use the sp_attach_single_file_db stored procedure because this stored procedure can be used to attach a database that has only one data file to the current server. The sp_attach_single_file_db stored procedure cannot be used with multiple data files.