Post reply

SQL Server 2005 installation question

  • January 13, 2009 at 8:11 pm

    #130056

    I am installing sql server 2005. Now, my question is how can I give different paths for my data files and my transaction log files while installing....i.e. if i want data files on d drive and trans log files on e drive,thus after installation if i check paths for my system databases then data files will be on d drive and trans files will be on e drive.....Thanks in advance..

  • January 14, 2009 at 1:37 am

    #926411

    I don't remember the exact screen, but when you select the features to install click on advanced and then I think when selecting the Database Engine there's the possibility to change the default path for database files.

    [font="Verdana"]Markus Bohse[/font]

  • January 14, 2009 at 2:19 am

    #926421

    You can do this after installation also if you missed do this while installation.

    In management studio Goto server property --> Database setting --> Database default locations

    Regards,
    Nitin

  • January 14, 2009 at 10:28 am

    #926742

    First of all, thanks all for your replies

    Let me give more details on my question..

    ..We can give the path for default data files..But in my environment.,I want to give seperate paths for data as well as trans files...i.e. data files will be on d drive and transaction log files will be on E drive...I am also aware that we can also assign default paths for data and log files after installation....But I want it from first so that my system databases will also be on proper place...

    I am also aware that we can move system database files after installation...But is there any way that we can specify the paths for data and trans. log files separately while we install sql server??

  • January 14, 2009 at 10:31 am

    #926745

    I haven't seen an option for that in the installer. Probably should be, and maybe I'm just missing it, but I haven't seen it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 14, 2009 at 10:34 am

    #926749

    Same here...There is only option for data files..but about trans files..I am not aware about it...There must we some way...and if there is not, then it's one more drawback of SQL Server

  • January 14, 2009 at 10:43 am

    #926761

    Easiest way is to modify path in server properties after installation. server properties -> Database Settings -> Database default locations.

    SQL DBA.

  • January 14, 2009 at 10:54 am

    #926767

    Hi Sanjay,

    Thanks for your reply. But how about paths for system databases?

    Thanks

  • January 14, 2009 at 10:59 am

    #926772

    RPSql (1/14/2009)

    Hi Sanjay,

    Thanks for your reply. But how about paths for system databases?

    Thanks

    S

    For system databases you have to give path while installing SQL Server 2005. You will have Advanced option when you check for the disk cost. There you can change the path if don't want to store your files in default path. All installation files would be in C:\ drive only but for database ( system databases ) files you can change paths. .

    SQL DBA.

  • January 14, 2009 at 11:50 am

    #926820

    Is there any way to give different paths for data and Trans.log files while we install sql server????????

  • January 15, 2009 at 8:34 am

    #927390

    RPSql (1/14/2009)

    Is there any way to give different paths for data and Trans.log files while we install sql server????????

    Simple answer - no, there isn't, at least in 2005. Maybe 2008 is different, but I haven't tried it yet.

  • January 15, 2009 at 8:42 am

    #927408

    Hi RPSql,

    there isn't any way to change the place for the system databases during the installation.

    You can change the location afterward for SQL Server 2005 according to http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx

    However, do NOT move the distmdl.ldf or the mssqlsystemressource.ldf from their counterparts, the datafiles. If you do so, the cumulative updates (for sure) or service packs ( most probably) won't work.

    As for SQL 2008 the mssqlsytemressource-database will be local and isn't supported to be moved (lessons learned, I think) ( http://msdn.microsoft.com/en-us/library/ms345408.aspx ). The distmdl database doesn't exist under 2008.

    So yes, it is possible to seperate the transaction logs from the database files.

    And yes, do NOT do it (beside of the tempdb perhaps).

  • January 26, 2009 at 3:20 pm

    #932861

    You will have to move the system databases after you install SQL server. However, you need to be careful with the new resource databases that install with SQL 2005. The resource mdf must be in the same directory with the master mdf. The resource ldf must also be in the same directory with the resource mdf. If you separate the resource log and data files, you will not be able to apply updates to SQL server. So, if you wanted to separate data from logs, you could do that after installation with sql scripts, but make sure you do not separate the resource log from the resource data file. The system will allow you to do so, but when you try to apply an update, it will fail.

  • January 27, 2009 at 7:11 am

    #933157

    I'm not sure why you're so concerend about the system database .mdf and .ldf files being on different drives. It's perfectly normal to have them all together. There's very little day-to-day activity on these files. It's the User databses you should be concerned about and as already stated, you can change that path after installation in the Instance properties. Messing with the system databases is complicated and frankly not a good practice to do with every installation of SQL Server.

    Tim White

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply