In this article I will be discussing the moving of database files within a SQL Server instance. We'll also work through a typical move scenario, looking at the scripts we should use and the meta data available to help us.

Let's first begin with what we do know.

Under SQL Server 2000, altering database paths was all but limited to the TempDB database. You had to detach the user database and then re-attach it, or you could use backup, then restore. Hmm, messy to say the least. 

Under normal operation, once SQL Server has a handle on a database you will not be able to manipulate the files at the OS level. Any attempt to copy the files, etc., will result in the dialog box below. 

To address the first point, thankfully in SQL Server 2005 onwards, this is no longer necessary, and in fact SP_ATTACH_DB has been deprecated and will be removed in a future version of SQL Server. You should now use:

CREATE DATABASE ... FOR ATTACH

To address the second point, in order to release the handle the database engine has on the user database files we merely need to Offline the database. We do not need to stop the SQL Server services.

Let's just re-cap that; we do not need to stop the SQL Server services.

You may issue the following command to Offline the database;

ALTER DATABASE [yourDB] SET OFFLINE

If you have active connections and wish to roll them back and take the database offline you may do so using;

ALTER DATABASE [yourDB] SET OFFLINE WITH ROLLBACK IMMEDIATE

Once the database is Offline you may move and\or rename your database files. Just remember that if you delete or rename the files, the database will not come back online again. When attempting to Online the database you will usually receive an error along the lines of: 

You must first amend the system catalogs to provide the new paths\filenames, this is done using the ALTER DATABASE command passing in the MODIFY FILE parameters as shown in the following query construct:

ALTER DATABASE [yourDB]
  MODIFY FILE ( name=logicalfilename, 
                filename=N'c:\folder1\folder2\adbfile.mdf'
              )

Important Note: When using the T-SQL command above, SQL Server will accept whatever you type and issue in the ALTER DATABASE statement so be careful and check your typing!

For example this would be valid

ALTER DATABASE [yourDB] MODIFY FILE(name=logicalfilename, 

filename=N'c:\MSSQL\DATA\gobbeldygook.dat')

If the path\filename does not exist when the database tries to start you will receive an error!!

Moving the Files

With the above in mind, let's look at how we would achieve the goal of moving a databases disk files to new locations.

Our Scenario

The server drives are filling up quickly and you have been asked by the manager to move the disk files to a new set of drives provided by the Windows administrator. The engineer has created your new file paths for you and retained all NTFS ACLs required for the SQL Server services. The drives\paths supplied are as follows (I am using my C drive but this could easily be G or F or some other drive letter);

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBA\MSSQL\Data

Before making any changes to the OS files and their locations first check the metadata available to you in the following system catalog master.sys.master_files. The important metadata to collect consists of the Logical Filenames and the Physical Names. You may obtain this information using the following query;

SELECT database_id,
       name, 
       physical_name 
 FROM sys.master_files 
 WHERE database_id = DB_ID('SampleServiceCompany2009')

For my database I have the following: 

I need to amend these paths from the "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data" directory to match the new location provided to me. The first task is to construct a set of T-SQL move commands. The ALTER DATABASE ... MODIFY FILE command really only needs 2 parameters for the file move: the files logical name and the physical_name. Both of these are retrieved in the query shown above. The move commands are extremely simple and as shown earlier take the following form; 

Issuing these commands now will update the system catalogs, however, the new paths will not take effect until the database has been restarted (or taken Offline then brought back Online). The following message informs us of this: 

I'll now take the database Offline using the command highlighted below. 

With the database Offline, I may now move the files(and even rename them if I really wanted to).

A word of caution here. It is advisable to copy and paste the files to the new locations. Only when the database comes online successfully would I then remove the old files.

Once you have copied the files to the new locations you would then bring the database Online, this is done using:

ALTER DATABASE [yourDB] SET ONLINE

Shown below are the typical screenshots you will see if the database fails to start. From the information dialog below click the message column and you will see details of the issue. 

