This guide is written for SQL Server 2008 R2 and has not been tested on newer/older versions. While I believe the process is broadly the same, please make sure you check in a development environment before you follow this guide for other versions of SQL.
This guide will show you how to change the physical file locations for SQL databases (excluding system databases like master or model). This may be required if faster storage become available or you need to reconfigure your current SQL storage and need to move the files away from it temporarily.
This guide is not intended to be used to move SQL databases to a different server nor should it be used used to move SQL databases to a new instance. It is simply to change the location of back-end SQL database files without changing the SQL instance/SQL server.
The amount of time each database will be unavailable varies by the size of the database and how quickly you can move it from its current location to its new location. A general rule for your first time following this guide is it will take 30 minutes plus 15 minutes per database – plus an additional 1 minute per 0.5GB of database file(s) that you are moving.
So if you’re moving 5 databases that total 5GB, you are looking at 1 hour 55 minutes of work and potential disruption (30 minutes + 1 hour 15 minues + 10 minutes).
1) Any changes on a live environment should be performed out of hours with the relevant people informed well ahead of time. While the database files are being moved, that SQL database will not be available for use.
2) At this point, make sure you have a backup of the relevant SQL databases and a backup of the SQL server.
3) Stop any services/programs that are using the SQL databases that you are planning to move. These services and/or programs may be running on different servers or on the SQL server itself. Either way, you need to make sure they are stopped so that the SQL database(s) can be moved.
For example, if one of the databases you are moving is a SharePoint database, you will have to stop the relevant SharePoint and Web services that may be accessing the SQL database (these services will probably be located on different servers, but still must be stopped).
Moving a Database
1) Open up SQL Management Studio and find the database that you’d like to move. At the same time, open up the current location of both the database file (.mdf file) and the log file (.ldf file) in Windows Explorer and make sure the new location(s) is/are ready for the files to be moved into. Current file locations are available via right-clicking the target database, choosing properties and opening “Files” on the left-hand side (you may need to scroll right to see the physical location, depending on your screen resolution) – you can then navigate to this path via Windows Explorer.
*** HERE BE DRAGONS ***
At this point you haven’t actually done anything to the SQL database, it should be available as normal. If you’re worried or not confident about proceeding, please stop this guide now and seek further advice. Otherwise, let’s get some SQL files moved.
2) In SQL Management Studio, select the database that you want to move the files for, right-click on it, hover over Tasks and select Detach. If the Status shows as “Ready”, then continue onto step 3). If the Status shows as “Not ready”, then look at the Message section on the right for an indication of the problem.
A common issue you may see in the Message section is “x Active Connections” (where “x” is an integer). To resolve this, make sure that all services/users/programs that talk to this SQL database are stopped or suspended. If you’re still getting this message but are sure that nothing is accessing the database, then tick the box under the Drop Connections section and carry on to step 3).
3) Hit “OK” and the database will be detached from the SQL instance.
4) Move the .mdf file and the .ldf file to their new location(s) via Windows Explorer or using a command-line tool. This can take some time depending on the size of the database files and how quickly these files can be moved.
5) Once you’ve moved the two files, head back into SQL Management Studio and right-click the Databases container on the left-hand side. Choose “Attach”. Find the location that you have moved the .mdf file to. If the .ldf file is in the same place (not recommended) then continue to step 6). If the .ldf file is in a different place, then glance down to the bottom of the window and find the line with the File Type entry of “Log”. Now head over to the right and click the … (three dots) to choose the correct location of the .ldf file. Once you’ve checked the paths are OK, continue to step 6).
6) Hit OK and the database should re-attach into the instance. The re-attached database should become available straight away. Test this by looking in SQL Management Studio for the newly reconnected database (use Refresh if it doesn’t appear) and check it appears OK with the correct file paths (right-click the ree-attached database, choose properties and open “Files” on the left-hand side to confirm the paths are correct). Once you’ve tested this in SQL Management Studio you can then try the application(s) that access this SQL database (start the appropriate services and programs that you may have stopped) and make sure that everything functions as before (if you’ve moved to quicker storage you should find things are faster).
7) Lastly, find the old .mdf and old .ldf files and either archive them somewhere or rename them – when you do this you shouldn’t get any file locked errors. If you do, check through the steps above to make sure you moved both the .mdf and .ldf files correctly. I tend to keep these old files on hand for a short while (a week or so) just in case any issues occur, after this you can safely delete them.
It’s as simple as that. Any problems, please get in touch with us and we’ll be able to assist..