How to move a SQL Server database within the same instance without losing settings

By: K. Brian Kelley   |   Updated: 2009-02-16   |   Comments (4)   |   Related: More > Database Administration

Problem

I want to move my database on my SQL Server 2005 system to a new drive, but I have database options, like cross-database ownership chaining, that I don't want to lose or have to remember to re-apply. How can I do this?

Solution

There are several solutions for moving database files around. One of the holdovers from SQL Server 7.0 and 2000 is to detach the database using sp_detach_db. The problem with detaching a database is SQL Server loses database properties like cross database ownership chaining because these aren't stored in the database itself, but rather in the metadata contained in the master database.

As a result, when you detach a database, you'll have to remember to re-apply those settings when re-attaching the database. And that's the only solution when moving files from one server to another, to determine what those settings are and re-apply them on the new server. However, if you're just moving files around on the same SQL Server instance and you're running SQL Server 2005 or above, you can take advantage of several ALTER DATABASE commands to preserve your settings and accomplish the move.

First, let's set up a test database to show what happens to the settings when we use sp_detach_db. The path setup is what I've created for testing. You'll need to modify the path to what's correct for your test system. After the database is created, we'll query to see if cross database ownership chaining is on. We should get a value of 0, indicating that it is not on.

CREATE DATABASE TestMove
ON (NAME='TestMove', FILENAME='C:\Temp\Dir1\TestMove.mdf')
LOG ON (NAME='TestMove_log', FILENAME = 'C:\Temp\Dir1\TestMove_log.ldf');
GO

SELECT is_db_chaining_on FROM sys.databases WHERE NAME = 'TestMove'; 
GO 

Let's turn cross database ownership chaining on and verify:

ALTER DATABASE TestMove 
SET DB_CHAINING ON; 
GO 

SELECT is_db_chaining_on FROM sys.databases WHERE NAME = 'TestMove'; 
GO 

We should now get a value of 1 to show cross database ownership chaining is on.

Now, if we detach and attach the database (sp_attach_db is deprecated, hence the use of CREATE DATABASE with the FOR ATTACH option), we should be able to show that the cross database ownership setting has been lost.

EXEC sys.sp_detach_db @dbname = 'TestMove', @skipchecks = N'TRUE', @keepfulltextindexfile = N'TRUE'; 
GO 

CREATE DATABASE TestMove 
ON (NAME='TestMove', FILENAME = 'C:\Temp\Dir1\TestMove.mdf'), 
(NAME='TestMove_log', FILENAME = 'C:\Temp\Dir1\TestMove_log.ldf') 
FOR ATTACH; 
GO 

SELECT is_db_chaining_on FROM sys.databases WHERE NAME = 'TestMove'; 
GO 

With a value of 0, we can confirm cross database ownership chaining is off.

Let's turn it back on, and then we'll use ALTER DATABASE to move the files without losing the settings.

First, to turn cross database ownership chaining  back on.

ALTER DATABASE TestMove 
SET DB_CHAINING ON; 
GO 

SELECT is_db_chaining_on FROM sys.databases WHERE NAME = 'TestMove'; 
GO 

And once we've verified the value is 1, indicating ownership chaining is on, the next step is to take the database off-line. This will prevent access to the database as we move the files. SQL Server will close the files, allowing us to move them:

ALTER DATABASE TestMove 
SET OFFLINE; 
GO

Once the database is offline, move the files from the current folder to the new folder you've set up for the test. For our test we are moving the MDF and LDF files from "C:\Temp\Dir1\" to "C:\Temp\Dir2\"

Issue the ALTER DATABASE commands to tell SQL Server the new file paths:

ALTER DATABASE TestMove 
MODIFY FILE (NAME='TestMove', FILENAME='C:\Temp\Dir2\TestMove.mdf'); 
GO 

ALTER DATABASE TestMove 
MODIFY FILE (NAME='TestMove_log', FILENAME='C:\Temp\Dir2\TestMove_log.ldf'); 
GO 

And with the files moved and SQL Server re-pointed, we can bring the database online again.

ALTER DATABASE TestMove 
SET ONLINE; 
GO 

And with the database on-line again, if we query for cross database ownership chaining, we'll see that the setting has been preserved through the file move when a value of 1 is returned:

SELECT is_db_chaining_on FROM sys.databases WHERE NAME = 'TestMove'; 
GO

Therefore, the steps to move files around on the same database (assuming SQL Server 2005 or 2008) is:

1. Issue an ALTER DATABASE command using the SET OFFLINE option to take the database off-line.
2. Move the files to their new locations.
3. Issue the appropriate ALTER DATABASE commands using the MODIFY FILE option to tell SQL Server where the new locations are.
4. Issue an ALTER DATABASE command using the SET ONLINE option to bring the database back on-line.

Next Steps

• Next time you need to move database files to different locations for the same instance of SQL Server don't forget to try this process

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips