Moving transaction log for master/model/msdb databases

How do you do this out of interest. I know that when you create a new db you can specify a path for the transaction log i.e a searate physical disk.

Question is how do I put the transaction logs for the system databases on another disc - I can't remember seeing an option during installation...

Thanks

FunkyDwhy would you want to move these files? These db see little activity.

If you were posting a general question, How do I move a Database to a new location? then check out the following:

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071|||I guess if they have little activity it's not necessary I suppose...

If you can't backup the system databases after a change then would the transaction logs help you with recovery?

We have a lot of users creating/deleting databases and I figured that the T-logs might come in handy and so thought it wise to give them the same protection as a normal db....|||What prevents you from backing up the system DB after a change?

Granted you might want to backup a user's db but the entry in the master db will ge tcreated when you restore the backup.

IMHO backing up the master db after changes saves you on lost logins and recovering from a bigger dissaster.|||How about that I don't know when changes are made lol...

That's another issue but one I am trying to work on. There is no way people should be doing stuff ad hoc on a production system... but they do!!

Thanks for the advice :-)|||You can always make friends by revoking sa from these people and force them to justify getting it back!

Again, just restoring the master DB would do little to help in restoring from a lost DB, so as long as you make a backup after changes you should be good to go. Also this might be a good reason for users to NOT be doing adhoc stuff on the production box.|||FunkyD,

The four system databases each have different methods for moving the physical files (including the log files).

Master:
1. Change the startup parameters (from the properties page for the server).
2. Shut down SQL Server.
3. Move the physical files (using Explorer)
4. Restart SQL Server (crossing your fingers here might help)

Model:
This involves setting a trace flag somewhere; look it up on line (use Google) but it is doable.

MSDB:
Same, it involves setting a trace flag. Look it up on Google.

TempDB:
This one is easy:
1. User ALTER DATABASE and the options to specify physical file locatations.
2. Restart SQL Server
3. The new TempDB master and log files will be created automatically; you can remove the old files by hand later.

As for why you might want to do this, I can't really think of any good reasons to move master or model. TempDB, on the other hand, can get a lot of use and it might help performance to move it to a separate partition that otherwise does not have a lot of I/O.

HTH,

Hugh Scott

Originally posted by FunkyD
How do you do this out of interest. I know that when you create a new db you can specify a path for the transaction log i.e a searate physical disk.

Question is how do I put the transaction logs for the system databases on another disc - I can't remember seeing an option during installation...

Thanks

FunkyD|||Thanks - okay, I won't bother moving these dataabases but instead will look at getting a procedure (another one!!) to ensure updates get a backup afterwards..