There are some situations where we need to move
the SQL Server data and log files to a new directory or even to another disk.
This procedure can be done safely, both in system and user databases. The only
exception is the system database “Resource”, which is located in “<drive>:\Program Files\Microsoft
SQL Server\MSSQL<version>.<instance name>\MSSQL\Binn\” and
cannot be moved . To move the other databases, follow the steps below:
MOVING SYSTEM DATABASES
1. For each file to be moved, except
for the files from databases “master” and “Resource”, run the following
statement:
ALTER DATABASE <database
name> MODIFY FILE (NAME = <logical
name>, FILENAME = '<new path>\<file name>')
2. Stop the instance of SQL Server.
3. Move the files to the new location.
4. Restart the instance of SQL Server.
5. Verify the file change by running
the following query:
SELECT name, physical_name
AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database name>');
6. If the “msdb” database is moved and
the instance of SQL Server is configured for “Database Mail”, complete these
additional steps:
a. Verify that Service Broker is
enabled for the “msdb” database by running the following query:
SELECT is_broker_enabled
FROM sys.databases
WHERE name = N'msdb';
b. Verify that Database Mail is working
by sending a test mail.
MOVING THE “MASTER” DATABASE
To move the “master” database, follow these
steps:
1. From the “Start” menu, point to “All
Programs”, point to “Microsoft SQL Server”, point to “Configuration Tools”, and
then click “SQL Server Configuration Manager”.
2. In the “SQL Server Services” node,
right-click the instance of SQL Server (for example, “SQL Server (MSSQLSERVER)”)
and choose “Properties”.
3. In the service properties dialog
box, click the “Startup Parameters” tab.
4. In the “Existing parameters” box,
select the “–d” parameter to move the “master” data file. Click “Update” to
save the change. In the “Specify a startup parameter” box, change the parameter
to the new path of the “master” database.
5. In the “Existing parameters” box, select the “-l”
parameter to move the “master” log file. Click “Update” to save the change. In
the “Specify a startup parameter” box, change the parameter to the new path of
the “master” database.
The parameter value for the data file should follow the “-d”
parameter and the value for the log file should follow the “-l” parameter. The
following example shows the parameter values for the default location of the “master”
data file.
-dC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\master.mdf
-lC:\Program Files\Microsoft SQL Server\MSSQL<version>.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
If the planned relocation for the “master” data file is “E:\SQLData”,
the parameter values would be changed as follows:
-dE:\SQLData\master.mdf
-lE:\SQLData\mastlog.ldf
6. Stop the instance of SQL Server by
right-clicking on the instance name and choosing “Stop”.
7. Move the “master.mdf” and “mastlog.ldf” files to
the new location.
8. Restart the instance of SQL Server.
9. Verify the new settings by running the following
query:
SELECT name, physical_name
AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id =
DB_ID('master');
GO
FOLLOW-UP: AFTER
MOVING ALL SYSTEM DATABASES
If all of the system databases were
moved to a new drive, volume or to another server with a different drive
letter, make the following updates:
- Change the “SQL Server Agent” log path. If you do not update this path, “SQL Server Agent” will fail to start.
- Change the database default location. Creating a new database may fail if the drive letter and path specified as the default location do not exist.
To change the “SQL Server Agent” Log Path
1. From “SQL Server Management Studio”, in “Object
Explorer”, expand “SQL Server Agent”.
2. Right-click “Error Logs” and click “Configure”.
3. In the “Configure SQL Server Agent Error Logs”
dialog box, specify the new location of the “SQLAGENT.OUT” file. The default
location is “C:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance
name>\MSSQL\Log\”:
To change the database default location
1. From “SQL Server Management Studio”, in “Object
Explorer”, right-click the SQL Server server and click “Properties”.
2. In the “Server Properties” dialog box, select “Database
Settings”.
3. Under “Database Default Locations”, browse to
the new location for both the data and log files:
4. Stop and start the SQL Server service to
complete the change.
MOVING USER DATABASES
In SQL Server, we can move the
data, log, and “full-text” catalog files of a user database to a new location
by specifying the new file location in the “FILENAME” clause of the “ALTER
DATABASE” statement. This method applies to moving database files within the
same instance of SQL Server. To move a database to another instance of SQL
Server or to another server, use “backup” and “restore” or “detach” and “attach”
operations.
CONSIDERATIONS
When we move a database onto
another server instance, to provide a consistent experience to users and
applications, we might have to re-create some or all the metadata for the
database. For more information, see “Manage Metadata When Making a Database
Available on Another Server Instance (SQL Server)” (https://msdn.microsoft.com/en-us/library/ms187580.aspx).
Some features of the SQL Server
Database Engine change the way that the Database Engine stores information in
the database files. These features are restricted to specific editions of SQL
Server. A database that contains these features cannot be moved to an edition
of SQL Server that does not support them. Use the “sys.dm_db_persisted_sku_features”
dynamic management view to list all edition-specific features that are enabled
in the current database.
The procedures in this topic
require the logical name of the database files. To obtain the name, query the “name”
column in the “sys.master_files” catalog view:
Starting with SQL Server 2008
R2, “full-text” catalogs are integrated into the database rather than being
stored in the file system. The “full-text” catalogs now move automatically when
you move a database.
PLANNED RELOCATION
PROCEDURE
To move a data or log file as
part of a planned relocation, follow these steps:
1. Run the following statement:
ALTER DATABASE <database
name> SET OFFLINE;
2. Move the file or files to the new location.
3. For each file moved, run the following
statement:
ALTER DATABASE <database
name> MODIFY FILE (NAME = <logical
name>, FILENAME = '<new path>\<file name>');
4. Run the following statement:
ALTER DATABASE <database
name> SET ONLINE;
5. Verify the file change by running the following
query:
SELECT name, physical_name
AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database
name>');
RELOCATION FOR
SCHEDULED DISK MAINTENANCE
To relocate a file as part of a
scheduled disk maintenance process, follow these steps:
1. For
each file to be moved, run the following statement:
ALTER DATABASE <database
name> MODIFY FILE (NAME = <logical
name>, FILENAME = '<new path>\<file name>');
2. Stop the instance of SQL Server or
shut down the system to perform maintenance. For more information, see “Start,
Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL
Server Browser Service” (https://msdn.microsoft.com/en-us/library/hh403394.aspx).
3. Move the file or files to the new
location.
4. Restart the instance of SQL Server
or the server. For more information, see “Start, Stop, Pause, Resume, Restart
the Database Engine, SQL Server Agent, or SQL Server Browser Service” (https://msdn.microsoft.com/en-us/library/hh403394.aspx).
5. Verify the file change by running
the following query:
SELECT name, physical_name
AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database
name>');
FAILURE RECOVERY PROCEDURE
If a file must be moved because
of a hardware failure, use the following steps to relocate the file to a new
location.
However, if the database cannot
be started, that is it is in suspect mode or in an unrecovered state, only
members of the “sysadmin” fixed role can move the file.
1. Stop the instance of SQL Server if it is
started.
2. Start the instance of SQL Server in “master-only”
recovery mode by entering one of the following commands at the command prompt:
For the default (MSSQLSERVER) instance, run the following
command:
NET START MSSQLSERVER /f /T3608
For a named instance, run the following command:
NET START MSSQL$<instance
name> /f /T3608
For more information, see “Start, Stop, Pause,
Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser
Service” (https://msdn.microsoft.com/en-us/library/hh403394.aspx).
3. For each file to be moved, use “sqlcmd” commands
or SQL Server Management Studio to run the following statement:
ALTER DATABASE <database
name> MODIFY FILE (NAME = <logical
name>, FILENAME = '<new path>\<file
name>');
For more information about how
to use the “sqlcmd” utility, see “Use the sqlcmd Utility” (https://msdn.microsoft.com/en-us/library/ms180944.aspx).
4. Exit the “sqlcmd” utility or SQL Server
Management Studio.
5. Stop the instance of SQL Server.
6. Move the file or files to the new location.
7. Start the instance of SQL Server. For example,
run: “NET START MSSQLSERVER”.
8. Verify the file change by running the following
query:
SELECT name, physical_name
AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database name>');
For more information, see the articles below:
- Move System Databases: https://msdn.microsoft.com/en-us/library/ms345408.aspx
- Move User Databases: https://msdn.microsoft.com/en-us/library/ms345483.aspx
No comments:
Post a Comment