Moving System Databases: SQL Server 2000 and 2005

Moving System Databases in SQL Server 2005


Notes

Tempdb is built when ever the sql server restarts so you tell it where you want the database then restart sql and it will build the database there. You will then need to delete the old files.

In SQL 2005 the resource database log and data file have to go into the same directory as the master data file.

sp_helpfile will show you where the database files are – good to run after the change to check they are where you think they are.

Move Tempdb

Run the script below using the correct path for the server:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLogs\templog.ldf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO

For this next step you can wait and do the stop /start for msdb
Next stop Sql Server
Restart SQL Server
Go back and delete the old files

Move Model and MSDB Files

First run this – with the filename being the path required

ALTER DATABASE Model MODIFY FILE (NAME = modellog, FILENAME = 'F:\SQLLogs\modellog.ldf');
Go
ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBLog, FILENAME = 'F:\SQLLogs\msdblog.ldf');
Go
ALTER DATABASE Model
MODIFY FILE (NAME = modeldev, FILENAME = 'E:\SQLData\model.mdf');
Go
ALTER DATABASE MSDB
MODIFY FILE (NAME = MSDBData, FILENAME = 'E:\SQLData\msdbdata.mdf');
go

Stop Sql Server
Move the files
Restart SQL Server

Move Master and Resource Log

1. From the Start menu, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In the SQL Server 2005 Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.

3. In the SQL Server (instance_name) Properties dialog box, click the Advanced tab.

4. Edit the Startup Parameters values to point to the planned location for the master database data and log files, and click OK. Moving the error log file is optional.

The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter.

5. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.

6. Move the master.mdf and mastlog.ldf files to the new location.

7. Start the instance of SQL Server in master-only recovery mode by entering one of the following commands at the command prompt. The parameters specified in these commands are case sensitive. The commands fail when the parameters are not specified as shown.

1. For the default (MSSQLSERVER) instance, run the following command.

NET START MSSQLSERVER /f /T3608

2. For a named instance, run the following command.

NET START MSSQL$instancename /f /T3608

8. Using sqlcmd commands or SQL Server Management Studio, run the following statements. Change the FILENAME path to match the new location of the master data file. Do not change the name of the database or the file names.

Save the script into a file called c:\move.sql (make sure the paths are right for your server NOTE the ldf and mdf go to the same location as the master mdf

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME=
'E:\SQLData\mssqlsystemresource.ldf');
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME=
'E:\SQLData\mssqlsystemresource.mdf');

Now run sqlcmd
"C:\Program Files\Microsoft SQL Server\90\tools\binn\SQLCMD.EXE" -E -S . -d master /i c:\move.sql

1. Move the files to the new location.

2. Set the Resource database to read-only by running the following statement.

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

1. Stop the instance of SQL Server.

2. Restart the instance of SQL Server using the sqlcmd this time without the flags /f /t3608

3. Check file and log locations:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files

Moving System Databases in SQL Server 2000
Moving the Master Database

The location of the master database and its associated log can be changed from within SQL Server Enterprise Manager. To do this:

Open SQL Enterprise Manger and drill down to the proper database server.

Right-click the SQL Server in Enterprise Manager and click Properties.
Click the Startup Parameters button and you will see something similar to the following entries:

-dC:\MSSQL\data\master.mdf
-eC:\MSSQL\log\ErrorLog
-lC:\MSSQL\data\mastlog.ldf

Change these values as follows:

Remove the current entries for the Master.mdf and Mastlog.ldf files.

Add new entries specifying the new location:

-dE:\SQLData\master.mdf
-lL:\SQLLogs\mastlog.ldf

Stop SQL Server.

Copy the Master.mdf and Mastlog.ldf files to the new locations

Restart SQL Server.

Moving MSDB and Model

When you are using this procedure to move the msdb and model databases, the order of reattachment must be model first and then msdb.

To move the MSDB follow these steps:

In SQL Server Enterprise Manager, right-click the server name and click Properties. On the General tab, click Startup Parameters.

Add a new parameter as -T3608. Select OK to close the Startup Parameters and the Properties page. You will not be able to access any user databases at this time. You should not perform any operations other than the steps below while using this trace flag.

Drill down to the msdb database and then right click on it. Select Properties and then select the Options tab.

Select Restrict access and then Single User. Close the Properties sheet by selecting OK.

Stop and then restart SQL Server.

Open SQL Query Analyzer and then detach the msdb database using the following commands:

use master
go
sp_detach_db 'msdb'
go

Move the Msdbdata.mdf and Msdblog.ldf files from the current location to the new location.

Reattach the MSDB database as using the following commands:

use master
go
sp_attach_db 'msdb','E:\SQLData\msdbdata.mdf','L:\SQLLogs\msdblog.ldf';
go
Remove the -T3608 trace flag from the Startup Parameters box in the SQL Enterprise Manager.

Stop and then restart SQL Server.
Moving Tempdb

*** This is the same as the SQL 2005 method ***

Run the script below using the correct path for the server:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQL_Logs\templog.ldf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQL_Data\tempdb.mdf');
GO

Next stop Sql Server
Restart SQL Server
Go back and delete the old files

Replication failed between two 2000 machines: server execution failed

After dropping all replication objects and then recreating them the agents were still failing.

The error that was showing stated: server execution failed

Ran the snapshot replication agent from the command line.

To do this go to the sql folder with snapshot.exe In this case C:\Program Files\Microsoft SQL Server\80\COM

Then run snapshot.exe followed by the string found in the second step of the start agent job. In this case: -Publisher [Server2Name] -PublisherDB [replictedDatabaseName] -Distributor [Server2Name] -Publication [nameOfPublication] -DistributorSecurityMode 1

This then generated the real error: ATL71.Dll was missing from – series of directories all defined by PATH.

I did a search and found this dll in the temp directory. I copied it to system32 and re ran the agents.

They are now distributing transactions. It took a while to catch back up again though.