Moving SQL Server Database Files

MEDITECH's Data Repository best practices recommend a specific disk drive configuration and corresponding SQL Server database file location, based on what a particular database's role is within the SQL Server instance. Over time, as databases grow, sometimes it's necessary to add new databases or add new database files on drives other than those originally used. Read on to learn how to manage your database files on a growing server, while keeping the database performance as efficient as possible.

When a Data Repository system is initially set up, whether by you or your managed system vendor, ideally it follows MEDITECH's best practices for hardware configuration. Part of that specification includes a specific drive configuration, as shown below. The approach pictured below recommends dedicated drives for specific purposes: the C: drive only for Windows, etc. The important notes for a DR sysem are a drive just for live and test db files (H: below), the L: drive for transaction log files, and the T: drive only for tempdb.

 

blog moving dbs 1

Often when we review the DR health status of our sites we find that through time the integrity of these allocations has become compromised. User databases or databases from other applications end up on the same drive as the live DR database(s), the transaction log files, or on a drive with tempdb. This approach can lead to drive space shortage, poor disk read and write performance, and cause problems for database administrators in charge of managing the system. The last thing you need is a DR server short on disk space, so assuming drive space is available (especially on SAN-based systems), you can correct any database location problems to bring your system back in line with the guidelines above.

A couple of common scenarios we see with our clients that tell us it's time to reorganize and move databases. When non-MEDITECH databases have been added to the system, they may be small relative to the live DR databases, but it's best to move them off the drive reserved for DR databases. The simplest approach is to allocate a new drive with a modest of amount of disk space, then move all user or third-party databases there. It's worth repeating that MEDITECH recommends NOT installing any other applications or databases, other than your own for report development, on the DR server.

The other situation is your live DR databases have outgrown the drive on which they were initially installed. In this case, if a new drive with more disk space is available, you can move some or all of the live and test databases there, then re-allocate the old drive for any remaining user databases. (You can also add secondary data files to an existing database, but that's the topic for another blog!)

In either situation, moving database files to a different drive is easily accomplished with SQL Server Management Studio.

Let’s do it

Assuming that you have available disk space, the process of moving data or log files is fairly straightforward. The steps look like this:

  1. Get current information about database file locations
  2. Back up the database
  3. Detach the database
  4. Copy the database file(s) to their new location
  5. Attach the database
  6. Delete the database file(s) from the original location
1: Get current information

It's a good idea to review or document the current file properties for the database before you make any changes. If you prefer the GUI, use Management Studio to review database files information by right-clicking on the database name and selecting "Properties." This way you can see where the database data and log files currently reside, along with the file sizes.

 blog moving dbs 2 blog moving dbs 3 

 

Alternately, you can query the system management views built-in to SQL Server and get info for all the databases on thse system - use the script below for an example.  

SELECT
   DB.name AS DatebaseName
   ,MF.name AS FileName
   ,MF.physical_name AS CurrentPath
FROM sys.databases DB
   INNER JOIN sys.master_files MF
   ON DB.database_id = MF.database_id
WHERE
   DB.name NOT IN ('master', 'model', 'msdb')
ORDER BY
   DB.name

blog moving dbs 4

2. Back up the database

It's just a precaution, but you can never have enough database backups, right? If you can, take a backup before you move the database. Or, if your databases are already backed up daily and your DBA owes you a favor, keep their phone on speed dial, just in case.

3. Detach the database

Detaching the database takes it offline and temporarily removes it from the SQL Server instance, but leaves all data and the file structures intact, so databases can be moved to other systems (or drives on the same system.)  To do this in Management Studio, once again right-click on the database name in the Object Explorer window (making sure there are no active connections first) and select "Tasks, Detach."

blog moving dbs 5

Or you can use T-SQL from a query window using the system procedure "sp_detach_db:"

EXEC sp_detach_db 'MyDatabase_Name', 'true';

 

4. Copy the database file(s) 

Now you can copy the database file or files to their new location. You can use Windows Explorer to do this, with one window open from the source, and another for the destination. You could move the files rather than copy them, but copying will give you some peace of mind in case your network connection hiccups during the operation. If you have server console access, you can also use the command-line (remember that?) with xcopy and switches to verify the operation if you prefer. If you have file management utility software you use already, by all means do so.

5. Attach the database

Once again, back to Management Studio, except this time right-click on the "Databases" folder, then select "Attach...". From the next window, click the "Add" button, then point to the .mdf (primary data file) for the database, and select "OK," then "OK" again. You'll see the database re-appear in the databases folder in the Object Explorer window.

blog moving dbs 6              blog moving dbs 7 
 
6. Delete the orginal database 

Just a bit of housekeeping, assuming you copied rather then moved the file. 

Some other items to consider: if you're moving a test or live DR database, you should stop all the related background transfer jobs first and wait for them to catch up before detaching the database. For any database you move, don't forget to check for active connections first, and either warn your users, or you can optionally drop their connections when you do the detach command.

 

Edited by Ian Proffer