THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Finding Your Default File Locations in SMO

I love the fact that you can get information about your SQL Server installations from SMO, and then you can use that information in scripts. This allows you to create one script for some function, like to create a database, for example, and run it on any server and you know it'll just run.

There are two places where SMO stores database file default location. One is for the system database and log files, and it's in the Information collection of the Server object. The properties are MasterDBPath and MasterDBLogPath, respectively. The other is in the Settings collection of the Server object, and the properties are DefaultFile and DefaultLog, respectively.

Here's the problem: when the default location for the user database files is the same as the system database files, the DefaultFile and DefaultLog properties are never initialized. If you change them (using Management Studio or via the registry values) then the properties will be populated, but if you don't the values will be empty.

Using a PowerShell script to get this information, we can accommodate this issue by script like this:

	[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null

	$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'SQLTBWS\INST01'
	$fileloc = $s.Settings.DefaultFile
	$logloc = $s.Settings.DefaultLog
	if ($fileloc.Length -eq 0) {
	    $fileloc = $s.Information.MasterDBPath
	    }
	if ($logloc.Length -eq 0) {
	    $logloc = $s.Information.MasterDBLogPath
	    }

At this point the $fileloc variable contains a string object with the name of the default file location, and the $logloc variable contains a string object with the name of the default log file location.

The SQL Server dev team is going to make a change in SMO so that DefaultFile and DefaultLog are properly populated, but until that's available this code will successfully return the locations you need in your scripts.

Allen

Published Thursday, February 19, 2009 1:31 PM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Bernd Eckenfels said:

Oh cool, thanks for that. I had a CMD script to create a Database, but since I was not able to detect the default installation, it was hard to specify/optimize storage parameters (without user intervention). With your tip I should be able to rewrite the command line in a scripting language (powershell is not yet available on all systems we target).

February 19, 2009 7:56 PM
 

ALZDBA said:

Hey Allen, that's a real cool finding.

No more messing around the ugly registry keys :))

It does it four you.

Once again, thank you for the tip.

Johan

February 26, 2009 12:15 PM
 

Reg said:

Thanks for the hint.  I was going down the registry route but went with SMO in the end and this tip was really useful.  Thanks.

April 18, 2009 8:44 AM
 

Prakash Heda said:

Nice trick...thanks for sharing right method...rest are all so complicated methods...

February 16, 2012 3:23 PM
 

Chris Magnuson said:

Thank you for this tip, exactly what I needed.

December 19, 2012 12:20 PM
 

steve sofar said:

Hello

Very interesting code

I'm newby in Powershell

Is there a way to define a list of SQL Servers in a txt file eg : SQL-Servers.txt  and specifying the SQLServer the SQL authentication like following And to loop into it to execute your magic powershell script

eg :SQL-Servers.txt

SERVER1 sa only4admin

SERVER2 sa Power

SERVER3 test dbatest

thanks a lot

March 12, 2013 2:01 PM
 

Backup-Zip-Copy-Unzip-Restore SQL Server Databases | Kaizen - Continuous Improvement said:

November 16, 2014 1:20 PM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement