Systems Engineering and RDBMS

SQL Server Default Folder Locations

Posted by decipherinfosys on September 25, 2008

If you are configuring your backups in SQL Server using the GUI, you would notice that by default the backup folder location would be something like:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\

Likewise for the data and the log folders, the default location would be something like:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

This is on my SQL 2005 default instance.

If you want to make changes to these default locations, these are stored in the registry.  However, since modifying the registry is not a good idea (sometimes we are forced to do it due to the limitations of the tools), good news is that for changing the default data and log file locations, we can do it through the GUI – right click on the instance name, select properties and go to Database Settings.  Over there, you will see Database Default locations at the bottom as shown in the image below:

However, if you want to change the location of the backup folder, you will not be able to find a place where you can make that change – you will need to use the registry for making that change.  On my default instance, this is available at:

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

And under it, you will see Backup Directory as shown in the image below:

So, that is how you can go about changing the default locations for both the data/log file locations as well as the backup folder for a given instance.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: