首页 > 代码库 > SQL SERVER MOVING SYSTEM DATABASES
SQL SERVER MOVING SYSTEM DATABASES
- Moving system databases
operation. If you move a system database and later rebuild themaster database, you must move that
system database again because the rebuild operation installs all system databases to their default
location. The steps for moving tempdb, model, and msdb are slightly different than for moving the
master database.
Note In SQL Server 2012, the mssqlsystemresourcedatabase can’t be moved. If you move
the fies for this database, you won’t be able to restart your SQL Server service.
Here are the steps for moving an undamaged system database (other than the master database):
1. For each fie in the database to be moved, use the ALTER DATABASE command with the
MODIFY FILE option to specify the new physical location.
2. Stop the SQL Server instance.
3. Physically move the fies.
4. Restart the SQL Server instance.
5. Verify the 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>‘);
problematic because you might not have access to the server to run theALTER DATABASE command.
Follow these steps to move a damaged system database (other than themaster database or the
resource database):
1. Stop the instance of SQL Server if it has been started.
2. Start the instance of SQL Server in master-onlyrecovery mode (by specifying traceflg 3608)
by entering one of the following commands at the command prompt:
-- If the instance is the default instance:
NET START MSSQLSERVER /f /T3608
-- For a named instance:
NET START MSSQL$instancename /f /T3608
/f indicates that the instance of SQL Server starts with minimal confiuration.
CHAPTER 4Special databases143
3. For each fie in the database to be moved, use the ALTER DATABASE command with the MODIFY FILEoption to specify the new physical location. You can use either Management Studio or theSQLCMD utility.
4. Exit Management Studio or the SQLCMD utility.
5. Stop the instance of SQL Server.
6. Physically move the fie or fies to the new location.
7. Restart the instance of SQL Server without traceflg 3608. For example, run NET START MSSQLSERVER.
8. Verify the 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>‘);
- Moving the master database
summarized here. The biggest difference between moving this database and moving other system
databases is that you must go through the SQL Server Confiuration Manager.
To move the master database, followw these steps.
1. Open the SQL Server Confiuration Manager. Right-click the desired instance of SQL Server,
choose Properties, and then click the Startup Parameters tab.
2. Edit the Startup Parameters values to point to the new directory location for themaster
database data and log fies and click Update. If you want, you can also move the SQL Server
error log fies. The parameter value for the data fie must follow the–d parameter, the value
for the log fie must follow the –l parameter, and the value for the error log must follow the–e
parameter, as shown here:
-dE:\Data\master.mdf
-lE:\Data\mastlog.ldf
-eE:\Data\LOG\ERRORLOG
3. Stop the instance of SQL Server and physically move the fies for to the new location.
4. Restart the instance of SQL Server.
5. Verify the fie change for the master database by running the following query:
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(‘master‘);
144 Microsoft SQL Server 2012 Internals
Finally, if you are also changing your drive letters or folders, you need to verify and perhaps
change the location of the SQL Server Agent error log fie and the confiured database default
locations setting. The Database default locations setting is defied on Server Properties | Database
Settings.
SQL SERVER MOVING SYSTEM DATABASES
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。