Provisioning databases

Moving and removing databases

In this section, we review the steps and options to moving databases and the various methods and stages of removing databases from use.

Moving user and system databases

In this section, we discuss moving database files, which becomes necessary from time to time, either because of improper initial locations or the addition of new storage volumes to a server. Relocating system and user databases is similar to each other, with the master database being an exception. Let’s look at each scenario.

Locating SQL Server files

As we discussed in our earlier checklist, you can review the location of all database files by querying the catalog view sys.master_files. If you did not specify the intended location for the data files while you were on the Data Directories page of the Database Engine Configuration step of SQL Server Setup, you will find your system database files on the OS volume at %programfiles%\Microsoft SQL Server\instance\MSSQL\Data.

Ideally, there should be no data or log files on the OS volume, even system database files. You can move these after SQL Server Setup is complete, however.

When you’re planning to move your database data or log files, prepare their new file path location by granting FULL CONTROL permissions to the per-SID name for the SQL Server instance. (Note that this is not necessarily the SQL Server service account.) For the default instance, this will be NT SERVICE\MSSQLSERVER; for default instances, it will be NT SERVICE\MSSQL$instancename.

Database actions: offline versus detach versus drop

Earlier in this chapter, we discussed strategies to move user database files by using the OFFLINE status. Let’s discuss the differences between various ways to remove a database from a SQL Server instance.

The OFFLINE option is one way to quickly remove a database from usability. It is also the most easily reversed, as demonstrated here:

SET ONLINE;

You should set maintenance activities to ignore databases that are offline because they cannot be accessed, maintained, or backed up. The data and log files remain in place in their location on the drive and can be moved. The database is still listed with its files in sys.master_files.

Taking a database offline is an excellent intermediate administrative step before you DETACH or DROP a database; for example, a database that is not believed to be used any more. Should a user report that she can no longer access the database, the administrator can simply bring the database back online—an immediate action.

You can separate a database’s files from the SQL Server by using a DETACH. The data and log files remain in place in their location on the drive and can be moved. But detaching a database removes it from sys.master_files.

To reattach the database, in SQL Server Management Studio, in Object Explorer, follow the Attach steps. It is not as immediate an action and requires more administrative intervention than taking the database offline.

When reattaching the database, you must locate at least the primary data file for the database. The Attach process will then attempt to reassociate all the database files to SQL Server control, in their same locations. If their locations have changed, you must provide a list of all database files and their new locations.

Finally, a DROP DATABASE command, issued when you use the Delete feature of Object Explorer, removes the database from the SQL Server and deletes the database files on the drive. An exception to the delete files on drive behavior is if the destination database is offline. Deleting an offline database and detaching a database are therefore similar actions.

Dropping a database does not by default remove its backup and restore history from the msdb database, though there is a check box at the bottom of the Drop Database dialog box in SQL Server Management Studio that you can select for this action. The stored procedure msdb.dbo.sp_delete_database_backuphistory is run to remove this history. For databases with a long backup history that has not been maintained by a log history retention policy, the step to delete this history can take a long time and could cause SQL Server Management Studio to stop responding. Instead, delete old backup and restore history incrementally by using msdb.dbo.sp_delete_backuphistory and/or run the msdb.dbo.sp_delete_database_backuphistory procedure in a new SQL Server Management Studio query window.

For more information on backup and restore history, see Chapter 13.

Moving user database files

You can move user databases without a SQL Server instance restart and without disrupting other databases by taking the database offline, updating the files, moving them, and then bringing the database online again.

Use the following steps to move user database files:

1. Perform a manual full backup of the soon-to-be affected databases.

2. During a maintenance outage for the database and any applications that are dependent, begin by taking the user database offline and then running a T-SQL script to alter the location of each database file.

3. Here’s an example of the T-SQL statements required:

   ALTER DATABASE database_name SET OFFLINE WITH ROLLBACK IMMEDIATE
   ALTER DATABASE database_name MODIFY FILE ( NAME = logical_data_file_name,
   FILENAME = 'location\physical_data_file_name.mdf' );
   ALTER DATABASE database_name MODIFY FILE ( NAME = logical_log_file_name,
   FILENAME = 'location\physical_log_file_name.ldf' );
   ALTER DATABASE database_name SET ONLINE

4. While the database is offline, physically copy the database files to their new location. (You will delete the old copies when you’ve confirmed the new configuration.) When the file operation is complete, bring the database back online.

5. Verify that the data files have been moved by querying sys.master_files, which is a catalog view that returns all files for all databases. Look for the physical_name volume to reflect the new location correctly.

6. After you have verified that SQL Server is recognizing the database files in their new locations, delete the files in the original location to reclaim the drive space.

7. After you have successfully moved the database files, you should perform a manual backup of the master database.

Moving system database files, except for master

