Clean Out the Unwanted Databases
The first thing I do once I've installed SQL Server is get rid of the sample databases. After all, how often do you actually use them? Wouldn't it be great if the SQL Server install had the option to not install the sample databases? Of course, but it doesn't yet! Maybe if Microsoft gets enough demand, Yukon will offer this option.
This first script (DropDatabases.cmd) drops unwanted databases from the server. While very simple, it demonstrates the concept and the structure of all the scripts (see Listing 1).
It takes in a space-delimited list of database names (like "pubs Northwind MyDB"), iterates through the list and creates a .qry file to pass to OSQL, and then drops the databases. Simple, huh? As I specified earlier, this script has been designed for Windows Server 2003, though it should run in Windows 2000 without any issues. However, don't attempt to run it in NT 4.0. The old "for" command may just be the script's downfall in that OS.
Move the System Databases
By default, SQL Server gives you the option to separate the data and transaction log files from the SQL Server program files when you install it. However, the best practice documentation for SQL Server recommends separating the log files onto a separate set of spindles (drive array) from the data files. Why? Because the log files require a lot of writing and the data files a lot reading. So two drive heads reading and writing to two drives ostensibly is faster than one.
Now take this "best practice" recommendation with a grain of salt. Given the performance that modern hard drives provide, the performance gain comes into play only on systems that have very heavy disk-usage scenarios.
The databases that I have written these scripts for are MSDB, Master, tempDB, and Model. Feel free to modify the script to move user databases. I am not going to list all of the scripts, because it would just take up room and provide little value. Instead, I cover the key areas within the scripts (download the scripts here).
Script for Modifying the Master DB
All the first script (MoveMasterDB.cmd) needs to do is stop the SQL Server service, modify the registry, copy the files to the new location, and restart the SQL Server service. Pretty simple, isn't it? To accomplish its task, the script uses two utilities from the Windows 2000 Resource Kit, Reg.exe (a command-line registry editor tool) and RoboCopy.exe (a command-line, file-copying tool). You can modify the script to use tools that you prefer.
The script first checks the old path and the new path, as shown in Listing 2.
If it detects that the old path (OldDataFilePath) and the new path (NewDataFilePath) are different (i.e., they do not match), then it updates the registry with the new values. The script repeats this same concept for all the variables passed in to it.
Next, it copies the files from one location to another, just after it finishes the registry modifications. Notice the use of
%UtilsDir% (shown in bold), which is where the RoboCopy.exe and Reg.exe files are on the disk.
Finally, once the registry has been updated for all values and all files have been copied, you can restart the SQL Server service:
***** Begin Code Insert *****
REM Ok now restart the SQL Service...
(echo Restarting the SQL Server service.) >> %LogFile%
(net start MSSQLServer) >> %LogFile% 2>>&1
***** End Code Insert *****
And that is basically it! Bad-dah bing, bad-dah boom, you have moved the master database to a new location.
Script for Modifying the Model and MSDB
The next script (MoveDBFiles.cmd) moves the Model and MSDB databases simply by using the "detach" and "reattach" commands. Once again, I have stripped the script down to show only its key elements (see Listing 3).
The first thing I do here is stop the SQL Server service. Then I add a special flag with the value "-T3608" to the registry and restart the SQL Server service. This flag tells SQL Server not to recover any databases except the Master database. This allows for the MSDB and Model databases to be moved, since they will not be in use.
Next, I build a .qry file in the script, which I pass to OSQL to execute the detach command. You can connect to OSQL with either a trusted connection (Windows Authentication) or SQL Authentication, as shown in Listing 4.
Then once again, I use RoboCopy.exe to copy the transaction log and data files (if their paths are different) from the old location to the new location, as shown in Listing 5.
Then I just reverse what I already have done: attach the databases, stop SQL Server, remove the trace flag "-T3608", and then restart the SQL Server. I'm sure by now you can pretty much guess what that code looks like.
Script for Modifying the tempDB DB
The last system database I need to modify is good ol' tempDB. So once again, I look at the key parts of the script (AlterDatabases.cmd), rather than the whole thing (see Listing 6).
I apply a similar principle to this script as I did to the previous scripts (i.e., write out a .sql file with my parameters in it and pass it to OSQL). Then I simply stop and start the SQL Server service so that if I run
sp_helpdb it reports the correct paths for the data and transaction log files. I also delete the old data and transaction log files, as shown in Listing 7.
And there you have it: all the scripts you need to successfully move your transaction logs and data files for your system databases.
Before moving on to general configuration for SQL Server, make sure that you move the databases in this order: MSDB, Model, tempDB, and finally Master. This ensures that Master is up to date with all of the other system database configurations before it moves. And in the event of a catastrophe, it allows you to at least recover the databases to the point at which they last were.