SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Duncan Pryde / Default Backup Directory / Latest Posts

RE: Default Backup Directory

zymos — Mon, 05 Mar 2012 08:15:39 GMT

Nice question and important to note that the default backup directory cannot be changed using SQL Management Studio but by modifying the registry in SQL Server 2005.Thanks.

RE: Default Backup Directory

Duncan Pryde — Thu, 24 Feb 2011 01:23:30 GMT

Thanks everyone for a lively discussion. I honestly thought this would be a nice uncontroversial question! Apart from it actually being incomplete (or incorrect, depending on your point of view), there are also issues with what the default location would actually be and so on.Live and learn I suppose!Duncan

RE: Default Backup Directory

Christoph Muthmann — Thu, 24 Feb 2011 00:56:41 GMT

Try it with SQL Server 2000! It's funny!Cheers,Christoph

RE: Default Backup Directory

Christoph Muthmann — Thu, 24 Feb 2011 00:49:04 GMT

I've just used the facets method with SSMS 2008 and SQL Server 2005. It is working! So you should give the points to the answer SSMS!GreetingsChristoph

RE: Default Backup Directory

L' Eomot Inversé — Wed, 23 Feb 2011 19:35:51 GMT

Interesting question.Using .NET (Framework 2 or later) it can also be done using SMO, there are public functions to get and set the (default) backup directory. Documented for SQL 2005 at [url=http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.settings.backupdirectory%28v=SQL.90%29.aspx]Settings.BackupDirectory Property[/url].

RE: Default Backup Directory

SQLRNNR — Wed, 23 Feb 2011 16:54:51 GMT

RE: Default Backup Directory

SQLRNNR — Wed, 23 Feb 2011 16:52:33 GMT

Thanks for the question.

RE: Default Backup Directory

UMG Developer — Wed, 23 Feb 2011 15:06:22 GMT

Thanks for the question. (It was easy since it has been modified to limit it to SQL Server 2005.)

RE: Default Backup Directory

SanDroid — Wed, 23 Feb 2011 09:08:33 GMT

[quote][b]Duncan Pryde (2/23/2011)[/b][hr]No apologies however to anyone that didn't know about that way of doing it and thought you could set it in the same place as the default locations for data and log files ;-))[/quote]Duncan, you can if you do it during the server install. Of course your question did say default, but then again a default installation on a cluster never puts any of these on the "C:" or "<system drive>:". You could add that to your explination. :-D

RE: Default Backup Directory

SanDroid — Wed, 23 Feb 2011 09:04:30 GMT

[quote][b]The Spartans (2/23/2011)[/b][hr]Good qUESTIONS..I SAW THIS TYPE QUESTIONS VERY RARE[/quote]This is off topic, but does everyone know there is an [b][u]Edit button[/u][/b] for changing your POST?It actually does work!

RE: Default Backup Directory

SanDroid — Wed, 23 Feb 2011 09:02:18 GMT

First let me say I only got the question right because I know someone that works at M$.Second let me say that I have no idea if this question is still on the M$ database exams becuase it is so controverted.The exam question used to be how do you set the default backup directory.The correct answer was only during installation.It was said the other "correct answers" were wrong becuase manual registry updates to change a policy settings was not supported.Unfortunately it actualy can be done by updating the registry and with the FACETS management object for updating policy objects. Does not even have to be done in SSMS if you know how to call it from code.Shame that M$ support does not properly document the Policy Update or FACETS utility.

RE: Default Backup Directory

SKYBVI — Wed, 23 Feb 2011 08:43:59 GMT

[quote][b]Duncan Pryde (2/23/2011)[/b][hr]SKYBVI, Lady Di, MG-148046 and anyone else who picked SSMS because you can do this in 2008:In case you haven't read my previous posts, I apologise for the question - it should have specified SSMS 2005, and I have emailed the webmaster to ask for it to be changed. Not much more I can do for now I'm afraidDuncan[/quote]Thanks Duncanbtw, you made us afraid before :)Regards,Sushant

