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
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.PROD\MSSQLServer
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.