locked
How to set initial alternate Data path during Installation RRS feed

  • Question

  • During initial setup of SQL Server 2005 Standard Addition, I was not able to configure an alternate path (non C:\ ) for the Data component. Can anyone advise on the procedure to set the Data path to non default during initial setup.

     

    Thanks in advance,

     

    Paul

    Wednesday, October 24, 2007 2:26 PM

Answers

  • Do you want to store the User database in alternate path? or you want install SQL Server in other partition?  User database can be configured after installation also. Go properties of the Server and Select the Database setting table and change the Data and Log file path. If you chanage that all the database created will be by  default created on this location.

     

    If you want install SQL Server on seperate partition, then  you can change while installing

     

     

    Madhu

    Wednesday, October 24, 2007 3:19 PM
  • If databases are already exists then it will be there only , existing database files will not be shifted.  All the databases created after changing the default path to new location , will be created in new locaiton. If you want to shift the existing one you may detach the db , copy the file to new location attach the db.

     

    Madhu

    Wednesday, October 24, 2007 4:46 PM

All replies

  • Hi ,
         You can refer the below link http://sql-articles.com/articles/install/insql.htm refer the step 8 in the above link and click "Browse" in it and provide your required path

    Regards
    Deepak
    Wednesday, October 24, 2007 2:41 PM
  • Do you want to store the User database in alternate path? or you want install SQL Server in other partition?  User database can be configured after installation also. Go properties of the Server and Select the Database setting table and change the Data and Log file path. If you chanage that all the database created will be by  default created on this location.

     

    If you want install SQL Server on seperate partition, then  you can change while installing

     

     

    Madhu

    Wednesday, October 24, 2007 3:19 PM
  • Madhu,

     

    Thank you.. looks like that would be the location for modifying the path.. Do you know what occurs to the sample database locations or existing user database locations.. Will the subsequent db data files assume the *:\ ...\MSSQL.n structure in the new path?

     

    Paul

    Wednesday, October 24, 2007 4:39 PM
  • If databases are already exists then it will be there only , existing database files will not be shifted.  All the databases created after changing the default path to new location , will be created in new locaiton. If you want to shift the existing one you may detach the db , copy the file to new location attach the db.

     

    Madhu

    Wednesday, October 24, 2007 4:46 PM
  • If you wish to change the program file directory path, then yes, click "Browse" in Step 8 of the above link.

     

    If you wish to change the directory for your user databases, you will need to follow these steps for each database:

     

    1. Run an ALTER DATABASE statement for each file that makes up the database. For example:

     

    ALTER DATABASE MyDb MODIFY FILE (NAME = <logical datafile name> , FILENAME='x:\new path\MyDbDataFile.mdf');

    GO

    ALTER DATABASE MyDb MODIFY FILE (NAME = <logical logfile name> , FILENAME = 'x:\new path\MyDbLogFile.ldf');

    GO

     

    2. Stop the MSSQLSERVER service.

    3. Move the actual files that you specified above into the new directories you specified above.

    4. Start the MSSQLSERVER service.

    5. Verify the change was made by running sp_helpdb 'MyDb'

     

    Running an ALTER DATABASE statement simply reflects the change within the master database and doesn't actually move the files over.

     

    If you wish to actually move your system databases over to a new location (recommended), the follow the MSDN article Moving System Databases.

    Monday, October 29, 2007 11:22 PM