Attaching and Detaching Databases Versus Backup and Restore

One feature introduced in SQL Server 7.0 was the ability to attach and detach a database. This is a very useful function that you can use in many scenarios, including upgrades and disaster recovery, but what exactly is the difference between detaching and attaching a database and using backup and restore? Both seemingly do the same things. However, in reality, they are different. Detaching and then attaching a database, notwithstanding the physical time it takes to copy the files, can be a relatively quick process in comparison to a straightforward backup and restore. That is one of the reasons many people like to use it as an option for their databases.

If you detach and then attach a database from the same SQL Server version (for example SQL Server 2000 to SQL Server 2000), nothing should really change. The on-page structure of the database will be intact after the move. You might notice performance issues if the server is not exactly the same (capacity, memory, RAID type on the disks). Why? Remember that your statistics, as well as execution plans, are based on a specific configuration. These are not changed or updated in the attach process to match the new server, so you need to ensure that after the database is attached, it performs and behaves as you expect it to.

If you use the detach and attach process or the Copy Database Wizard, which is based on that functionality, to upgrade from SQL Server 7.0 to SQL Server 2000, that is a fully supported method of migration. During the attach process, the database s metadata is upgraded to SQL Server 2000. However, the earlier caveat will become much more important: your statistics are not automatically rebuilt. Although SQL Server 2000 is built on the foundation started by SQL Server 7.0, the engine did change a bit in SQL Server 2000, so statistics that were generated under SQL Server 7.0 are invalid, meaning that the queries do not take advantage of the new optimizer. You should thus ensure that your execution plans and statistics are rebuilt based on the hardware you will now be running.

Take into account when detaching and attaching that your database will be unavailable during the entire process. You are literally detaching the file from SQL Server and can then make a copy, and so on, and then reattach the database after you have your copy. Once you have your copy, or if you plan on using that file and not making a backup, you can move it wherever you want as a function of time, bandwidth, and disk speed. The savings that you might gain in the attach process might therefore be negated by a copy time for a very large database over a WAN.

Tip

One of the keys to good performance with attaching and detaching (and subsequently the Copy Database Wizard) is to copy the files to a different set of disks. When copying from the same physical disk or stripe of disks to the same disk or stripe, you will spend most of your time seeking back and forth for the write location, and therefore your I/O performance will suffer a great deal.

Tip

Remember that you have the ability to use compression with a third-party tool to reduce the size of your files before you copy them over the network. You are in effect trading processor time on both ends for reduced network time in the middle. You also might want to try alternative copy programs such as xcopy or robocopy from the Windows 2000 Resource Kit if you have particularly large files to move around. Even if they do not move the data faster for your particular case, the ability to restart at the point at which you left off in the case of a disconnect or timeout can be invaluable.

Backup and restore was discussed in detail in Chapter 10. However, when comparing backup and restore to detaching and attaching a database, why should you consider it? First and foremost, it is tried and true (as is detach and attach at this point). Like the attach process, a restore upgrades a SQL Server 7.0 database when it is restored on an instance of SQL Server 2000. The main difference that you are likely to be concerned with is that statistics are rebuilt automatically during the restore process. All other things being equal, you have two excellent choices for ensuring your database is upgraded and restored on your SQL Server 2000 instance.

Note

Keep in mind that server capacity and configuration on a different server affect your database, whether as the result of an attach or a restore. For example, SQL Server will keep the on-page structures, but if it is on a different disk subsystem (say, RAID 5 versus a striped mirror), your data access might have better (or possibly worse ) performance.