SQL Server: Mobile System Database

When it comes to this problem, someone will think of three questions:

1. What is system data?

2. Why do we need a mobile system database?

3. We can use attaching and separating mobile system databases. Why do we need to separate them?

Let me talk about these three questions one by one, and take notes by myself.

1. What is system data?

The so-called system database is the database that comes with the system after we install SQL Server (is this an idiot? ^_^ ).

If you install SQL server2005 or 2008 and open an SQL instance, you will see a database ---> System database folder, which contains the database that comes with the system,

For every system database, let's talk about it in a simple language:

1), Master:
This database is a global database that contains system tables, permission assignment, user account settings, current database configuration information, disk space, file allocation, and other information. Therefore, this data must be backed up after you perform settings such as user account settings, permission assignment, and system configuration changes. Therefore, we strongly recommend that you not only back up your own database frequently, but also back up the database as frequently as you back up your own database. Back up the database at least once every half month or one month.
There is also a special database where Daniel discusses whether to back up this database: SQL Server-Backup master database interval-master database Best Practices
2), model:
This database is just a template database. When we create any database, we copy this database as the basis for the new database. If you want each new database to contain certain objects or permissions, this object or permission can be stored in this database. All newly created databases will inherit the new objects or permissions of this data and have these objects or permissions.
3), MSDB:
Original words: The SQL Server proxy server uses the database to execute scheduled activities such as backup and replication tasks. The database is also used by service borker, which provides queues and reliable message transmission for SQL Server. When we do not perform backup or maintenance tasks for this database, we can usually ignore this database. Before SQL server2005, you can delete the database, but SQL Server is still available, but you cannot maintain any backup history, and cannot define tasks, warnings, work or create a copy, but because the default MSDB database is very small, it is recommended that you do not delete it if it is not used.
4), tempdb:
To put it bluntly, the database is a transfer station or data storage station. The user displays the created temporary table and the worksheet of the intermediate results generated internally during query, processing, and sorting, snapshots used for maintenance will be used in this database. Unlike other databases, each time the SQL server instance is restartedReconstruction rather than restoration. Therefore, all the objects and permissions created in SQL Server will be lost the next time we restart SQL Server.
However, we cannot ignore this database because the size and configuration of tempdb are important for optimizing the functions and performance of SQL Server.
I would like to add a few more words to the tempdb database. Although every time tempdb is rebuilt, it will inherit most Database options from the model database, however, tempdb does not copy its recovery mode from the modeldb database because it always uses the simple recovery mode. In addition, tempdb cannot be deleted or backed up.

2. Why do we need a mobile system database?

After SQL Server is installed, the default system databases are stored in the c: \ Program Files \ Microsoft SQL Server \ mssql10.mssqlserver \ MSSQL \ data folder, which is generally not very large, why are we still moving them?
I didn't have this idea before I managed the server, but I found that my server's drive C has been increasing all the time, or in case of system reinstallation, the database options I set, and the user account settings must be reset, so we have this idea.

Another point is that, as part of the planned or scheduled maintenance operation, we may need to move the system database.

3. You can use append and separation. Why do you need to separate them?

Before answering this question, let's look at a picture.

Note: Right-click the Master System database and select the task. The "detach" option does not appear after the task is selected. It means that the databases of mobile systems are different from those of user-defined databases.
The steps of moving tempdb, model, and MSDB are slightly different from those of the mobile master database.
1) mobile tempdb, model, and MSDB Databases
I), move a non-corrupt system database
First, let's use the query command to check the path where SQL server stores these system databases by default. The query command:
Select name, physical_name as currentlocation, state_desc
From SYS. master_files
F5 execution, display

Start our mobile journey later!
A) use the alter database command with the modify file option to specify the new folder option for each file to be moved in the database. For example:

 -- Move tempdb Alter database tempdb Modify file (name =' Tempdev ', Filename =' D: \ database \ tempdb. MDF '); Alter database tempdb Modify file (name =' Templog ', Filename =' D: \ database \ templog. LDF '); -- Move Model Alter database model Modify file (name =' Modeldev ', Filename ='D: \ database \ model. MDF '); Alter database model Modify file (name =' Modellog ', Filename =' D: \ database \ modellog. LDF '); -- Move MSDB Alter database MSDB Modify file (name =' Msdbdata ', Filename =' D: \ database \ msdbdata. MDF '); Alter database MSDB Modify file (name =' Msdblog ', Filename =' D: \ database \ msdb_log.ldf ');

B) use the net stop MSSQLServer command on the command prompt line to stop the SQL server instance;

C) physically move the file to the folder we define, such as the D: \ database folder described above;

D) restart the SQL server instance;

Done, and then use the above query to verify the change. F5 is executed and displayed

Ii) mobile system databases are required due to hardware faults
If you need to move the system database because of hardware failure, the above method will not work, because we may not be able to access the server to run the alter database command. Then we have another solution!

A) if the SQL server instance has been started, stop the instance;

B) In the command prompt line, enter the following command to start the SQL server instance to master-only recovery mode.

Net start MSSQLServer/f/t3608

C), then we can link to the server. Next we will follow the steps above to "move a database without any damages.

Note: If you use the net start msssql server command to start the SQL server instance, the system will receive the error 1814 message. You can go to "Control Panel"-"Administrative Tools"-"Event Viewer" to view specific error logs.

2), mobile master database

The location of the mobile master database is different from that of other system databases. You can only use the SQL Server Configuration Manager to change the location of the master database.

First open the SQL Server Configuration Manager, right-click the target SQL server instance, select properties, and then click the Advanced Tab ,:

In the startup parameters, edit the values of each parameter to point to the directory location of the new master database data file and log file, as shown below:

-DD: \ database \ master. MDF;

-EC: \ Program Files \ Microsoft SQL Server \ mssql10.mssqlserver \ MSSQL \ log \ errorlog;

-Ld: \ database \ mastlog. LDF

Then stop the SQL server instance and move the physical files to the new folder. For example, I move the files to the D: \ database \ directory;

Finally, start the SQL server instance! If you want to test it, use the test statement mentioned above, F5 to execute ,:

Reference: SQL server technology-storage engine

Author: xingbaifang

Web: http://xbf321.cnblogs.com

Time: 2010.3.12