x

Getting Basic Info via PowerShell

Hey gang,

Anyone have a snippet to retrieve the default backup and data folders using PowerShell?

Thanks!
more ▼

asked Apr 29, 2011 at 08:14 AM in Default

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

I'll give the answer to @sqlaj but I'd still like to know if others get a blank on default installs or if the variables are populated.
May 02, 2011 at 01:39 PM Blackhawk-17
@Blackhawk-17 I get the backup directory populated but other 2 are blanks.
May 02, 2011 at 02:25 PM Oleg
@Oleg - if you change them in SSMS are they populated then? And if you change them back to the original defaults are they still populated (mine are)?
May 03, 2011 at 04:56 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

See if this helps. You can paste this into Powereshell ISE and run.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.reflection.assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
$instance = gc env:computername #get the computername
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instance
$settings = $s.Settings
$bkdir = $s.Settings.BackupDirectory #gets the backup directory 
$datafldr = $s.Settings.DefaultFile #gets the data directory 
$Logflder = $s.Settings.DefaultLog #gets the log directory 

$bkdir
$datafldr
$Logflder
more ▼

answered May 01, 2011 at 09:07 AM

sqlaj 1 gravatar image

sqlaj 1
5.4k 1 4

Weird... it appears that Settings.DefaultFile and Settings.DefaultLog aren't "populated" until you actually set them to something. Can someone else try the code on a fresh install and see if they get the same experience of blank results?
May 02, 2011 at 06:42 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

We have drive and configuration standards for all SQL installs. By default SQL uses the install directory.

Do you have databases on the server? Can you create a database within SSMS? Where does it put the data and log files?

Here is a link to MSDN about default installation, may be of some help. http://msdn.microsoft.com/en-us/library/ms143547.aspx
more ▼

answered May 02, 2011 at 08:42 AM

sqlaj 1 gravatar image

sqlaj 1
5.4k 1 4

I support many different Clients and, between various standards and legacy installs, I can not be assured that the default will be the correct location. Often the data files are on one SAN LUN under a Data directory and the log files are on a different LUN under Logs.
May 02, 2011 at 10:15 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Apr 29, 2011 at 08:34 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

Getting the backup folder returned no problem... The data and log entries are coming back empty.
Apr 30, 2011 at 10:01 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x28

asked: Apr 29, 2011 at 08:14 AM

Seen: 1398 times

Last Updated: Apr 29, 2011 at 08:14 AM