SQL Server Mobile System Database _mssql2005

When it comes to this question, there are basically three questions that people think about:

1, what is System data?

2, why do you want to move the system database?

3, Mobile System Database we can use additional and separate, why do we have to say it alone?

Let me say one or two of these three questions and make a note of myself.

1, what is System data?

The so-called system database is the database we have when we install SQL Server (the answer is not very stupid ^_^).

If you install SQL Server2005 or 2008 after you open an SQL instance, you will see a database---> System database folder, which is the system's own database, as shown in figure:

For each system database, let me say this in a simple language:

1), Master:

This database is a global database that contains system tables, permissions assignments, user account settings, current database configuration information, and information about disk space, file allocation, and so on. So back up this data after performing things like user account settings, permission assignments, and changing system configuration information. So it is highly recommended here not only to back up your own database frequently, but also to back up this database, although not as frequently as backing up your own database. Back up this database at least half a month or one months.
There's a special database here. Daniel has discussed whether this database should be backed up: SQL server–backup master database interval–master database Best practices

2), Model:

This database is just a template database, when we create any one database, we are copying this database as the basis for the new database, if you want each new database contains some objects or permissions, you can put this object or permissions in this database, Newly created new databases inherit new objects or permissions for this data, and they have those objects or permissions.

3), msdb:

The SQL Server Proxy server uses the database to perform some scheduled activities such as backup and replication tasks, the authors quote. Service Borker also uses the database, which provides queues and reliable messaging for SQL Sever. This database can usually be ignored when we do not perform backup or maintenance tasks on the database. Before SQL Server2005, you can actually delete the database, and only SQL Server is still available, but you cannot maintain any backup history, and you cannot define tasks, warn, work, or create replication, but because the default msdb database is very small, It is recommended that you do not delete it even if you do not use it.

4), tempdb:

The database is a transit point or data storage station, the user displays the temporary tables created, the worksheets for intermediate results generated internally during query processing and sorting, maintenance snapshots, and so on, which are used in this database, unlike other databases, after each instance of SQL Server is restarted. will be rebuilt, not restored. So all the objects and permissions we create in it will be lost the next time you restart SQL Server.
But we cannot ignore this database, because the size and configuration of tempdb is important for optimizing the functionality and performance of SQL Server.
For the tempdb database, even though it inherits most database options from the model database every time that tempdb is rebuilt, tempdb does not replicate its recovery model from the MODELDB database because it always uses the simple recovery model. In addition, tempdb cannot be deleted and is not backed up.

2, why do you want to move the system database?

The default system databases that we install after SQL Server are placed in C:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\data This folder, the general is not very big, why do we still have to move them?
I didn't have this idea until I had a practice Management server, but I found that my server C was always on the increase, or in the event of a reload system, I set the database options, and the user account settings are reset, so I have this idea.

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

3, with the addition and separation can be, why do you have to say it alone?

Before answering this question, we are looking at a picture

Notice that the "detach" option does not appear when I select the task by right-clicking on the Master System database. That means that moving these system databases is different from a user-defined database.

The steps to move Tempdb,model and msdb are slightly different from the steps to move the master database.

1), mobile Tempdb,model and msdb database

i), move a system database without damage
First, let's look at the path that SQL Server stores these system databases by default, using the query command:

SELECT Name,physical_name as Currentlocation,state_desc from
sys.master_files

F5 execution, as shown in figure:

Then start our mobile journey!

A), use the ALTER DATABASE command with the Modify File option to specify new folder options for each file to be moved in the database. Such as:

--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) to stop the SQL Server instance with the net stop mssqlserver command at the command prompt line;

c), physically move the file to our defined folder, such as the D:\Database folder mentioned above;

d), restart the SQL Server instance;

Done, and then in the above query to verify the changes, F5 execution, display as shown in figure:

II) the need to move the system database due to hardware failure

The above method is not possible with the need to move the system database due to a hardware failure because we may not be able to access the server to run ALTER DATABASE commands. Then we can change a different solution!

A), if the instance of SQL Server is already started, stop the instance;

b, at the command prompt, enter the following command to start the SQL Server instance into master-only recovery mode
NET START mssqlserver/f/t3608

c), then we can link to the server, and then the above "move a undamaged system database" step is the same.

Note: If you start the SQL Server instance directly with the net start Msssql Server command, you receive a 1814 error prompt. We can go to the Control Panel-"Administrative Tools"-"Event Viewer" to see the specific error log.

2), move master database

The location of the move master database is different from that of other system databases, and only the SQL Server Configuration Manager can be used to change the location of master.

First open SQL Server Configuration Manager, right-click the target SQL Server instance, select Properties, and then click the Advanced tab, as shown in the figure:

Edit the values of each parameter in the startup parameter to point to the directory location of the new master database data file and log file, as follows:

d), D:\Database\master.mdf;
e), C:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\log\errorlog;
f), D:\Database\mastlog.ldf

Then stop the SQL Server instance and move the physical files to the new folder, such as I move to the D:\Database\ directory;

Finally start the SQL Server instance and it's done! If you want to test, use the test statement mentioned above, F5 execution, as shown in the figure:

summed up, if the system, and do not want to lose the original database settings information, you can learn to move the system database, but the cloud-Habitat community small set, move does not move another said, master table is still very necessary to back up.