RE: Default Backup Directory

danschl — Wed, 23 Feb 2011 08:36:49 GMT

easy one today thanks

RE: Default Backup Directory

Duncan Pryde — Wed, 23 Feb 2011 08:20:10 GMT

SKYBVI, Lady Di, MG-148046 and anyone else who picked SSMS because you can do this in 2008:In case you haven't read my previous posts, I apologise for the question - it should have specified SSMS 2005, and I have emailed the webmaster to ask for it to be changed. Not much more I can do for now I'm afraidDuncan

RE: Default Backup Directory

Cliff Jones — Wed, 23 Feb 2011 07:38:24 GMT

RE: Default Backup Directory

MG-148046 — Wed, 23 Feb 2011 07:07:42 GMT

I answered based on SSMS 2008. I haven't used the 2005 client tools in quite some time and the question did not specify which version of SQL tools was being used :(

RE: Default Backup Directory

SKYBVI — Wed, 23 Feb 2011 06:55:14 GMT

@ lady di....Same with me...I got wrong answer :(Regards,Sushant

RE: Default Backup Directory

Lady Di — Wed, 23 Feb 2011 06:48:31 GMT

I answered SQL Server Management Studiop because in SQL Server 2008, we can change the default backup directory in it.Select facets on the instance of SQL Server and change the value of Backup Directory.In the question, it was not specified if it was SQL Serv 2005 or 2008.

RE: Default Backup Directory

Koen Verbeeck — Wed, 23 Feb 2011 06:40:35 GMT

[quote][b]michael.kaufmann (2/23/2011)[/b][hr][quote][b]SUPER SQL STAR (2/22/2011)[/b][hr]GOOD MORNING ..MORNING I LEARNED NEW TOPIC THANKS..The directories for the default data files, log files and backups are stored in the system registry. As mentioned already you have the ability to change the default data and log directories using SQL Server Management Studio, by right clicking on the server name and selecting properties and navigating to the Database Settings page as shown below....[/quote]Super SQL Star,are you Greg Robidoux and is this your pseudonym here on SSC?If you aren't, please give credit to Greg and quote that you copied from his excellent post ([url]http://www.mssqltips.com/tip.asp?tip=1583[/url]), as Tejaswili Patil already pointed out.Regards,Michael[/quote]Busted!(unless he really is Greg Robidoux :-))

RE: Default Backup Directory

tejaswini.patil — Wed, 23 Feb 2011 06:29:14 GMT

Hi,In SQL Server 2008, we can change it from SSMS by right clicking Instance name.But in SQL Server 2005, it was possible by modifying registery.The question is not wrong but it is applicable to 2005 and not to 2008.Duncan has already said this in earlier posts. Kindly read earlier posts.Regards,Tejaswini

RE: Default Backup Directory

SKYBVI — Wed, 23 Feb 2011 06:22:08 GMT

The question is wrong.We can change from SSMS too...Why did i got wrong answer???Regards,Sushant

RE: Default Backup Directory

michael.kaufmann — Wed, 23 Feb 2011 05:51:11 GMT

[quote][b]SUPER SQL STAR (2/22/2011)[/b][hr]GOOD MORNING ..MORNING I LEARNED NEW TOPIC THANKS..The directories for the default data files, log files and backups are stored in the system registry. As mentioned already you have the ability to change the default data and log directories using SQL Server Management Studio, by right clicking on the server name and selecting properties and navigating to the Database Settings page as shown below....[/quote]Super SQL Star,are you Greg Robidoux and is this your pseudonym here on SSC?If you aren't, please give credit to Greg and quote that you copied from his excellent post ([url]http://www.mssqltips.com/tip.asp?tip=1583[/url]), as Tejaswili Patil already pointed out.Regards,Michael

RE: Default Backup Directory

paul.knibbs — Wed, 23 Feb 2011 03:12:16 GMT

I picked a random answer because I was interested to see what the actual answer was...interesting to know!

RE: Default Backup Directory

Duncan Pryde — Wed, 23 Feb 2011 02:39:02 GMT

Following James' observations, I've requested that the question be changed to apply only to SQL Server 2005, and the explanation reworded to add that you can in fact use SSMS 2008 to change the default backup directory.Again, apologies to anyone that knew about the Facets method and got the question wrong because of that.(No apologies however to anyone that didn't know about that way of doing it and thought you could set it in the same place as the default locations for data and log files ;-))Duncan

RE: Default Backup Directory

Sree Divya — Wed, 23 Feb 2011 02:26:00 GMT

Good qUESTIONS..I SAW THIS TYPE QUESTIONS VERY RARE

RE: Default Backup Directory

Duncan Pryde — Wed, 23 Feb 2011 01:50:57 GMT

[quote][b]James Lean (2/23/2011)[/b][hr]You [b]CAN[/b] change the default backup directory using SSMS very easily:-- From Object Explorer, right-click on the server and choose Facets.- In the Facet dropdown, choose Server Settings.- Change the BackupDirectory to your chosen folder.No server restart is necessary. You can verify the change by checking the registry key.[/quote]Oh dear. I had no idea about that. I'd always used the registry in the past as that was the only way I could find to do it, and BOL seemed to back me up.I'm not sure if anything can be done to fix the question, but in the meantime I can only apologise for the incorrect answer and explanation.Duncan

RE: Default Backup Directory

Marco V — Wed, 23 Feb 2011 01:44:43 GMT

[quote][b]James Lean (2/23/2011)[/b][hr]You [b]CAN[/b] change the default backup directory using SSMS very easily:-- From Object Explorer, right-click on the server and choose Facets.- In the Facet dropdown, choose Server Settings.- Change the BackupDirectory to your chosen folder.No server restart is necessary. You can verify the change by checking the registry key.[/quote]James is correct in SSMS 2008 you can change in it. So unless stated clearly in the question its not SQL 2008 its not valid imho

RE: Default Backup Directory

Duncan Pryde — Wed, 23 Feb 2011 01:29:26 GMT

Thanks for the positive responses, and a special thanks to SQL Super Star for the additional code.

RE: Default Backup Directory

James Lean — Wed, 23 Feb 2011 01:28:33 GMT

You [b]CAN[/b] change the default backup directory using SSMS very easily:-- From Object Explorer, right-click on the server and choose Facets.- In the Facet dropdown, choose Server Settings.- Change the BackupDirectory to your chosen folder.No server restart is necessary. You can verify the change by checking the registry key.

RE: Default Backup Directory

Koen Verbeeck — Wed, 23 Feb 2011 00:07:08 GMT

Nice question, thanks.

RE: Default Backup Directory

mohammed moinudheen — Tue, 22 Feb 2011 23:53:28 GMT

Good question. Thank you.

RE: Default Backup Directory

Sree Arjun Div — Tue, 22 Feb 2011 23:47:20 GMT

GOOD MORNING ..MORNING I LEARNED NEW TOPIC THANKS..Editor: Removed material copied from another source.

RE: Default Backup Directory

Gobikannan — Tue, 22 Feb 2011 23:47:04 GMT

Nice and simple question.

RE: Default Backup Directory

tejaswini.patil — Tue, 22 Feb 2011 23:27:29 GMT

Yuppy:-)I am the first one to answer. And got it right too.This was a learning for me. I googled a bit to get information about this.This url is having the info:http://www.mssqltips.com/tip.asp?tip=1583Thanks for good question.Regards

Default Backup Directory

Duncan Pryde — Tue, 22 Feb 2011 23:20:07 GMT

Comments posted to this topic are about the item [B]Default Backup Directory[/B]