You cannot move system database files while the SQL Server instance is online; thus, you must stop the SQL Server service.

1. Begin by performing a manual full backup of the soon-to-be affected databases.

2. For model, msdb, and TempDB, begin by running a T-SQL script (similar to the script for moving user databases). SQL Server will not use the new locations of the system databases until the next time the service is restarted. You cannot set the system databases to offline.

3. During a maintenance outage for the SQL Server instance, stop the SQL Server instance, and then copy the database files to their new location. (You will delete the old copies when you’ve confirmed the new configuration.) The only exception here is that the TempDB data and log files do not need to be moved—they will be re-created automatically by SQL Server upon service start.

4. When the file operation is complete, start the SQL Server service again.

5. Verify that the data files have been moved by querying sys.master_files. Look for the physical_name volume to reflect the new location correctly.

6. After you have verified that SQL Server is recognizing the database files in their new locations, delete the files in the original location to reclaim the drive space.

7. After you have successfully moved the database files, perform a manual backup of the master database.

If you encounter problems starting SQL Server after moving system databases to another volume—for example if the SQL Server service account starts and then stops—check for the following:

1. Verify that the SQL Server service account and SQL Server Agent service account have permissions to the new folders location. Review the following link for a list of File System Permissions Granted to SQL Server service accounts: https://docs.microsoft.com/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions#Reviewing_ACLs

2. Check the Windows Application Event Log and System Event Log for errors.

3. If you cannot resolve the issue, if necessary, start SQL Server with Trace Flag T3608, which does not start the SQL Server fully, only the master database. You then can move all other database files, including the other system databases, back to their original location by using T-SQL commands issued through SQL Server Management Studio.

Moving master database files

Moving the master database files is not difficult, but it is a more complicated process than that for the other system databases. Instead of issuing an ALTER DATABASE … ALTER FILE statement, you must edit the parameters passed to the SQL Server service in SQL Server Configuration Manager.

1. On the Startup Parameters page, notice that there are three entries containing three files in their current paths. (If you have other startup parameters in this box, do not modify them now.)

   Edit the two parameters beginning with -d and -l (lowercase “L”). The -e parameter is the location of the SQL Server Error Log; you might want to move that, as well.

   After editing the master database data file (-d) and the master database log file (-l) locations, click OK. Keep in mind that the SQL Server service will not look for the files in their new location until the service is restarted.

2. Stop the SQL Server service, and then copy the master database data and log files to their new location. (You will delete the old copies when you’ve confirmed the new configuration.)

3. When the file operation is complete, start the SQL Server service again.

4. Verify that the data files have been moved by querying sys.master_files, a dynamic management view that returns all files for all databases. Look for the physical_name volume to reflect the new location correctly.

5. After you have verified that SQL Server is recognizing the database files in their new locations, delete the files in the original location to reclaim the drive space.

Single-user mode

By default, all databases are in MULTI_USER mode. Sometimes, it is necessary to gain exclusive access to a database with a single connection, typically in SQLCMD or in a SQL Server Management Studio query window.

For example, when performing a restore, the connection must have exclusive access to the database. By default, the restore will wait until it gains exclusive access. You could attempt to discontinue all connections, but there is a much easier way: setting a database to SINGLE_USER mode removes all other connections but your own.

Setting a database to SINGLE_USER mode also requires exclusive access. If other users are connected to the database, running the following statement will be unsuccessful:

ALTER DATABASE database_name SET SINGLE_USER;

It is then necessary to provide further syntax to decide how to treat other connections to the database.

• WITH NO_WAIT. The ALTER DATABASE command will fail if it cannot gain exclusive access to the database It is important to note that without this statement or any other WITH commands, the ALTER DATABASE command will wait indefinitely.

• WITH ROLLBACK IMMEDIATE. Rollback all conflicting requests, ending other SQL Server Management Studio Query window connections, for example.

• WITH ROLLBACK AFTER n SECONDS. Delays the effect of WITH ROLLBACK IMMEDIATE by n SECONDS, which is not particularly more graceful to competing user connections, just delayed.

For example:

ALTER DATABASE databasename
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Instead of issuing a WITH ROLLBACK, you might choose to identify other sessions connected to the destination database; for example, by using the following:

SELECT *
FROM sys.dm_exec_sessions
WHERE
db_name(database_id) = ‘database_name’;

And then evaluate the appropriate strategy for dealing with any requests coming from that session, including communication with that user and closing of unused connections to that database in dialog boxes, SQL Server Management Studio query windows, or user applications.

After you have completed the activities that required exclusive access, set the database back to MULTI_USER mode:

ALTER DATABASE database_name SET MULTI_USER;

You need to gain exclusive access to databases prior to a restore. This script to change the database to SINGLE_USER and back to MULTI_USER is a common step wrapped around a database restore.

For more information on database restores, see Chapter 11.