THE SQL Server Blog Spot on the Web

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

Allen White

PASS Demonstration Errors

Errors occur. (I'm sure that's someone's corollary to Stuff Happens.) Errors occur, and that's why we write error handling code in our applications.

My least favorite time for the occurrance of errors is when I'm doing demos, and that's what happened in a presentation I did at PASS last week. I try to test everything before giving the demos, but every so often something slips through the cracks, and that happened. :-(

Two things that I'd blogged about here (SQL 2008 SMO - Doing a Little Rearranging) came back and bit me, because I'd forgotten about them. In a nutshell, the Backup/Restore objects have been moved to Microsoft.SqlServer.SmoExtended.dll and the WMI objects have been moved to Microsoft.SqlServer.SqlWmiManagement.dll. The appropriate DLL has to be loaded in the PowerShell script before the objects contained in those DLLs can be referenced.

If you download my demo scripts from the PASS website, know that they're configured to be run from a workstation where SQL Server 2008 tools have been installed. They'll work against SQL Server 2000, 2005 and 2008. If you want to use them from a workstation with SQL Server 2005 tools, then delete the lines reference the aforementioned DLLs and they'll run fine.

One other problem occurred with my createdb.ps1 script, which is a condition that's occurred since I started working with SMO from PowerShell. In the Server object there's a collection called Settings. In that collection are properties for DefaultData and DefaultLog. These properties are very convenient for finding where your data and log files are supposed to go when you're adding files to databases.

For some reason, these properties are set to null, or an empty string, when the server is installed. The only way I've found to set them to their proper values is to use the Server Properties dialog in Management Studio, go to the Database Settings page and change them to something very different than what's there, then go back and set them back again. Once that's done the properties return the correct values in PowerShell and the createdb.ps1 script I use in my demos works just fine.

I've updated the slides and demos available for download from the PASS site, so grab those updated files and good luck with your testing.


Published Monday, November 24, 2008 12:37 PM by AllenMWhite



a.m. said:

I had a little demo issue as well in one of my talks.  I spent a few minutes explaining why you -absolutely must- run the selected line of T-SQL before moving on to the rest of the solution, and talked about how the errors that will result if you don't run the line are annoyingly difficult to debug and make no sense...  And then I moved on to the rest of the solution, and an error hit.  And I had no clue what had gone wrong or why until 10 minutes -after- the session was over, when it suddenly hit me--I'd never actually run the line of code I had selected.  Doh!

November 24, 2008 1:05 PM

ALZDBA said:

I would have tried to blame it to the recording software of the VM :))

We always set DefaultData and DefaultLog explicitely using this T-sql script:


-- ALZDBA dd 20060622

-- Alter Local Default Backup Directory

-- xp_fixeddrives

Declare @NewDrive nchar(1)

Declare @RootDirectory2Add nvarchar(512)

Declare @NewDirectoryPath nvarchar(512)

Declare @Override char(1)

select @NewDrive = N'xxxx' -- ADJUST

, @RootDirectory2Add = ''

, @NewDirectoryPath = '' -- default blanc ! -- 'J:\MSSQL.2\MSSQL\Backup'  -->@Override = Y needed !!

, @Override = upper('N')

set nocount on

declare @CmdShellSettingBefore table (cfgname varchar(128), minimum varchar(128), maximum varchar(128), config_value varchar(128), runvalue varchar(128))

insert into @CmdShellSettingBefore

EXEC sys.sp_configure N'xp_cmdshell'

if exists(select * from @CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')


EXEC sys.sp_configure N'xp_cmdshell', N'1'



-- get Default-path

declare @DefaultBackupDirectory nvarchar(512)

exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @DefaultBackupDirectory OUTPUT;

if @DefaultBackupDirectory like '_:\MSSQL.%'

or @Override = 'Y'


   if @NewDrive = N'' set @NewDrive = substring(@DefaultBackupDirectory,1,1)

   print N'-- OLD path ' + @DefaultBackupDirectory

if @Override = 'Y'

and @NewDirectoryPath != ''


  set @DefaultBackupDirectory = @RootDirectory2Add




select @DefaultBackupDirectory = @NewDrive + substring(@DefaultBackupDirectory,2,2 )

+ @RootDirectory2Add

+ substring(@DefaultBackupDirectory,charindex(@DefaultBackupDirectory,':') + 3, datalength(@DefaultBackupDirectory))


select @DefaultBackupDirectory = replace(@DefaultBackupDirectory, '\\','\')

   declare @DOSCmd nvarchar(4000)

select @DOSCmd = N'if not exist "' + @DefaultBackupDirectory + N'" md "' + @DefaultBackupDirectory + N'"'

--print @DOSCmd

exec master..xp_cmdshell @DOSCmd, no_output

print '-- New Default Backup Directory'

print @DefaultBackupDirectory

exec master.dbo.xp_instance_regwrite  N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', REG_SZ, @DefaultBackupDirectory




print ' '

print '-- Standard Default Backup Directory has already been modified, use @Override=Y '

print @DefaultBackupDirectory

print '-- Default Backup Directory NOT altered !'


-- disable cmdshell if you enabled it

if exists(select * from @CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')


EXEC sys.sp_configure N'xp_cmdshell', N'0'




Thanks for elaborating on it and correcting the code.



November 25, 2008 6:16 AM

AllenMWhite said:

Well, it's too bad you couldn't come to my session this year, Johan.  (Perhaps I'll do it for you in Hamburg!)

I think that if the value is blank I can pull it from the server's Information collection in the MasterDBPath property.  I just have to set up the logic to do that.  (I don't like the fact that they leave Settings.DefaultData blank if it matches Information.MasterDBPath, that's all.)

PowerShell is probably a cleaner solution to your script, just because of the ugliness of using xp_regread and xp_cmdshell in T-SQL to get to the properties you need.  (Dan Jones said as much during our panel discussion on DBA Automation at PASS.)

See you soon, my friend.


November 25, 2008 7:59 AM

ALZDBA said:

Indeed, much depends on how you approach the surface.

I do much of my stuff using T-sql because that's the way I know and - even using things like xp_cmdshell in a sound way (only in admin scripts) - feel rather comfortable with.

And even though I've taken my first little steps with ps, it takes a while to get to the same level of (scripting) confidence.

But its strength is obvious.

I'll keep in touch for Hamburg.


November 25, 2008 9:49 AM
New Comments to this post are disabled

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


Privacy Statement