September 9, 2009 at 12:49 pm
Hi
Could any one tell me the steps to move the Distribution database from one drive to the other i.e moving from the default D:\Program Files\......\Data to a dedicated Data drive?
Actually this has to be done immediately after installing the SQL Server on a new machine.
Thank You,
Yours
SQLBuddy.
September 9, 2009 at 3:17 pm
sqlbuddy123 (9/9/2009)
HiCould any one tell me the steps to move the Distribution database from one drive to the other i.e moving from the default D:\Program Files\......\Data to a dedicated Data drive?
Actually this has to be done immediately after installing the SQL Server on a new machine.
Thank You,
Yours
SQLBuddy.
If it is SQL Server 2005 and above, please do the steps in the order given below.
1) Get the actual location of database and log files of distribution database.
2) Take the distribution database offline
3) Copy database and log files to desired location
4) Modify database so that filelocation is pointed to new location through alter database command.
5) Bring distribution database online.
September 9, 2009 at 3:44 pm
Hi Murthykalyani,
Thank you very much for your help.
But how to take distribution database offline? We don't find that database in SSMS right! I am actually talking about the system distribution database. Also I don't have any replication setup.
Also could you tell me the ALTER DATABASE command for that. Actually I searched the BOL and also the internet but could not find the "logical name" and the actual name of the "database".
I am getting the following error with below query
ALTER DATABASE distribution MODIFY FILE
(name=distmodel, filename='C:\Data\distmdl.mdf')
ALTER DATABASE distribution MODIFY FILE
(name=distmodel_log, filename='C:\Data\distmdl.ldf')
Error:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'distribution'. No entry found with that name. Make sure that the name is entered correctly.
Thank You,
Yours
SQLBuddy.
September 9, 2009 at 5:45 pm
if you dont have properly configured replication you dont have distribution database; but you can set default data/log path. sql will use them after create distribution database
September 9, 2009 at 6:43 pm
Hi Marcin,
Thank you very much for your reply.
Actually the problem was I installed a SQL Server instance on a Prod server. The data files were installed on the local drive. Later I seperated all the system database files except the distribution database on to dedicated Data and Log drives. But I couldn't move the distribution data and log files onto these data and log drives. They are still sitting on the local drive. But according to our company policy no database files should be left on the local drive.
Could you tell me what should I do to move them onto the dedicated Log and Data drives?
Thank You,
Best Regards,
SQLBuddy.
September 10, 2009 at 10:28 am
Any Help Please!
Thank You,
Best Regards,
SQLBuddy.
September 10, 2009 at 10:33 am
sqlbuddy123 (9/9/2009)
But I couldn't move the distribution data and log files onto these data and log drives.
Question is... why? what's preventing you from moving the files?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 10, 2009 at 10:34 am
if you mean the distmdl.mdf and .ldf files these are not required till you set up replication.
Just use windows cut and paste, SQL does not lock these files. simples!
---------------------------------------------------------------------
September 10, 2009 at 10:44 am
Hi George,
Thank You very much for your help.
Could you help me with this?
I can cut and paste those files onto the dedicated data drive and delete the old files from the default data directory. But how do windows know about the new file path for the distribution database. How can windows recognize the new path? We are not using any alter database command to do this. Should I change any registry entries or the system catalog to make SQL Server recognize those files? If so how to do that?
Thank You,
Yours,
SQLBuddy
September 10, 2009 at 11:52 am
Nothing else is required. you're done
---------------------------------------------------------------------
September 10, 2009 at 12:05 pm
SQL just expects those files to be in the same locaton as the master database. as long as they are you will be ok if you set up replication or apply an upgrade later on.
---------------------------------------------------------------------
September 10, 2009 at 12:08 pm
Hi George,
Thank you very much for your reply.
I really appreciate your help.
Best Regards,
SQLBuddy.
September 10, 2009 at 2:22 pm
no probs.
---------------------------------------------------------------------
September 10, 2009 at 2:40 pm
Hi George,
I have copied and pasted the data and log files on to the data and log drives. As of now everything is working fine. I don't know whether that will cause any problems in the future or not.
Thank you very much for all your guidance and help.
Yours
SQLBuddy.
September 10, 2009 at 3:52 pm
it won't.
to avoid the problem, when you install SQL, clicked advanced on the features to install screen and enter where you want the database engine data files to be installed to, will save you the unnecessary grief of having to move them immediately afterwards
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply