A Temporary Move

Introduction

Recently I had to move a bunch of system databases on a server to have it conform to our standards. I last wrote about moving master to another drive and moving msdb to another drive. This time I am finishing the series with the last system database to move, tempdb.

Why would you move any system database? Well, I mentioned reasons in the other articles, but for tempdb the biggest reason is usually performance. By separating out tempdb from other databases, you can increase the throughput, especially if it moves to a separate physical drive.
The process turns out to be even simpler than moving msbd or master. Let's walk through this simple procedure. The first step is to open Query Analyzer and connect to your server. Once connected, you can run this script to get the names of the files used for tempdb.

use tempdb

go

sp_helpfile

go

You should see something like:

name    fileid filename                                                       filegroup  size

------- ------ -------------------------------------------------------------- ---------- -------

tempdev 1      C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf    PRIMARY    8192 KB

templog 2      C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf   NULL       768 KB

along with other information. Note the names of the files, usually tempdev and demplog by default. You need these names in the next statement, which will actually move the files. Suppose I wanted tempdb to move to a brand to t: drive with it's log on the u: drive. I could run the following:

use master

go

Alter database tempdb modify file (name = tempdev, filename = 't:\data\tempdb.mdf')

go

Alter database tempdb modify file (name = templog, filename = 'u:\data\templog.ldf')

go

At this point, the definition of tempdb is changed. However, since the database is rebuilt everytime SQL Server starts, there are no files to move. You stop and restart SQL Server and it will create tempdb in your new locations.

Read Part I - Move Your Master or part II - Moving MSDB

Conclusions

Once again, an easy process. Simple, but something that would require some research. Fortuneately, I've done that for you and hopefully this resource will be handy when you need it. If you have any comments or questions, please feel free to note them below using the "Your Opinion" button.

Move Your Master

Introduction

Do you ever need to move your master database? Should you?
You might. Especially if you have standards in your company and you find a server that doesn't conform. It can happen, even to me, an experienced DBA. I've been known to not pay complete attention to an install and accidentally put things in the wrong place.

Like master.

Recently I installed a server and wasn't paying attention. Our standard is to place the master data files on the m: drive and the master log on the l: drive. However, I clicked too fast and installed the server on the c: drive. Not wanting to uninstall, I decided to move the master database.

This turns out to be simpler than one would think. The first step is to connect to your SQL Server using Enterprise Manager and select the "Properties" item after right-clicking on the server. You should see something like this:

Once you do this, you should see:

Select the "Startup Parameters" option. This will bring you to a dialog that contains all the startup parameters that are used by SQL Server when the service starts. If you are more daring, these parameters will also be stored in the registry under Local Machine\Software\Microsoft\MSSQLServer\MSSQLServer\Parameters. However, most of you will work with this dialog:

From here, there are usually 3 parameters that you can work with. These parameters are described below:

-d - The path and name to the master database file.
-e - The path to the error log file and it's name. Archived files are the base name + a number.
-l - The path and name to the master database transaction log.

Unfortunately you cannot edit these parameters. So you must remove and then add back the parameters. I'd suggest writing them down or getting a screen shot before beginning.

To move my master database, I first selected the "-d" parameter and clicked "remove". I then typed this in the edit box and clicked "add"

-d m:\MSSQL\data\master.MD

I repeated this for the log file, using this entry.

-l l:\MSSQL\log\mast log.MD

Once this is complete, hit OK and close this dialog and then the server properties dialog. Now you're ready to move the files.

First shut down the SQL Server. I usually right click the server in Enterprise Manager and select "stop". Next open explorer and physically copy the files to the new location.
That's it.

Now start the server and you should be good to go. If you have any issues, they are probably due to incorrectly typing the path and filenames. Double check these. Also check the Event Log (application) for clues if you experience issues.

Read Part II - Moving MSDB

Conclusions

Not a complicated process, but one that I've seen questions posted. In future articles I'll look at how to move MSDB, model, and temp.

Moving MSDB

Introduction

Recently I had to move a bunch of system databases on a server to have it conform to our standards. I last wrote about moving master to another drive and this time decided to write about how one goes about moving two other system databases, msdb and model.

Why would you move the databases? Well, I mentioned that your standards may need these databases on certain drives, as mine do. Or you may want to move them to a larger drive, or a faster drive. Or even, and hopefully it doesn't happen, a drive fails and they are suspect. You restore them from tape, but now you need to tell SQL they are on another drive.
The process turns out to be fairly simple. And it's similar to the process for moving master. Let's walk through this simple procedure. The first step is to open Enterprise Manager and select the server by right clicking it's name. Select the "Properties" option.

At this point, you should see the "General" tab for the server which shows the basic information that describes the server. At this point, you need to select the "Startup Parameters" button.

From here you should get a dialog similar to the following.

In order to move msdb and model, you need to tell SQL Server that you really want to do this. Once you do this, you can follow the normal detach and attach sequence to move the databases. The way you tell SQL Server that you want to move these databases (to prevent accidental mistakes) is to add a trace flag when the server starts up. Specifically trace flag 3608. This is detailed in Q224071. This is accomplished by typing (without quotes) "-T3608" in the edit box at the top of the dialog. After pressing "add" you should see:

For this to take effect, you need to stop and restart SQL Server. Once SQL Server has restarted, you can perform the detach using the GUI, copy the file over, and attach using the GUI. Alternatively, you can use the following T-SQL to detach msdb.

use master

go

sp_detach_db 'msdb'

go

Once the database is detached, copy it (or restore it) to the new location. Once you have verified the .mdf and .ldf files are in the desired location, run this script to attach the database. You may need to change the paths to match your system.

use master

go

sp_attach_db 'msdb','z:\Mssql\Data\msdbdata.mdf','z:\Mssql\log\msdblog.ldf'

go

That's it. You can repeat the same steps for model.

Read Part I - Move Your Master or part III - Moving tempdb

Conclusions

Steve Jones
©dkRanch.net November 2002

-------------------

Move index to another Driver :

declare @id integer

declare @tbname nvarchar(100)

declare @indid integer

declare @indname nvarchar(100)

declare @fill integer

declare @group integer

declare @list nvarchar(4000)

declare @strsql nvarchar(4000)

DECLARE @ROWCNT INTEGER

DECLARE @DEBUG INTEGER

SET @ROWCNT=150000000

SET @DEBUG=0 -- 1 just debug, 0 perform the move

declare curs1 cursor for

SELECT TOP 100 PERCENT dbo.sysobjects.id,dbo.sysobjects.name,dbo.sysindexes.indid, dbo.sysindexes.name AS indname, dbo.sysindexes.OrigFillFactor,

dbo.sysindexes.groupid

FROM dbo.sysindexes INNER JOIN

dbo.sysobjects ON dbo.sysindexes.id = dbo.sysobjects.id

WHERE (dbo.sysobjects.xtype = 'U') AND (dbo.sysindexes.indid BETWEEN 2 AND 254) and (sysindexes.status & 64) = 0 and (not(sysindexes.status & 0x800)=0x800) and sysindexes.groupid=1 AND SYSINDEXES.ROWCNT<=@ROWCNT

ORDER BY dbo.sysobjects.name, dbo.sysindexes.indid

open curs1

fetch next from curs1 into @id,@tbname,@indid,@indname,@fill,@group

while @@fetch_status=0

begin

set @list=''

SELECT @List = @List + '['+ dbo.syscolumns.name+'],'

FROM dbo.sysindexes INNER JOIN

dbo.sysobjects ON dbo.sysindexes.id = dbo.sysobjects.id INNER JOIN

dbo.sysindexkeys ON dbo.sysindexes.id = dbo.sysindexkeys.id AND dbo.sysindexes.indid = dbo.sysindexkeys.indid INNER JOIN

dbo.syscolumns ON dbo.sysindexkeys.id = dbo.syscolumns.id AND dbo.sysindexkeys.colid = dbo.syscolumns.colid

WHERE (dbo.sysobjects.xtype = 'U') AND (dbo.sysindexes.indid =@indid) AND (dbo.sysobjects.id = @id) and (sysindexes.status & 64) = 0 and (not(sysindexes.status & 0x800)=0x800) and sysindexes.groupid=1 AND SYSINDEXES.ROWCNT<=@ROWCNT

ORDER BY dbo.sysobjects.name, dbo.sysindexes.indid, dbo.sysindexkeys.keyno

set @list=left(@list,len(@list)-1)

begin

set @strsql='drop index ['+@tbname+'].['+@indname+']'

print @strsql

IF @DEBUG=0

BEGIN

exec sp_executesql @strsql

END

set @strsql='CREATE INDEX ['+@indname+'] ON [dbo].['+@tbname+']('+@list+') WITH FILLFACTOR = 90 ON [SECONDARY]'--+convert(nvarchar(5),@fill)+' ON [SECONDARY]'

print @strsql

IF @DEBUG=0

BEGIN

exec sp_executesql @strsql

END

end

fetch next from curs1 into @id,@tbname,@indid,@indname,@fill,@group

end

close curs1

deallocate curs1