Have you ever had to move databases and their files? A database migration involves some downtime and mistakes made can certainly ruin your day. You don’t want to be in the middle of a migration and be uncertain about what to do.

Read on to learn how to move SQL Server database files and also see some demos.

Why Move Database Files

Why would we need / want to move database files? There are some scenarios where a migration is done.

• Disk is out of space
  • Sometimes when an admin is sleeping on the job full disks go unnoticed until it it too late.
  • In these cases some databases can be moved to a different disk, volume, mount point, etc.
• Moving to new storage
  • New storage for the database comes in and we need to move some (or all) databases need to be moved.
• Separate database files
  • Following best practices to separate database files – data file (mdf), transaction log (ldf), tempdb, backups, trace files, etc.
  • I wrote about this here and more specifically here under the section called “Disk Setup”.

Pre Move Actions

The database will be unavailable during this operation so we need to notify our end users. Consider the ramifications if an application is using the database – we might want to stop application services or take some other custom action during the move.

Plan ahead before starting the job. Know what you are going to do before doing it. If you can test your method against a lab or development database that will help too.

Methods to Move Database Files

Once we have the game plan we can start to implement it. Let’s look at some details.

Migration – General Ways to Move Database Files

There are a few methods we can employ to get this job done:

1. Detach Database / Move Files / Attach Database
2. Set Database Offline / Move Files / Alter DATABASE MODIFY FILE / Set Online
3. Backup / Restore Elsewhere

Here are some Microsoft KBs to help along the process.

1. Attach a Database – explains CREATE DATABASE FOR ATTACH
2. sp_attach_db – deprecated! Please use the above method for attach
3. Create Database – more on the FOR ATTACH clause
4. Move User Databases – highlights ALTER DATABASE MODIFY FILE
5. sp_detach_db – caveats about detach

Let’s look in depth at each way.

Method 1: Detach Database / Move Files / Create Database For Attach

We create a few databases to test it out.

Verify DB file info.

It will look like this:

Now we do some preliminary things before moving files. We need to specify which databases we want to move. This is a very simplistic way to do it – hard coded in a temp table.

Now that we have what we need we can start to detach the databases.

Verify the changes. Notice that the DBs are totally gone. You won’t find them in the Object Explorer in SSMS or in sys tables.

Now comes the time for moving the database files. You can use your favorite technique for moving files. I like to use robocopy in a .bat file but there are many others such as PowerShell, XCopy, or right click cut / paste.

Here is some robocopy action:

The “/L” flag is how I first run things. It won’t actually copy anything but rather will list the files. It is a good pre-check before running a batch. Just remove the “/L” flag and run once verified.

Here is the first part of output from running a .bat file with robocopy statements:

Once the files are physically moved to the new location we can continue.

Verify the new file locations.

Our files used to be pointing to C: but are now on G: – the migration is complete.

Method 2: Set Database Offline / Move Files / Alter Database / Set Online

Continuing along, let’s move the 3 example databases we created and just moved back from the G: to C: drives. Here we start by defining our databases and setting a new path.

Here is the output from #DBFiles:

Now, instead of detaching like the previous example, let us simply take the databases offline.

The databases are still present but are offline. They are not able to be read or written to.

Subsequent to setting the databases offline move the data files to their new location.

Lastly we alter the databases to modify their files, point them to the new location, and then bring the databases back online.

Here is the output from the verification above. It shows the DB files in a new location and online.

Method 3: Backup / Detach and Delete / Restore Elsewhere

The backup will be like any other.

At this point we are free to detach the databases and dispose of the database files. It might be good to hold onto the files until after we have verified the restore worked. Since this is a contrived example let’s move on to the restore.

Plug in the new path for the MOVE TO clause and the restore will put the files there.

Post Move Actions

After migrating some databases we want to be confident that all is well and the system is up and running. A few simple checks help put your stakeholders at ease.

It is good to do the following things after migrating database files:

1. Check sys.database_files to make sure they are pointed to the right place
2. Run DBCC CHECKDB on the moved databases
   1. Moving database files can introduce concerns that a quick consistency check can alleviate

Final Thoughts

There are pros and cons to each of the methods presented above to move database files. Make sure that whatever technique you choose to employ that you first test it on a non-production server. This is not the point you want to encounter unknown surprises.

The safest method is probably #2 – setting offline, move files, alter modify file, set online. Detaching runs the risk of allowing the files to be deleted but is that is what you want then detach is the way. The backup method is probably best kept for the warm / cold standby but could work depending on the system usage and constraints.

 

---