The error details show below provide an indication to the issue, the files probably do not exist (in fact that's generally exactly what an OS Error 2 means). 

If the database starts successfully you will see the following 

Help, My Database Won't Start

In every scenario I have encountered whereby a database file move has failed, the issue has been down to a mistyped path and\or filename, resulting in the DBA then getting into a vicious loop trying to correct themselves. Should your database fail to start, don't panic. Perform the following tasks;

• Check the script you used to modify the database file locations, have you got the paths and filenames correct?
• Have your admin check the permissions to the new path for the SQL Server Database Engine service account.
• Query the catalog master.sys.master_files, do the paths\filenames here match what you're expecting?
• If you are unable to complete the move successfully, revert back to the original file paths\names. As you left the files in the source directory simply issue the appropriate ALTER DATABASE ... MODIFY FILE statements and bring the database back online.

Querying master.sys.master_files to obtain the current database file paths 

This is a very easy task to complete providing you pay full attention to the task in hand. If you encounter an error, stop and review what you currently have in place both from the system catalog metadata and the physical OS locations\filenames.

As always, enjoy and if you're still stuck post back and I'll help all I can

It's quite possible that at some point you may want to have the use of a corrupted SQL Server database for test or DR practice purposes. In fact, this can also aid in the recovery steps for a corrupted database once the file and object architectures are fully understood.
Don't ever be tempted to just go run the "Repair_allow_data_loss" clause of DBCC CHECKDB, it does what it says on the tin. I was recently involved on a forum post where a user had done exactly that, without understanding the ramifications or understanding the output of DBCC CHECKDB.

To understand more we will create and corrupt our own test database, this is very easy to achieve as I will detail below. 

For this exercise we merely need a Hex editor and the use of a SQL Server instance.

Note: do not use a Production SQL Server instance!

I have chosen XVI32 as this editor is free of charge and requires no installation to take place, simply place the files into a folder and create a shortcut to the program.

The core database will be created using the following simple script. We’ll go through the process in stages with diagrams to see exactly what’s happening. Start with the code below;

Don't forget to modify any drive letters and paths before executing the script ;-)

USE [master]

CREATE DATABASE [Corrupt2K8] ON PRIMARY

( NAME =N'Corrupt2K8', FILENAME=N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Corrupt2K8.mdf',

SIZE = 524288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'Corrupt2K8_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Corrupt2K8_log.ldf',

SIZE = 262144KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)

GO

USE [Corrupt2K8]

GO

CREATE TABLE dbo.NoddyTable(

NoddyID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()

, NoddyName VARCHAR(128) NULL

, NoddyInt BIGINT NULL

, NoddyDate DATETIME NULL

) 

GO

INSERT INTO dbo.NoddyTable

SELECT NEWID(), name, ROUND(RAND(object_id)*856542, 0), GETDATE() FROM sys.columns

UNION ALL

SELECT NEWID(), name, ROUND(RAND(object_id)* 1048576, 0), GETDATE() FROM sys.columns

ALTER TABLE dbo.NoddyTable ADD CONSTRAINT PK_NoddyID

PRIMARY KEY CLUSTERED (NoddyID)

WITH (IGNORE_DUP_KEY=OFF)

CREATE NONCLUSTERED INDEX IDX_NoddyName_NoddyDate

ON dbo.NoddyTable(NoddyName, NoddyDate)

WHERE NoddyName IN ('password','length','created','crtype','offset','intprop')

CREATE NONCLUSTERED INDEX IDX_NoddyDate

ON dbo.NoddyTable(NoddyDate)

Once you have the database created, take a full backup followed by a differential and then a transaction log backup, you may then use these in future testing scenarios.

We now want to choose an object as the target of our corruption exercise. I am going to choose a non clustered index on the table 'dbo.NoddyTable', to get a list of indexes on this table use the following query;

SELECT OBJECT_NAME(object_id), name, index_id, type_desc FROM sys.indexes

ORDER BY 1

I will be using the non-clustered index 'IDX_NoddyDate', this has an index id of 3. To find details of the page numbers in use by this index we now need to turn to an undocumented DBCC command 'DBCC IND'. Full details of how to use this command may be found at the links below but basically this is used as follows;

DBCC IND (DatabaseName,'tablename', index_id)

So, I have

DBCC IND (Corrupt2K8,'dbo.NoddyTable', 3)

Below is the output from the command above

I'm going to pick a page of page type 2 (an index page), my chosen page number here is 174.

Next I need to go view a dump of this page just to have a look at the records it contains. This requires the use of another undocumented DBCC command called DBCC PAGE. Again full details of this are in the links below but basically it's used as follows;

DBCC PAGE (DatabaseName, filenumber, pagenumber, printoption)

So, I have the following code

 --switch on client output first

DBCC TRACEON(3604)

--now read the page

DBCC PAGE (Corrupt2K8, 1, 174, 1)

This is the page header;

I'm going to home in on slot7 or record 7. I'll use the Hex editor to modify this record in the page which will then generate an error when DBCC CHECKDB is run. The detail for slot 7 looks as follows;

So, to hack the record at slot 7 on page 174, I first need to work out some figures to find the address locations within the file. Convert the record offset (indicated in the screenshot above) from hex to decimal first and then the address for slot 7 is calculated as follows

page number x num of bytes per page + record offset

This equates to 174 x 8192 + 292 = 1425700

Take the database offline and now open the primary data file using XVI32. From the File menu select open and then browse to the MDF file.

Now, from the File menu select "Address" > "Goto". In the dialog box which appears ensure you select the decimal radio button and enter the address which was calculated above, in my case 1425700. As you can see from the screenshot below, the editor has placed me at the start of my chosen record in page 174.

This record has a length of 28 bytes which was detailed in the page dump we did earlier, now to modify the record. First switch the editor to Text and Overwrite Mode if it isn't already. From the File menu ensure "Tools" > "Text Mode" and "Tools" > "Overwrite" are selected. Now I'll mark the blocks I wish to mangle. To do this, from the File menu select "Edit" > "Block <n> chars". Switching to decimal, I enter the record length of 28. The blocks have now been marked in red as shown below 

Now, to overwrite the record in slot 7 as shown below, in text mode type a simple string

Now click "File" > "Exit" and save the file when prompted to do so. In SQL Server you may now bring the database back online. We'll re run the page dump and check the results which are shown below;

Well, as we can see above the record was modified in the anticipated location, of course the only part hosed here is the non clustered index which is easily fixed by dropping and re creating it. What does DBCC CHECKDB show us?

Now you have a corrupt database which you may use for your DR and script tests. Give this a go in your test systems and by all means post back if you're stuck. 

Credits

Information on these undocumented procedures was digested from Paul Randal's blogs at the following links

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/12/13/more-undocumented-fun_3a00_-dbcc-ind_2c00_-dbcc-page_2c00_-and-off_2d00_row-columns.aspx

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/08/09/692806.aspx

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/10/625659.aspx

Warnings

Obviously you should not perform this on your production databases\servers, complete all tests in your offline environments. I will not be held responsible for those who wish to apply this in online environments