Chapter 1 The Copy Database Wizard in SQL Server 2000
The Copy Database Wizard is a new utility in SQL Server 2000 that allows you to move or copy a database from SQL Server 7.0 or from SQL Server 2000 to SQL Server 2000. The copy or move process is relatively straightforward and it may seem like the process barely affects anything. This article provides information on how the Copy Database Wizard works and outlines some issues to look out for when you use the wizard.

1.1 How the Copy Database Wizard Operates

To open the Copy Database Wizard:

1. In the SQL Server Enterprise Manager, click Management, and then click Run a Wizard.

2. Right-click the Databases folder, click All Tasks, and then click Copy Database Wizard....

3. At a command prompt, type Cdw.exe and then press Enter.

The wizard detaches the databases that are to be copied or moved, copies the files associated with the database to the destination server and then attaches the database. The detaching and attaching of the databases is performed through the following stored procedures: sp_detach_db and sp_attach_db

The actual copying of the file occurs via XP_CMDSHELL which executes a command-line COPY command. This is why it is important that the account that runs the Copy Database Wizard is a memeber of sysadmin.

The wizard performs the following steps:

1. Creates a UNC share point on the source computer where the files are to be copied.

2. Checks for active connections in the source database.

3. Puts the database in the single user mode.

4. Detaches the source database.

5. Checks if the files exist on the destination and then copies the database files to the share point created earlier.

6. Attaches the database back on the source server.

7. Attaches the database on the destination server.

8. Removes the UNC share.

9. Puts the database back in its original mode (single-user, multi-user, and so forth).

Four additional features exist that allow you to move objects that are associated with the database that is being copied or moved.

1. Logins (recommended).

l All logins detected at package run-time.

l Only logins used by the selected databases.

2. Shared stored procedures from the master database (optional).

l All stored procedures detected at package run-time.

l User-selected stored procedures.

l Jobs from msdb (optional).

l All jobs detected at package run-time.

l User-selected jobs.

4. User-defined error messages (optional).

l All error messages detected at package run-time.

l User-selected error messages.

1.2 Wizard Creates a Data Transformation Services (DTS) Package

The Copy Database Wizard creates a DTS package that runs on the destination server and can be run either immediately or can be scheduled to run later. Alternatively, you can manually create a copy of the database DTS package by using the DTS Designer tool and clicking Transfer Database Task.

The package is saved if it is scheduled to run on a recurring basis or one time. Therefore, the SQL Server Agent on the destination server has to be started. The Transfer Database DTS package must be treated as any other DTS package including the requirements and permissions associated with scheduling and running the package.

1.3 Copy Database Wizard Log - DTS Package Log

To view the log, follow these steps:

1. On the destination server where the package is stored, open SEM and expand the Data Transformation Services folder.

2. Under Local Packages, locate the package for which you want to view the log. By default, the packages are named CDW_SourceServer_DestinationServer_autonumber.

3. Right-click the package and select the Package Logs option.

4. Under DTS Package versions and log tree, click the plus (+) sign to expand and view the list of logs.

This dialog box allows you to view the log(s) for this package or to select another package from the drop-down box. It also allows you to delete logs that you do not need by clicking the Delete button.

Alternatively, you can configure DTS Package to produce an output file for troublshooting purpose.

1. Right-click on the package, select Design Package.

2. From the Package menu, select Properties.

3. On the logging tab, specify the errr file.
This file will have detailed information on the executation of the package.

1.4 Be Aware that the Database Detaches

As mentioned earlier, the Copy Database Wizard detaches the source database before copying it to the destination server. Do not run the Copy Database Wizard if the database is suspect or if any other problems exist with the database that may prevent it from detaching or reattaching. Problems such as I/O errors (for example, error 823), data integrity errors (for example, Table Corrupt), or any known hardware issues (for example, SCSI port errors or controller errors in the system event log), to name a few, may be an impediment to a successful reattachment of the database.

Make sure that the source database is not marked 'read only', 'offline', 'suspect', 'emergency', or 'loading' or the detach will fail. The detach would fail because the detach procedure must update the statistics in the database; if the database is in any way not accessible for writes, the statistics update fails.

In addition, detaching the database requires that it be in single-user mode or have no users connected to it. If multiple users are using the database, the wizard may fail

1.5 Ensure Destination Directory Exists

When using the Copy Database Wizard (CDW), at the Database File Location screen, click the Modify button and confirm that the destination directory is correct. If the directory does not exist, the CDW may fail with the error.

