SQL Server Default Backup(s) Location- Using DENALI SSMS/2008R2

Have you ever been in a situation thinking, why MSFT did not included an option to change default backups location in SSMS? Well, I was in few cases!! The good news is they’ve given that Option in DENALI SSMS….Yes Guys you heard it right!! So no more digging into Scary Registry values to change those settings 🙂 In this post let’s see how this can be achieved now running on 2008R2(This applies to SQL 2005/2008 and 2008R2) and also..let’s see how to tweak this option even on earlier Versions of your SQL Server using DENALI SSMS without getting our hands dirty touching Registry values 😉

If you recall correctly, while installing SQL Server It’ll ask for all the Data, Log, TempDB and Backup locations. Changing the TempDB files location is pretty simple, can be done just by Alter Database/Modify File and restarting your SQL Instance. Changing Default Database Files(mdf and ldf) files location is also pretty much simple, Just Right click on your Instance in SSMS and select Properties and navigate to Database Settings as shown below…

But……….where can i look and change the default Default Backup Location???  By navigating to the below registry entry


In this case I’m looking at my “PROD” Instance. Similarly  If i want to verify/change my “DR” Instance backup location, I can simply go to MSSQL10_50.DR and do the same. This might change accordingly for your SQL Instance.

That was little bit annoying right??? Well, it really depends on how comfortable are you with dealing with registries!! Okay! Now let’s see how this can be achieved using DENALI SSMS without pain.

Verifying/Changing Default Backup Location in DENALI:

Open your DENALI SSMS and connect to your DENALI Instance and RC and Properties at Instance level and navigate to Database Options, Interestingly You can see an added option below your Database Files location(s) as shown below

Let’s change the Backup location from SSMS and let’s also see how the registries are being changed behind the scenes.

Registry before Changing Backup Location:

Changed Backup Location from DENALI SSMS:

Registry After changing Backup(s) Location:

Awesome!!!!!!!!!!!!! Interestingly, you can also just connect to your SQL Server 2008R2 Instance from DENALI SSMS and change your default backup Location for your SQL Server 2008R2. Huhuuuuuuuuuu.

Let’s see how it works. I’ve connected to my PROD Instance which is SQL Server 2008R2 version from DENALI SSMS as you can see below and i changed the default location to D:\Backs.

Nowww……Let’s see how our Registry Value looks like for our 2008R2 PROD Instance.

Tadaaaaaaaaaaaaaaaaaaaa!!!!!! 🙂 🙂 🙂
For me this is one of the Most Interesting enhancements with DENALI SSMS. Can’t express how impressed am i with this little thing:)

Bottom Line : Using DENALI SSMS you can change the Default Backup Location for your SQL Server DENALI Instance as well as SQL Server 2008R2 Instance without digging into Registry Values.

SQL Server Configuration Manager/Startup Parameters 2008R2 vs DENALI

Things we should be Aware of in SQL Server DENALI Configuration Manager, A Small simple change which might make a huge difference!

Before proceeding any further, let me make it very clear….for any changes to me made on any of the SQL Server Services you’ve to make use of SQL Server Configuration Managernot our typical windows services from administrative tools. 

I’ve seen in the past(still seeing few folks doing the same even now!!) where people going to “RUN” and entering “services.msc” for changing SQL Server Service accounts, Start Up type etc….Hope you are not doing the same!

Config Manager in SQL Server 2008R2:

Okay, Let’s jump into Config Manager in SQL Server 2008R2, when you choose properties of SQL Server Engine we’ll be getting what is shown in the screenshot below.

This is the place where we’ve to change the Service accounts and pwd’s. You can change the Start Mode(Automatic/Manual/Disabled) in the Service Tab. For adding any start up parameter, you’ve to go to your advanced Tab as shown below.

Configuration Manager in DENALI:

See the screenshot below and if you see carefully, you’ll notice a new tab for startup parameters!

Basically, DENALI has 2 new tabs, one for Brand new fabulous feature “AlwaysOn HA” and the other for “Starup Parameters”.  Let’s see what’s present in my AlwaysON tab…

Well, I’ve Installed this Instance of DENALI on my WINDOWS 7 x64 Client OS which is a stand alone, hence unfortunately, I can’t show you much about this setting here 😦  (I’m planning to Install DENALI on my LAB Cluster pretty soon to explore and learn this brand new HADR feature of DENALI…yayyyyyy)

Let’s move on to Starup Parameters Tab, You can see the brand new interface as shown below.

IMHO, this makes all of our(DBA’s) lives much easy dealing with those annoying semi-colons “;” and Spaces where we should be very very cautious upto SQL Server 2008R2. A single space will change the entire behavior of your startup parameter(s).

Let me show you how we can add a trace flag as a starup parameter in DENALI. Let’s try to enable Trace Flag 3226  as a start up parameter to disable all the successful backup events being logged in our error logs.( See here if you want to know how you can enable a trace flag globally instead of making it a startup parameter). All you need to do is Just typing “-T 3226” and click Add ->Apply->Ok (Service restart is required to reflect the changes) as shown in the below screenshot.

If you want to Update any parameter, all you need to do is Just click on the parameter which you want to change and make changes and click on Update Button. ( The Add button will change to Update button once you select any parameter as shown below).

In the same manner you can remove any Startup parameters which you’ve added.

This greatly reduces pain in our Butt dealing with Spaces and Semicolons( I’m sure that almost everybody agree with me in this matter, especially who’ve already faced an issue and wasted time(like me when i was a Novice in SQL)  figuring out what’s wrong with the parameter provided and banging head to your desk, OMG! why this SQL Server is not starting up as expected 😦 )

Hope this post helps!