Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2009-02-16   |   Comments (4)   |   Related Tips: 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


Last Updated: 2009-02-16


next webcast button


next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, December 05, 2017 - 4:59:15 PM - DVP Rao Back To Top

 Is the process same for AG rpelicas ?

 

 


Wednesday, August 31, 2016 - 4:57:08 AM - John Back To Top

Excellent article. Very useful!

It's also a good idea to change the default database path for new databases creation. The default path is obtained from the Windows registry.

You can also change with T-SQL, for example, to set default destination to: D:\MSSQLData

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\MSSQLData'

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\MSSQLData'

GO

Extracted from: Mover base de datos sql server a otro disco

 


Sunday, September 23, 2012 - 8:34:42 AM - K. Brian Kelley Back To Top

db chaining is cross-database ownership chaining. If you're not familiar with it, do a search in Books Online. It basically allows ownership chaining across databases configured to do this. Master and TempDB have this turned on but other databases have it turned off by default. You can't turn off master and TempDB's setting, and you should only modify another DB's setting when you absolutely need it.


Wednesday, September 19, 2012 - 2:34:57 AM - Dinesh Back To Top

Is_db_chainging_on=0 mean ??

 


Learn more about SQL Server tools