In SQL Server Management Studio, right-click a database in Object Explorer view, point to Tasks, and then select Copy Database.

When the Copy Database Wizard starts, click Next.

On the Select A Source Server page, specify the server that has the database you want to copy or move. Type the DNS or host name of the source server, such as CORPSVR09 (see Figure 7-7). Alternately, you can click the button to the right of the Source Server box to browse for available source servers.

Figure 7-7. The Select A Source Server page of the Copy Database Wizard

Windows Authentication is used by default, which means that your current login credentials are used to determine if you have appropriate permissions. If you want to use SQL Server Authentication, select SQL Server Authentication, and then enter your SQL Server login and password in the text boxes provided. Click Next.

On the Select A Destination Server page, specify the server to which you are copying or moving the selected database, and then specify the authentication technique to use. Click Next.

Note

SQL Server Agent must be running on the destination server.

Select the transfer method—either Use The Detach And Attach Method or Use The SQL Management Object method. If you choose to detach and attach the database, the source database is reattached automatically by default if failure occurs. To prevent this, clear the check box for the option If A Failure Occurs, Reattach The Database. Click Next.

As shown in Figure 7-8, you can now select the database you want to copy or move. Click Next.

Figure 7-8. The Select Databases page of the Copy Database Wizard

Use the Configure Destination Database page shown in Figure 7-9 to define the destination configuration of each database you are copying or moving, one at a time. Pay particular attention to the Source Database and Destination Database boxes. The Source Database box shows the current name of the database on the source. Use the Destination Database box to set the name that will be used on the destination server.

Figure 7-9. The Configure Destination page of the Copy Database Wizard

Any data and log files associated with the database are shown with their destination file name and folder. You can change the default locations by typing new values. If you are creating a copy of a database on the same source and destination instance, be sure to change the database name and file names.

If the destination database already exists, the default option is to stop the transfer. You can drop the existing database and force the transfer by selecting the Drop Any Database... option.

Click Next. If you are copying or moving multiple databases, you will see a Configure Destination Database page for each database.

When you have configured all destination databases, the next page you will see is the Configure The Package page. Set the package name and logging options you prefer, and then click Next.

You can run the wizard now or schedule the wizard to run at a later time. To run the wizard immediately and perform the copy/move operations, select Run Immediately. To schedule the wizard to run at a later time, select Schedule, and then click Change. You will then be able to schedule this task as a new job. See Chapter 15, for details on scheduling.

Click Next. Review your choices, and then click Finish. The wizard will perform the necessary tasks to prepare and create the copy/move package. If a critical error occurs during these tasks, the operation will fail, and you should view the report to determine what error occurred and then resolve it.

Get the logical name of the data and log files associated with the database by typing:

USE master

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID("Personnel");

Take the database you want to work with offline by typing:

ALTER DATABASE Personnel

SET offline

GO

Move one file at a time to the new location by typing:

ALTER DATABASE Personnel

MODIFY FILE ( NAME = Personnel_Data, FILENAME =
"C:DataPersonnel_Data.mdf")

GO

Repeat the previous step to move other data and log files.

Put the database online by typing:

ALTER DATABASE Personnel

SET online

GO

Take the database you want to work with offline by typing:

ALTER DATABASE database_name

SET offline

GO

Move one file at a time to the new location by typing:

ALTER DATABASE database_name

MODIFY FILE ( NAME = logical_name, FILENAME = "new_path".

GO

Repeat the previous step to move other full-text catalog files as necessary.

Put the database online by typing:

ALTER DATABASE database_name

SET online

GO

Get the logical name of the data and log files associated with tempdb by typing:

Use Master

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID("tempdb");

GO

Move each data and log file to a new location one at a time by typing:

USE master

GO

ALTER DATABASE tempdb 

MODIFY FILE (NAME = logical_name, FILENAME = "new_path/file_name")

GO

ALTER DATABASE  tempdb 

MODIFY FILE ( NAME = logical_name, FILENAME = "new_path/file_name")

GO

Repeat the previous step to move other data and log files as necessary.

Stop and restart SQL Server.

Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server, and then work your way down to the Databases folder.

Right-click the database you want to manage, and then select Properties from the shortcut menu to open the Database Properties dialog box.

Select the Files page from the Select A Page list in the Database Properties dialog box.

On the Files page, click Add to set a secondary data file. Then, in the Database Files area, type a new file name, such as Personnel_Data2 or Personnel_Log2.

Set the file type:

Set the initial size of the file, and then click the button to the right of the Autogrowth box. Then you can set Autogrowth options for the new data or log file.

Click the button to the right of the Path box to find a new path, or you can enter a new path directly. The file name is set based on the logical name and file type.

Click OK to make the changes.