1.6 The Move Option Removes the Database

Bear in mind that if you choose to move a database, rather than copying it, the database is removed from the source SQL Server server. More specifically, the database is detached from the source server but is not reattached. However, the original database files (.mdf, .ndf, .ldf) remain on the source server and if you do not need those because of space considerations, you have to manually delete them.

However, if you mistakenly select the MOVE option instead of the COPY option, you can safely reattach those files back to the server to recover the database.

1.7 Moving Logins

A new feature of the Copy Database Wizard alleviates the issue of manually moving logins. The wizard identifies the logins corresponding to the database users and provides you with the option to move or copy those as part of moving or copying the database. Both SQL Server Standard authentication logins and Microsoft Windows NT and Microsoft Windows 2000 authentication logins are moved by the wizard.

1.8 Database/Files Exist or There is Insufficient Disk Space

When transferring a database, the wizard detects whether or not the database exists on the destination server. The Select the Database to Move or Copy dialog box indicates that databases can be transferred with an OK status or that databases cannot be transferred either because the database Already Exists or is a System Database like the master database.
A database transfer can also be stopped if the same database files already exist on the destination server. So, you may have a database named MyPubs with file names equivalent to those of the Pubs database on the destination.

To avoid this error message, either modify the filenames to be created on the destination server or change the physical location into which those will be created.
A transfer can also be hampered by a lack of disk space on the destination server. Change drives or delete some files to allow for sufficient space.

1.9 Full-Text Search Stops. Full-Text Catalog Files Not Transferred

If the database contains any full-text catalogs there are two effects of using the Copy Database Wizard to transfer it. First, searching the Full-Text (FT) catalogs is stopped by the wizard so that the database can be put in single user mode. Second, the FT catalog files are not moved by the wizard. If you try to run a full text search query on the source or destination SQL server after the Copy Database Wizard is run, you may get the error message.

CHAPTER 2 Stop the transaction log of a SQL Server database from growing unexpectedly

The expansion of the transaction logs can make your Microsoft SQL Server database unusable. In SQL Server 2000, each database contains at least one data file and one transaction log file. SQL Server 2000 stores the data physically in the data file. The transaction log file stores the details of all the modifications that you perform on your SQL Server database and the details of the transactions that performed each modification. Because the transactional integrity is considered a fundamental and intrinsic characteristic of SQL Server, logging the details of the transactions cannot be turned off in SQL Server 2000.

2.1 The reason of the transaction log from growing unexpectedly

The transaction log file is logically divided into smaller segments that are referred to as virtual log files. In SQL Server 2000, you can configure the transaction log file to expand as needed. The transaction log expansion can be governed by the user or can be configured to use all the available disk space. Any modifications that SQL Server makes to the size of the transaction log file, such as truncating the transaction log files or growing the transaction log files, are performed in units of virtual log files.

If the transaction log file that corresponds to a SQL Server database is filled and if you have set the option for the transaction log files to grow automatically, the transaction log file grows in units of virtual log files. Sometimes, the transaction log file may become very large and you may run out of disk space. When a transaction log file grows until the log file uses all the available disk space and cannot expand any more, you can no longer perform any data modification operations on your database. Additionally, SQL Server may mark your database as suspect because of the lack of space for the transaction log expansion.

2.2 Reduce the size of the transaction logs

To recover from a situation where the transaction logs grow to an unacceptable limit, you must reduce the size of the transaction logs. To do this, you must truncate the inactive transactions in your transaction log and shrink the transaction log file.

Note The transaction logs are very important to maintain the transactional integrity of the database. Therefore, you must not delete the transaction log files even after you make a backup of your database and the transaction logs.

2.3 Truncate the inactive transactions in your transaction log

When the transaction logs grow to an unacceptable limit, you must immediately back up your transaction log file. While the backup of your transaction log files is created, SQL Server automatically truncates the inactive part of the transaction log. The inactive part of the transaction log file contains the completed transactions, and therefore, the transaction log file is no longer used by SQL Server during the recovery process. SQL Server reuses this truncated, inactive space in the transaction log instead of permitting the transaction log to continue to grow and to use more space.

2.4 Shrink the transaction log file

The backup operation or the Truncate method does not reduce the log file size. To reduce the size of the transaction log file, you must shrink the transaction log file. To shrink a transaction log file to the requested size and to remove the unused pages, you must use the DBCC SHRINKFILE operation. The DBCC SHRINKFILE Transact-SQL statement can only shrink the inactive part inside the log file.