MSSQL system database transfer

MSSQL and bases 1c 8.2

Here I will describe my observations, as well as small utilities collected by Google for the effective operation of these two software products.
Everyone knows that you need to store user databases on a separate disk, for example, here D: \ Base. And it is also desirable to transfer to a separate disk and transaction files, for example E: \ Logs. Well, since we are talking about transferring databases and logs to different disks, then to increase performance, you need to spread them across different physical disks. Expensively, you say, not quite, it’s a penny within the company, and the headache will be less, even with free space it will be easier.
After all this, it will be advisable to transfer the system databases. Previously, I did not think about it, but I pressed it. And it was like that. Valiant accounting, rolled a new version of the yellow program, and everything was great until it was necessary for them to "close the month." The procedure is long and therefore left it overnight. And in the morning we saw a nice MSSQL database error message. The investigation led me to the fact that there was no free space on the C: \ drive. Namely, the logs of the system databases have expanded beyond any measure. It was decided to transfer.
If the transfer of user bases is not difficult, then the system transferred using Google and admin's ingenuity. Since no exact article was found on the Internet, I decided to describe it here.

And do not forget to remind, do not forget about backup!
')
And now, I will tell how to transfer the MSSQL system bases.
For example, I will transfer them from the folder
C: \ Program Files \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ Data \
at
1. D: \ System (base)
2. E: \ System (logs)

Stop the MSSQL service.

We start MSSQL in the exclusive mode and we transfer the mssqlsystemresource base
To do this, in cmd, run as administrator, type the command

net start MSSQLSERVER /c /m /T3608

then we enter
sqlcmd -A

Then we successively type three commands to change the path to mssqlsystemresource.mdf.

1> ALTER DATABASE mssqlsystemresource

2> MODIFY FILE (name = 'data', filename = 'D:\System\mssqlsystemresource.mdf')

3> GO

If we see the answer:
The file "data" has been modified in the system catalog. The database is started.
So everything is in order.

We drive 3 more commands to change the path to mssqlsystemresource.ldf

1> ALTER DATABASE mssqlsystemresource

2> MODIFY FILE (name = 'log', filename = 'E:\System\mssqlsystemresource.ldf')

3> GO

Again, if we see the answer:
The file "data" has been modified in the system catalog. The database is started.
So everything is in order, and we were not mistaken.

Exit sqlcmd with the QUIT command.

Stop the MSSQL service with the command: net stop mssqlserver
Here’s how it should look:



Copy the files .mdf and .ldf from the databases mssqlsystemresource and master in the proper places for them,
in “D: \ System” we put the bases (.mdf) and in “E: \ System”, respectively, logies (.ldf).

Run SQL Server Configuration Manager.

Go to: Start-> All Programs-> Microsoft SQL Server 2005-> Configuration Tools-> SQL Server Configuration Manager
In the opened window, go to: SQL Server 2005 Services -> SQL Server (MSSQLSERVER) -> Properties
Tab: Advanced
Line: Startup Parameters
Change the path from the standard:
-dC: \ Program Files \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ DATA \ master.mdf; -eC: \ Program Files \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ LOG \ ERRORLOG; -lC: \ Program Files \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ DATA \ mastlog.ldf
on
-dD: \ System \ master.mdf; -eC: \ Program Files \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ LOG \ ERRORLOG; -lE: \ System \ mastlog.ldf
Click the button "Apply"
Illustration:


We start SQL as usual.

Further we transfer the msdb, model, tempdb bases.

In MSSQL Server Management Studio we will execute the following query:
ALTER DATABASE msdb

MODIFY FILE (name = 'MSDBDATA', filename = 'D:\System\MSDBDATA.mdf')

ALTER DATABASE msdb

MODIFY FILE (name = 'MSDBLOG', filename = 'E:\System\MSDBLOG.ldf')

ALTER DATABASE model

MODIFY FILE (name = 'modeldev', filename = 'D:\System\model.mdf')

ALTER DATABASE model

MODIFY FILE (name = 'modellog', filename = 'E:\System\modellog.ldf')

ALTER DATABASE tempdb

MODIFY FILE (name = 'tempdev', filename = 'D:\System\tempdb.mdf')

ALTER DATABASE tempdb

MODIFY FILE (name = 'templog', filename = 'E:\System\templog.ldf')

Looks like that:


Stop MSSQL.
We decompose the files .mdf and .ldf in the necessary places.
We start service MSSQL.
On this, the transfer of system databases is complete.

I also want to mention the backup. My observations led me to the fact that I refused to backup transaction files. In my particular case, they are not needed, I can’t argue in yours, but maybe also. Therefore, I only copy the database, that is, files with the extension .bak. But you cannot restore the database using Management Studio with a simple mouse click, since MSSQL is offended by the absence of .ldf files, but you can use a simple script for our selfish purposes.

RESTORE DATABASE basename

FILE = 'basename'

FROM DISK = 'X:\mssql_backup\folder_ basename\basename_backup_xxxxxxxxxxx.bak'

WITH MOVE 'basename' TO 'E:\MSSQL_DATA\ basename'

Where:
"Basename" is the name of the base
"X: \ mssql_backup \ folder_ basename \" path to database backup.

And just in case, the mdf mount script without ldf. This is for the case when there are manually copied files but there are no transaction files for them.
USE [master]
GO
CREATE DATABASE [ ] ON
( FILENAME = N' file.mdf' )
FOR ATTACH _REBUILD_LOG