| Author |
Topic |
|
beyonder422
Posting Yak Master
124 Posts |
 Posted - 2005-11-15 : 11:57:03
|
| Can anyone tell me if it is safe to move the system databases?ie. distribution, master, model, etc., from one folder/drive to another?Can anyone tell me any known reasons why moving (detach copy/paste to new location) those files would cause any problems? |
|
|
TimS
Posting Yak Master
198 Posts |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-15 : 19:26:06
|
| Are you even using the distribution database??MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-15 : 19:31:02
|
| I have successfully moved the system databases using KB224071 several times in the past. I have never moved the distribution database though.Tara Kizeraka tduggan |
|
|
|
beyonder422
Posting Yak Master
124 Posts |
Posted - 2005-11-16 : 09:54:35
|
| Well,I was down to the restart step after changing the start-up param's for the master db, and everything has gone to sh1t.I can't start the db back-up...I get a MSSQLServer has "started and then stopped" and I can't connect to the db server at all through EM.anyone have any suggestions as how to get in to see/change/troubleshoot/whatever without using EM (command line or something)?is there a safe mode for SQL Server? - HELP! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-16 : 12:43:55
|
| You'll need to explain to us what you did so far. Also, we'll need the error message from Event Viewer as to why the service won't start.Tara Kizeraka tduggan |
|
|
|
beyonder422
Posting Yak Master
124 Posts |
Posted - 2005-11-16 : 13:01:10
|
| msdb steps - good1. I went through the msdb removal successfully (stop server, remove, start server), granted I have not yet reattached msdb, per MS article, I am moving all system databases from one drive to another.master steps - problem 2. changed db start-up paramaters, "copied" files (master.mdf, mastlog.ldf), to new location and pointed errorlog param to new folder location, stopped server, then tried to start it back up and BAM....server won't start. whenever I try to start sqlserveragent i get, "started and stopped"look in event manager and see this:17050 :initerrlog: Could not open error log file 'S:\SQL_Logs\ErrorLog'. Operating system error = 5(Access is denied.).I have "Everyone" full control on that folder. Side note, that folder is on a SAN, and prior to all of this the cluster was failed over and is still failed over to the server that I am working on.What other details can I provide?A basic question I've got, is there any way to point the errorlog param back to the original folder location without using enterprise manager? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-16 : 13:04:24
|
| You've provided enough information. Your problem is with the Error Log. Is S:\SQL_Logs\ErrorLog a good path? If it is, then I'd suggest logging into the server with the MSSQLSERVER account and try to access that path. This would be the same thing that SQL Server would do. Also, make sure it can access the files inside that folder as well.Tara Kizeraka tduggan |
|
|
|
beyonder422
Posting Yak Master
124 Posts |
Posted - 2005-11-16 : 13:46:48
|
| ok, tried that. I'm logged on with the mssqlserver account user and have opened, created, edited, deleted files and folders in that location.no problem.I've tried changing the mssqlserver account to system account, another "god" account, but still same error as before...I would like to point the error log param to another location. Is there another way to do that without using Ent. Man.? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-16 : 13:53:42
|
| I believe the info is stored in the registry. Do a search in HKLM for that path and modify it. Then move the files over and try starting the service again.Tara Kizeraka tduggan |
|
|
|
beyonder422
Posting Yak Master
124 Posts |
Posted - 2005-11-16 : 14:22:58
|
Well, SH1T!I just got it. just slap me around and call me dumb@ss....when i put the errorlog parameter into start up i used-eS:\log\errorlog,thinking it was going to go into that folder and create it's files...NO, NO, NO....That was the actual file name it needed "ErrorLog" as a file name, not as a folder name.don't know how I came to that conclusion. I started using sqlserv command line parameters for troubleshooting and stumbled onto that revalation... Thanx for the help guys (er moral support).... lol |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-16 : 14:29:31
|
| I'm glad you worked it out!Tara Kizeraka tduggan |
|
|
|
beyonder422
Posting Yak Master
124 Posts |
Posted - 2005-11-16 : 16:12:55
|
| None of the processes listed in that article work with the distribution database.I found the mdf file location on the "Replication" tab of the sql server properties, but can I split the data and log file locations?I want to put all my data files on D drive and log files on L drive, etc... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-16 : 16:19:05
|
| I would think so. I believe the distribution database is considered a user database and not a system database. I've always left the MDF and LDF files for the distribution in the same folder. All other user databases have them split on different drives for performance.Tara Kizeraka tduggan |
|
|
|
beyonder422
Posting Yak Master
124 Posts |
|
|
Beth
Starting Member
6 Posts |
Posted - 2005-12-12 : 11:20:47
|
| Potentially stupid question: Why would you want to move the system databases? SHOULD they be moved from the default installation path? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-12 : 14:31:40
|
| "SHOULD they be moved from the default installation path?"If the current location is C:\PROGRAM FILES\MICROSOFT SQL SERVER\...then my answer would be definitely!!They should not be polluting your System driver, NOR you static programs folder for that matter. And ideally they should be on a disk channel more appropriately suited to the requirements of a database file, and [separately] a Transaction log file, than the requirements of the O/SMOOKristen |
|
|
|
Beth
Starting Member
6 Posts |
Posted - 2005-12-12 : 19:11:29
|
Thank you. I was afraid you would say that  |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-12-12 : 21:28:08
|
quote:
Originally posted by Beth
Potentially stupid question: Why would you want to move the system databases? SHOULD they be moved from the default installation path?
you would move them for performance reasons or managebility reasons.In the past, we would move our Tempdb to a RAID10 filesystem for better performance. We would also move the other system databases to a separate (from the user databases) filesystem.Now, we really no longer do this for performance reasons since all our servers are SAN attached. We do still move the system databases just to maintain a standard installation. We know exactly where the datafiles are located and this can help us with management - which filesystems to exclude for netbackup, which to exclude for virusscan, etc.hth.-ec |
|
|
|
|
Safe to move system databases?