Overview of SSMS-E Webcast URLs and Code Snippets

As part of my web cast, there are several URLs and sample scripts that I’d like to share with you.

Demo 0 – Downloading.

The URL that I use to find the download site for SQL Server Express is to start at:

https://msdn.microsoft.com/sql/. It’s not direct, but it gets you close. Over in the right

column there is a link to the Express download. To get up and running with SQL

Server Management Studio Express, you only need to download the 38.5 meg

option – assuming that you already have the .NET Framework 2.0 installed. If you

want just about everything, download the “Install Microsoft SQL Server 2005

Express Edition with Advanced Services” 234 meg option.

Demo 4 – Templates

This is the starting script for the template to view backup history.

Use msdb;

GO

SELECT     backup_finish_date, name, user_name, backup_size

FROM         backupset

WHERE     (database_name = 'Pubs')

ORDER BY backup_finish_date DESC;

GO

I then change the script to include the parameter for templates.

Use msdb;

GO

SELECT     backup_finish_date, name, user_name, backup_size

FROM         backupset

WHERE     (database_name = '<Database_Name, sysname, Database_Name>')

ORDER BY backup_finish_date DESC;

GO

Demo 5 – User Instances

In preparing for the demonstration, I went to the readme file for SSMS-E and

discovered the query for determining if a user instance is running was wrong. The

correct query is as follows:

Use Master;

GO

Select owning_principal_name, instance_pipe_name from sys.dm_os_child_instances;

GO

To actually get the User Instance started, I needed to use the SSEUtil command

line program. You can find SSEUtil at:

https://www.microsoft.com/downloads/details.aspx?familyid=FA87E828-173F-472E-A85C-27ED01CF6B02&displaylang=en. The command

Sseutil -l

Lists the databases installed with the User Instance.

To attach the Northwind database, I used the following command line.

sseutil -a northwnd.mdf Northwind

The –A switch is for the attach command. The MDF file follows – in this case – it

happens to be in the current directory. The final value on the command line is the

name of the database.

Demo 6 – Life without SQL Agent

To use SQLCMD to run a backup script, you can use the following on the command

line.

Sqlcmd –S .\sqlexpress –i “backup pubs.sql”

The –S switch specifies the server name and the –i switch is used for the input file.

When running with the windows scheduler, you will need the full path specifications

for SQLCMD and the script file. This is what I used.

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S .\sqlexpress -i "c:\demo\Backup Pubs.sql"

Cheers,

Bill