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..

MOVING SSIS PACKAGES FROM ONE PATH TO ANOTHER ONE

hi, does anyone know how can i move a group of ssis packages from the original path into another one in the same server? from ssis services? just like we do it with the windows explorer?

thanks for your help!!!!!!!!!!

May be it’s not the easiest way to do it, but it works for me:

1. Open MS Visual Studio, and create a new Integration Services project if you already don’t have one.

2. In the solution Explorer under SSIS packages - right click on the folder – Add existing packages (add original packages)

3. Right click on the project – Property.Specify the output path for your build.Click on Deployment utility and set “Create DepoloymentUtility” to true.Specify the output Path. Click OK.

4. Right click on the project – Build.The packages with a deployment utility should be under the directory specified in step 3.

5. Copy the entire deployment folder and paste it to the server you want to move the packages to.

6. Log in to the server and navigate to the directory made in step 5.Double click on the xxx.SSISDeploymentManifest file to initiate the deployment wizard.

a. Choose File System Deployment (Next)

b. Choose the new folder where you want your packages to be copied to (Next)

c. Finish the wizard, it should create packages in the new directory

Hope it helps…

|||

thanks it works fine, just for the record, there is another option i've found. using the 'dtutil' tool

there it is an example of it:

dtutil /MOVE SQL;destPackage /SQL srcPackage /SOURCEUSER srcUserName /SOURCEPASSWORD $Hj45jhd@.X /DESTUSER destUserName /DESTPASSWORD !38dsFH@.v||| Looks much cleaner..

moving msdb database

on SQL 2000:
I need to move the system databases to a different path. This morning I attempted to move the msdb to a different path, and got myself in trouble.

I'm following the guidelines at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

I added the "-T3608" to the sql server startup parameters, and when I tried to stop and restart sql, it would not start back up. Keep in mind that I did not even attempt to detach the msdb database.

I tested this out on my test server before trying it out on the live server, and it worked fine. I called MS, and Robin told me that she has never seen that before, and had no records of that ever happening. She said the only thing she could think of was that the server was being accessed by users at the time, and this "may" have caused the problem. I would LIKE to believe that was the case, but I'm not convinced. The only error I could see in the logs was this:

spid68 WARNING: problem activating all tempdb files.
See previous errors. Restart server with -f to correct the situation.

Any thoughts on this?

Thanks
TommyTry restarting SQL Server as single user :

Open a dos window on the server, change directory to directory where sqlservr.exe is, then run the following:

sqlservr.exe -c -m

This restarts SQL in single user mode.
Make sure that you only have EM open, as only one person allowed on the server at a time.

Remove the -T3608 flag from the startup params.

Shut down sql server by doing control C in the dos box ( ugly eh??)
then close dos window once sql stops.

Try restarting sql normally.

Let us know how it goes.

HTH

Cheers,

SG.|||I found the problem on this. You need to disconnect all users and pull out the network cable, else sql will not start back up. MS acknowledged this, and said they will be adding it to their KB.

Tommy|||Howdy

Glad its resolved.

Cheers,

SG