Enterprise Library 5.0 – Dev Guide

http://entlib.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=35832

Release Notes

Zen and the Art of Managing Crosscutting Concerns in Enterprise Software Development

A Guide to Developing with Enterprise Library 5.0 (C# Edition) – PREVIEW

Visual Studio 2008 and .NET Framework 3.5 Training Kit

http://www.microsoft.com/downloads/details.aspx?FamilyID=8BDAA836-0BBA-4393-94DB-6C3C4A0C98A1&displaylang=en

The Visual Studio 2008 and .NET Framework 3.5 Training Kit includes presentations, hands-on labs, and demos. This content is designed to help you learn how to utilize the Visual Studio 2008 features and a variety of framework technologies including: LINQ, C# 3.0, Visual Basic 9, WCF, WF, WPF, ASP.NET AJAX, VSTO, CardSpace, SilverLight, Mobile and Application Lifecycle Management.

Visual Studio 2010 and .NET Framework 4 Training Course

http://channel9.msdn.com/learn/courses/vs2010/

The Visual Studio 2010 and .NET Framework 4 Training Course includes videos and hands-on-labs designed to help you learn how to utilize the Visual Studio 2010 features and a variety of framework technologies including: C# 4.0, Visual Basic 10, F#, Parallel Computing Platform, WCF, WF, WPF, ASP.NET AJAX 4.0, ASP.NET MVC Dynamic Data.

Visual Studio 2010 and .NET Framework 4 Training Kit – October Preview

http://www.microsoft.com/downloads/details.aspx?familyid=752CB725-969B-4732-A383-ED5740F02E93&displaylang=en

The Visual Studio 2010 and .NET Framework 4 Training Kit includes presentations, hands-on labs, and demos. This content is designed to help you learn how to utilize the Visual Studio 2010 features and a variety of framework technologies including:

• C# 4.0
• Visual Basic 10
• F#
• Parallel Extensions
• Windows Communication Foundation
• Windows Workflow
• Windows Presentation Foundation
• ASP.NET 4
• Windows 7
• Entity Framework
• ADO.NET Data Services
• Managed Extensibility Framework
• Visual Studio Team System

This version of the Training Kit works with Visual Studio 2010 Beta 2 and .NET Framework 4 Beta 2.

Dynamic/Conditional Order By Clause in SQL Server/T-SQL

http://www.dominicpettifer.co.uk/Blog/21/dynamic-conditional-order-by-clause-in-sql-server-t-sql

Technical Article – This article shows you how to achieve a conditional Order By clause in your SQL Server Stored Procedures, without having to resort to inline SQL in your applications, or Dynamic SQL (yuck!) in your Stored Procedures.

Inline SQL or Stored Procedures

You’ve probably come across times when you needed to sort a recordset dynamically in a Stored Procedure based on an input parameter. For instance, if you’re displaying records on a webpage with sortable columns, like on most ecommerce sites. You could use an inline SQL statement dynamically built in your ASP/PHP server-side code with string concatenation eg:

string sql = "SELECT * FROM Products ORDER BY ";

if(Request.QueryString["orderBy"] == "NameAsc")
{
sql += "Name ASC";
}
else if(Request.QueryString["orderBy"] == "NameDesc")
{
sql += "Name DESC";
}
else if(......etc etc

However, you’re probably using a Stored Procedure for reasons such as performance (execution plan is cached in a Sproc), maintainability (string concatenation can get messy with complex queries), prevent security vulnerabilities (such as SQL Injection), and maybe you want to achieve efficient database tier paging using SQL Server 2005’s ROW_NUMBER() function.

Introducing the CASE Function

The trick is to use the CASE function, but there are quirks with this that can trip you up. First of all you declare a Stored Procedure with an Order By input parameter and apply the CASE to the Order By clause like this:

CREATE PROCEDURE GetProducts
(
@OrderBy      VARCHAR(50),
@Input2       VARCHAR(30)
)
AS
BEGIN
SET NOCOUNT ON

SELECT Id, ProductName, Description, Price, Quantity
FROM Products
WHERE ProductName LIKE @Input2
ORDER BY
CASE
WHEN @OrderBy = 'ProductNameAsc' THEN ProductName
END ASC,
CASE
WHEN @OrderBy = 'ProductNameDesc' THEN ProductName
END DESC

END

Ascending and Descending actions need to be grouped into separate CASE statements, separated with a comma. In your server-side code/script make sure to append ‘Asc’ or ‘Desc’ onto the order by string, or you could have two Stored procedure input parameters for column name and order by direction if you want.

Multiple Columns with Different DataTypes

You’ll hit problems when you try to include multiple columns with different data types (VARCHAR, INT etc.). Eg:

ORDER BY
CASE
WHEN @OrderBy = 'ProductNameAsc' THEN ProductName
WHEN @OrderBy = 'QuantityAsc' THEN Quantity
END ASC

…will throw an error because ProductName is a VARCHAR and Quantity is an INT…

Conversion failed when converting the nvarchar value ‘Value’ to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.

I’ve seen people wrap the column around a convert function eg.

WHEN @OrderBy = 'QuantityAsc' THEN CONVERT(VARCHAR(32), Quantity)

DON’T do this as the order by will be different as a string versus it’s original data type (eg. 30 will come before 4 as the first character 3 is earlier in the alphebet then 4). This is especially true with DATETIME types as the CONVERT function could convert to into any number of date time formats (2009/01/15, 01/15/2009 etc.)

Instead you have to separate each datatype into separate groups of case statements like this:

SELECT Id, ProductName, Description, Price, Quantity
FROM Products
WHERE ProductName LIKE @Input2
ORDER BY
CASE –- VARCHAR types ascending
WHEN @OrderBy = 'ProductNameAsc' THEN ProductName
END ASC,
CASE –- VARCHAR types descending
WHEN @OrderBy = 'ProductNameDesc' THEN ProductName
END DESC,
CASE -- INT types ascending
WHEN @OrderBy = 'QuantityAsc' THEN Quantity
END ASC,
CASE -- INT types descending
WHEN @OrderBy = 'QuantityDesc' THEN Quantity
END DESC,
CASE -- MONEY types ascending
WHEN @OrderBy = 'PriceAsc' THEN Price
END ASC,
CASE -- MONEY types descending
WHEN @OrderBy = 'PriceDesc' THEN Price
END DESC,
CASE -- Default order by
WHEN 1 = 1 THEN ProductName
END ASC

Remember to provide a default Order By for when none of the CASE statements match.

Paging With ROW_NUMBER()

You can also use this technique with database tier paging using the ROW_NUMBER() function in SQL Server 2005/2008:

SELECT
Id,
ProductName,
Deacription,
Quantity
FROM
(
SELECT ROW_NUMBER() OVER(
ORDER BY
CASE
WHEN @OrderBy = 'ProductNameAsc' THEN ProductName
END ASC,
CASE
WHEN @OrderBy = 'ProductNameDesc' THEN ProductName
END DESC,
CASE
WHEN 1 = 1 THEN ProductName
END ASC,
//-- Snip --//
) AS RowNumber,
Id,
ProductName,
Description,
Price,
Quantity
FROM Products
WHERE ProductName LIKE @Input2
) i
WHERE RowNmber BETWEEN @FirstRecord AND @LastRecord
ORDER BY RowNumber ASC

Case in Where Clause

http://p2p.wrox.com/sql-server-2000/832-case-where-clause.html

Hi everybody
Can I use case statement in WHERE clause.
For example

DECLARE @VAR1 nvarchar(50)
DECLARE @VAR2 nvarchar(50)
SELECT * FROM ORDERS
WHERE CASE @VAR1
          WHEN ‘Customers’ THEN CustomerID = @VAR2
          WHEN ‘Employee’ THEN EmployeeID = @VAR2
          END

This is not working OK.
Can someone explain me the right way?
Thanks.
Best regards
Alex

 

In your situation, where you wish to select on two entirely different columns depending on a parameter, it is probably better to use an IF/ELSE construction, as:

Code:

IF @Var1='Customers' THEN
   SELECT * FROM Orders WHERE CustomerID=@Var2;
ELSE
   SELECT * FROM Orders WHERE EmployeeID=@Var2;

It’s important to realize that CASE is an expression and not an executable statement.

The general form of the predicate of a WHERE clause is:

Code:

 expression operator expression

where operator is = or <> or > or < etc. The WHERE clause in your query does not follow this form, which is why you were having problems.

If you insist on using the CASE expression, then you can try a couple of ugly alternatives, like:

Code:

   SELECT * FROM Orders
    WHERE CustomerID=CASE @Var1 WHEN 'Customers' THEN @Var2 ELSE 0 END
       OR EmployeeID=CASE @Var1 WHEN 'Employees' THEN @Var2 ELSE 0 END

assuming that 0 is not a legal value for either ‘CustomerID’ or ‘EmployeeID’. You could also try a nested CASE like:

Code:

   SELECT * FROM Orders
    WHERE 1 =
      CASE @Var1
         WHEN 'Customers' THEN CASE WHEN CustomerID=@Var2 THEN 1 ELSE 0 END
         WHEN 'Employees' THEN CASE WHEN EmployeeID=@Var2 THEN 1 ELSE 0 END
      END

I think that the CASE expression doesn’t work well in this situation and that the IF/ELSE is simpler and easier to understand.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I think this should work for you…

SELECT * FROM ORDERS
WHERE
(
  CASE
    WHEN @VAR1 = ‘Customers’ AND CustomerID = @VAR2 THEN 1
    WHEN @VAR1 = ‘Employee’ AND EmployeeID = @VAR2 THEN 1
    ELSE 0
  END
) = 1

or you could do this …

SELECT *
FROM ORDERS
WHERE (@VAR1 = ‘Customers’ and CustomerID = @VAR2) OR
      (@VAR1 = ‘Employee’ and EmployeeID = @VAR2)

Jeff’s first answer is best (though it doesn’t handle an illegal value for @VAR1) and SBacon’s answer is a close second, but Jeff’s other answers and Craig’s answer are all overkill.

Why not just:

Code:

DECLARE @VAR1 nvarchar(50)
DECLARE @VAR2 nvarchar(50)
SELECT * FROM ORDERS
WHERE @VAR2 = ( CASE @VAR1
                WHEN 'Customers' THEN CustomerID
                WHEN 'Employee'  THEN EmployeeID
                ELSE -1
                END )

The ELSE is in there to protect against an illegal value for @VAR1 of course. Jeff’s first answer is best (though it doesn’t handle an illegal value for @VAR1) and SBacon’s answer is a close second, but Jeff’s other answers and Craig’s answer are all overkill.

Why not just:

Code:

DECLARE @VAR1 nvarchar(50)
DECLARE @VAR2 nvarchar(50)
SELECT * FROM ORDERS
WHERE @VAR2 = ( CASE @VAR1
                WHEN 'Customers' THEN CustomerID
                WHEN 'Employee'  THEN EmployeeID
                ELSE -1
                END )

The ELSE is in there to protect against an illegal value for @VAR1 of course.

 

Quote:

quote:Originally posted by Old Pedant  …Jeff’s other answers … are all overkill.

Well… I did say they were “…a couple of ugly alternatives…”

(Any idea why we’re responding to a 5 year old original post? )

Jeff Mason
je.mason@comcast.net

> (Any idea why we’re responding to a 5 year old original post?

Ummm…because I inanely didn’t look at the original message date and because SBacon is a complete newbie and how the heck did he *find* this thread in the first place and… Well, maybe Google sent him this way??? But then why is he responding instead of asking? And… Ehhh, leave it at “because I inanely…”.

Windows PowerShell with Sql Server

http://www.mssqltips.com/category.asp?catid=81

Backup SQL Server Databases with a Windows PowerShell Script In a previous tip on Backup and Restore SQL Server databases programmatically with SMO, you’ve seen how you can use Windows PowerShell to backup and restore SQL Server databases. In this tip, I will cover how to use Windows PowerShell to generate SQL Server backups.Generating SQL Scripts using Windows PowerShell In a previous tip on SQL Script Generation Programmatically with SMO, you have seen how you can use SMO to generate SQL scripts programmatically. In this tip I will cover how to generate scripts programmatically using Windows PowerShell. Checking SQL Server Agent jobs using Windows PowerShell Checking for SQL Server Agent jobs and their status is part of your daily task as a DBA. How do we use Windows PowerShell to check for SQL Server Agent jobs? Check the Last SQL Server Backup Date using Windows PowerShell In a previous tip, Retrieve List of Databases and their Properties using PowerShell, you have seen how you can use Windows PowerShell to audit database properties and use Microsoft Excel to generate a report. Part of the daily SQL Server DBA tasks is to check the backups of all the databases in a SQL Server instance. How do we use Windows PowerShell to check for the last backup date of SQL Server databases and to confirm that they still meet our service level agreement? Retrieve a List of SQL Server Databases and their Properties using PowerShell In a previous tip on using Using PowerShell with SQL Server Management Objects (SMO), you have seen how you can use Windows PowerShell and SMO to administer SQL Server databases. In this tip I translate some of the Transact-SQL scripts that I use every day to PowerShell scripts, starting with the simple ones like retrieving a list of databases and their properties for auditing purposes. Using PowerShell with SQL Server Management Objects (SMO) I have been hearing about Microsoft’s scripting language called Windows PowerShell which is extensively used nowadays for administering and managing Windows servers and workstations. Exchange Server 2007 and Windows Server 2008 are just two of the Microsoft server products that have included this as part of their administrative tools. I want to know how Windows PowerShell can help me as a SQL Server DBA.Introduction to Windows PowerShell for the SQL Server DBA Part 2 I have been hearing about Microsoft’s scripting language called Windows PowerShell which is extensively used nowadays for administering and managing Windows servers and workstations. Exchange Server 2007 and Windows Server 2008 are just two of the Microsoft server products that have included this as part of their administrative tools. I want to know how Windows PowerShell can help me as a SQL Server DBA. PowerShell Support in SQL Server 2008 – Invoke-Sqlcmd Cmdlet In my organization we write scripts to perform all sorts of administrative functions on our many SQL Server instances as well as other server products. We are just getting started with using PowerShell and have heard that SQL Server 2008 provides some built-in support for executing T-SQL commands from within PowerShell. Can you provide some details and examples? Introduction to Windows PowerShell for the SQL Server DBA Part 1 I have been hearing about Microsoft’s scripting language called Windows PowerShell which is extensively used nowdays for administering and managing Windows servers and workstations. Exchange Server 2007 and Windows Server 2008 are just two of the Microsoft server products that have included this as part of their administrative tools. I want to know how Windows PowerShell can help me as a SQL Server DBA.

Backup and Restore SQL Server databases programmatically with SMO

http://www.mssqltips.com/tip.asp?tip=1849

Problem
In my last set of tips, I discussed SMO at a basic level.  In this tip I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases with SMO.  I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how to restore them when required programmatically using SMO.

Solution
As I discussed in my last tip, SMO provides utility classes for specific tasks. For backup and restore, it provides two main utility classes (Backup and Restore) which are available in Microsoft.SqlServer.Management.Smo namespace.

Before you start writing SMO code, you need to reference several assemblies which contain the SMO namespaces. For more details on these assemblies and how properly to reference them in your code, refer to my tip Getting started with SQL Server Management Objects (SMO).

Examples

C# Code Block 1 – Full Backups – This example shows how to issue full database backups with SMO. First, create an instance of the Backup class and set the associated properties. With the Action property you can specify the type of backup such as full, files or log backup. With the Database property specify the name of the database being backed up.  The device is the backup media type such as disk or tape, so you need to add a device (one or more) to the Devices collection of backup instance. With the BackupSetName and BackupSetDescription properties you can specify the name and description for the backup set.  The Backup class also has a property called ExpirationDate which indicates how long backup data is considered valid and to expire the backup after that date. The backup object instance generates several events during the backup operation, we can write event-handlers for these events and wire them up with events. This is what I am doing for progress monitoring.  I am wiring up CompletionStatusInPercent and Backup_Completed methods (event-handlers) with PercentComplete and Complete events of backup object instance.  Finally, I am calling the SqlBackup method for starting up the backup operation, SMO provides a variant of this method called SqlBackupAsync if you want to start the backup operation asynchronously.

C# Code Block 1 – Full Database Backup

Backup bkpDBFull = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBFull.Action = BackupActionType.Database; /* Specify the name of the database to back up */ bkpDBFull.Database = myDatabase.Name; /* You can take backup on several media type (disk or tape), here I am  * using File type and storing backup on the file system */ bkpDBFull.Devices.AddDevice(@”D:\AdventureWorksFull.bak”, DeviceType.File); bkpDBFull.BackupSetName = “Adventureworks database Backup”; bkpDBFull.BackupSetDescription = “Adventureworks database – Full Backup”; /* You can specify the expiration date for your backup data  * after that date backup data would not be relevant */ bkpDBFull.ExpirationDate = DateTime.Today.AddDays(10);   /* You can specify Initialize = false (default) to create a new  * backup set which will be appended as last backup set on the media. You  * can specify Initialize = true to make the backup as first set on the  * medium and to overwrite any other existing backup sets if the all the  * backup sets have expired and specified backup set name matches with  * the name on the medium */ bkpDBFull.Initialize = false;   /* Wiring up events for progress monitoring */ bkpDBFull.PercentComplete += CompletionStatusInPercent; bkpDBFull.Complete += Backup_Completed;   /* SqlBackup method starts to take back up  * You can also use SqlBackupAsync method to perform the backup  * operation asynchronously */ bkpDBFull.SqlBackup(myServer);

private static void CompletionStatusInPercent(object sender, PercentCompleteEventArgs args) {     Console.Clear();     Console.WriteLine(“Percent completed: {0}%.”, args.Percent); } private static void Backup_Completed(object sender, ServerMessageEventArgs args) {     Console.WriteLine(“Hurray…Backup completed.” );     Console.WriteLine(args.Error.Message); } private static void Restore_Completed(object sender, ServerMessageEventArgs args) {     Console.WriteLine(“Hurray…Restore completed.”);     Console.WriteLine(args.Error.Message); }

Result:

C# Code Block 2 Differential Backups - The process of issuing differential backups is not much different from issuing full backups. To issue a differential backup, set the property Incremental = true. If you set this property the incremental/differential backup will be taken since last full backup.

C# Code Block 2 – Differential Database Backup

Backup bkpDBDifferential = new Backup(); /* Specify whether you want to backup database, files or log */ bkpDBDifferential.Action = BackupActionType.Database; /* Specify the name of the database to backup */ bkpDBDifferential.Database = myDatabase.Name; /* You can issue backups on several media types (disk or tape), here I am * using the File type and storing the backup on the file system */ bkpDBDifferential.Devices.AddDevice(@”D:\AdventureWorksDifferential.bak”, DeviceType.File); bkpDBDifferential.BackupSetName = “Adventureworks database Backup”; bkpDBDifferential.BackupSetDescription = “Adventureworks database – Differential Backup”; /* You can specify the expiration date for your backup data  * after that date backup data would not be relevant */ bkpDBDifferential.ExpirationDate = DateTime.Today.AddDays(10);   /* You can specify Initialize = false (default) to create a new  * backup set which will be appended as last backup set on the media.  * You can specify Initialize = true to make the backup as the first set  * on the medium and to overwrite any other existing backup sets if the  * backup sets have expired and specified backup set name matches  * with the name on the medium */ bkpDBDifferential.Initialize = false;   /* You can specify Incremental = false (default) to perform full backup  * or Incremental = true to perform differential backup since most recent  * full backup */ bkpDBDifferential.Incremental = true;   /* Wiring up events for progress monitoring */ bkpDBDifferential.PercentComplete += CompletionStatusInPercent; bkpDBDifferential.Complete += Backup_Completed;   /* SqlBackup method starts to take back up  * You cab also use SqlBackupAsync method to perform backup  * operation asynchronously */ bkpDBDifferential.SqlBackup(myServer);

Result:

C# Code Block 3 Transaction Log Backups - Again the process of issuing transactional log backup is not much different from issuing full backups. To issue transactional log backups, set the property Action = BackupActionType.Log instead of BackupActionType.Database as in the case of a full backup.

C# Code Block 3 – Transaction Log Backup

Backup bkpDBLog = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBLog.Action = BackupActionType.Log; /* Specify the name of the database to back up */ bkpDBLog.Database = myDatabase.Name; /* You can take backup on several media type (disk or tape), here I am  * using File type and storing backup on the file system */ bkpDBLog.Devices.AddDevice(@”D:\AdventureWorksLog.bak”, DeviceType.File); bkpDBLog.BackupSetName = “Adventureworks database Backup”; bkpDBLog.BackupSetDescription = “Adventureworks database – Log Backup”; /* You can specify the expiration date for your backup data  * after that date backup data would not be relevant */ bkpDBLog.ExpirationDate = DateTime.Today.AddDays(10);   /* You can specify Initialize = false (default) to create a new  * backup set which will be appended as last backup set on the media. You  * can specify Initialize = true to make the backup as first set on the  * medium and to overwrite any other existing backup sets if the all the  * backup sets have expired and specified backup set name matches with  * the name on the medium */ bkpDBLog.Initialize = false;   /* Wiring up events for progress monitoring */ bkpDBLog.PercentComplete += CompletionStatusInPercent; bkpDBLog.Complete += Backup_Completed;   /* SqlBackup method starts to take back up  * You cab also use SqlBackupAsync method to perform backup  * operation asynchronously */ bkpDBLog.SqlBackup(myServer);

Result:

C# Code Block 4 Backup with Compression - SQL Server 2008 introduces a new feature to issues backups in a compressed form.  As such, SMO for SQL Server 2008 has been enhanced to support this feature. If you look at the image below you will notice the compressed backup size is almost 25% of full backup, though the level of compression depends on the several factors.

C# Code Block 4 – Backup with Compression (SQL Server 2008)

Backup bkpDBFullWithCompression = new Backup(); /* Specify whether you want to back up database or files or log */ bkpDBFullWithCompression.Action = BackupActionType.Database; /* Specify the name of the database to back up */ bkpDBFullWithCompression.Database = myDatabase.Name; /* You can use back up compression technique of SQL Server 2008,  * specify CompressionOption property to On for compressed backup */ bkpDBFullWithCompression.CompressionOption = BackupCompressionOptions.On; bkpDBFullWithCompression.Devices.AddDevice(@”D:\AdventureWorksFullWithCompression.bak”, DeviceType.File); bkpDBFullWithCompression.BackupSetName = “Adventureworks database Backup – Compressed”; bkpDBFullWithCompression.BackupSetDescription = “Adventureworks database – Full Backup with Compressin – only in SQL Server 2008″; bkpDBFullWithCompression.SqlBackup(myServer);

Result:

C# Code Block 5 Full or Differential Restores – Thus far we have worked through SMO backup examples. Now let’s change gears to restore with SMO.  SMO provides a Restore class to restore a database, similar to the Backup class.  With these classes it is necessary to specify the Action property to indicate the type of restore i.e. database, files or log.  In a scenario where if you have additional differential or log backups to be restored after it is necessary to specify the NoRecovery = true except for the final restore.  In this example, I am wiring up events of the Restore object instance to event-handlers for progress monitoring. Finally the SqlRestore method is called to start the restoration. If you want to start the restore operation asynchronously you would need to call SqlRestoreAsync method instead.

C# Code Block 5 – Database Restore – Full or Differential

Restore restoreDB = new Restore(); restoreDB.Database = myDatabase.Name; /* Specify whether you want to restore database, files or log */ restoreDB.Action = RestoreActionType.Database; restoreDB.Devices.AddDevice(@”D:\AdventureWorksFull.bak”, DeviceType.File);   /* You can specify ReplaceDatabase = false (default) to not create a new  * database, the specified database must exist on SQL Server  * instance. If you can specify ReplaceDatabase = true to create new  * database image regardless of the existence of specified database with  * the same name */ restoreDB.ReplaceDatabase = true;    /* If you have a differential or log restore after the current restore,  * you would need to specify NoRecovery = true, this will ensure no  * recovery performed and subsequent restores are allowed. It means it  * the database will be in a restoring state. */ restoreDB.NoRecovery = true;   /* Wiring up events for progress monitoring */ restoreDB.PercentComplete += CompletionStatusInPercent; restoreDB.Complete += Restore_Completed;   /* SqlRestore method starts to restore the database  * You can also use SqlRestoreAsync method to perform restore  * operation asynchronously */ restoreDB.SqlRestore(myServer);

Result:

To restore a database SQL Server needs to acquire exclusive lock on the database being restored.  If you try to restore a database which is in use, SQL Server will throw the following exception:

C# Code Block 6 Transaction Log Restore – The process of restoring a transactional log is similar to restoring a full or differential backup. While restoring a transactional log, it is necessary to set the property Action = RestoreActionType.Log instead of RestoreActionType.Database as in case of full/differential restore.  Here is an example:

C# Code Block 6 – Database Restore – Log

Restore restoreDBLog = new Restore(); restoreDBLog.Database = myDatabase.Name; restoreDBLog.Action = RestoreActionType.Log; restoreDBLog.Devices.AddDevice(@”D:\AdventureWorksLog.bak”, DeviceType.File);   /* You can specify NoRecovery = false (default) so that transactions are  * rolled forward and recovered. */ restoreDBLog.NoRecovery = false;   /* Wiring up events for progress monitoring */ restoreDBLog.PercentComplete += CompletionStatusInPercent; restoreDBLog.Complete += Restore_Completed;   /* SqlRestore method starts to restore database  * You cab also use SqlRestoreAsync method to perform restore  * operation asynchronously */ restoreDBLog.SqlRestore(myServer);

Result:

C# Code Block 7 Database Restore to a new location – At times you need to create a new database and restore to a new physical location which differs from the original database. For that purpose, the Restore class has the RelocateFiles collection which can be completed for each file with the new location as shown in the code below.

C# Code Block 7 Database Restore – Different location

Restore restoreDB = new Restore(); restoreDB.Database = myDatabase.Name + “New”; /* Specify whether you want to restore database or files or log etc */ restoreDB.Action = RestoreActionType.Database; restoreDB.Devices.AddDevice(@”D:\AdventureWorksFull.bak”, DeviceType.File);   /* You can specify ReplaceDatabase = false (default) to not create a new  * database, the specified database must exist on SQL Server instance.  * You can specify ReplaceDatabase = true to create new database  * regardless of the existence of specified database */ restoreDB.ReplaceDatabase = true;   /* If you have a differential or log restore to be followed, you would  * specify NoRecovery = true, this will ensure no recovery is done  * after the restore and subsequent restores are completed. The database  * would be in a recovered state. */ restoreDB.NoRecovery = false;   /* RelocateFiles collection allows you to specify the logical file names  * and physical file names (new locations) if you want to restore to a  * different location.*/ restoreDB.RelocateFiles.Add(new RelocateFile(“AdventureWorks_Data”, @”D:\AdventureWorksNew_Data.mdf”)); restoreDB.RelocateFiles.Add(new RelocateFile(“AdventureWorks_Log”, @”D:\AdventureWorksNew_Log.ldf”));   /* Wiring up events for progress monitoring */ restoreDB.PercentComplete += CompletionStatusInPercent; restoreDB.Complete += Restore_Completed;   /* SqlRestore method starts to restore database  * You can also use SqlRestoreAsync method to perform restore  * operation asynchronously */ restoreDB.SqlRestore(myServer);

Result:

Complete code listing (created on SQL Server 2008 and Visual Studio 2008, though there is not much difference if you are using it on SQL Server 2005 and Visual Studio 2005) can be found in the below text box.

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
namespace BackupAndRestoreWithSMO2008
{
    class Program
    {
        static void Main(string[] args)
        {
            Server myServer = new Server(@”ARSHADALI-LAP\ARSHADALI”);
            try
            {
                //Using windows authentication
                myServer.ConnectionContext.LoginSecure = true;
                myServer.ConnectionContext.Connect();
                Database myDatabase = myServer.Databases["AdventureWorks"];
                BackupDatabaseFull(myServer, myDatabase);
                //BackupDatabaseDifferential(myServer, myDatabase);
                //BackupDatabaseLog(myServer, myDatabase);
                //BackupDatabaseFullWithCompression(myServer, myDatabase);
               
                RestoreDatabase(myServer, myDatabase);
                //RestoreDatabaseLog(myServer, myDatabase);
                //RestoreDatabaseWithDifferentNameAndLocation(myServer, myDatabase);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.InnerException.Message);
            }
            finally
            {
                if (myServer.ConnectionContext.IsOpen)
                    myServer.ConnectionContext.Disconnect();
                Console.WriteLine(“Press any key to terminate…”);
                Console.ReadKey();
            }
        }
        private static void BackupDatabaseFull(Server myServer, Database myDatabase)
        {
            Backup bkpDBFull = new Backup();
            /* Specify whether you want to back up database or files or log */
            bkpDBFull.Action = BackupActionType.Database;
            /* Specify the name of the database to back up */
            bkpDBFull.Database = myDatabase.Name;
            /* You can take backup on several media type (disk or tape), here I am using
             * File type and storing backup on the file system */
            bkpDBFull.Devices.AddDevice(@”D:\AdventureWorksFull.bak”, DeviceType.File);
            bkpDBFull.BackupSetName = “Adventureworks database Backup”;
            bkpDBFull.BackupSetDescription = “Adventureworks database – Full Backup”;
            /* You can specify the expiration date for your backup data
             * after that date backup data would not be relevant */
            bkpDBFull.ExpirationDate = DateTime.Today.AddDays(10);
            /* You can specify Initialize = false (default) to create a new
             * backup set which will be appended as last backup set on the media. You can
             * specify Initialize = true to make the backup as first set on the mediuam and
             * to overwrite any other existing backup sets if the all the backup sets have
             * expired and specified backup set name matches with the name on the medium */
            bkpDBFull.Initialize = false;
            /* Wiring up events for progress monitoring */
            bkpDBFull.PercentComplete += CompletionStatusInPercent;
            bkpDBFull.Complete += Backup_Completed;
            /* SqlBackup method starts to take back up
             * You cab also use SqlBackupAsync method to perform backup
             * operation asynchronously */
            bkpDBFull.SqlBackup(myServer);
        }
        private static void BackupDatabaseDifferential(Server myServer, Database myDatabase)
        {
            Backup bkpDBDifferential = new Backup();
            /* Specify whether you want to back up database or files or log */
            bkpDBDifferential.Action = BackupActionType.Database;
            /* Specify the name of the database to back up */
            bkpDBDifferential.Database = myDatabase.Name;
            /* You can take backup on several media type (disk or tape), here I am using
             * File type and storing backup on the file system */
            bkpDBDifferential.Devices.AddDevice(@”D:\AdventureWorksDifferential.bak”, DeviceType.File);
            bkpDBDifferential.BackupSetName = “Adventureworks database Backup”;
            bkpDBDifferential.BackupSetDescription = “Adventureworks database – Differential Backup”;
            /* You can specify the expiration date for your backup data
             * after that date backup data would not be relevant */
            bkpDBDifferential.ExpirationDate = DateTime.Today.AddDays(10);
            /* You can specify Initialize = false (default) to create a new
             * backup set which will be appended as last backup set on the media. You can
             * specify Initialize = true to make the backup as first set on the mediuam and
             * to overwrite any other existing backup sets if the all the backup sets have
             * expired and specified backup set name matches with the name on the medium */
            bkpDBDifferential.Initialize = false;
            /* You can specify Incremental = false (default) to perform full backup
             * or Incremental = true to perform differential backup since most recent
             * full backup */
            bkpDBDifferential.Incremental = true;
            /* Wiring up events for progress monitoring */
            bkpDBDifferential.PercentComplete += CompletionStatusInPercent;
            bkpDBDifferential.Complete += Backup_Completed;
            /* SqlBackup method starts to take back up
             * You cab also use SqlBackupAsync method to perform backup
             * operation asynchronously */
            bkpDBDifferential.SqlBackup(myServer);
        }
        private static void BackupDatabaseLog(Server myServer, Database myDatabase)
        {
            Backup bkpDBLog = new Backup();
            /* Specify whether you want to back up database or files or log */
            bkpDBLog.Action = BackupActionType.Log;
            /* Specify the name of the database to back up */
            bkpDBLog.Database = myDatabase.Name;
            /* You can take backup on several media type (disk or tape), here I am using
             * File type and storing backup on the file system */
            bkpDBLog.Devices.AddDevice(@”D:\AdventureWorksLog.bak”, DeviceType.File);
            bkpDBLog.BackupSetName = “Adventureworks database Backup”;
            bkpDBLog.BackupSetDescription = “Adventureworks database – Log Backup”;
            /* You can specify the expiration date for your backup data
             * after that date backup data would not be relevant */
            bkpDBLog.ExpirationDate = DateTime.Today.AddDays(10);
            /* You can specify Initialize = false (default) to create a new
             * backup set which will be appended as last backup set on the media. You can
             * specify Initialize = true to make the backup as first set on the mediuam and
             * to overwrite any other existing backup sets if the all the backup sets have
             * expired and specified backup set name matches with the name on the medium */
            bkpDBLog.Initialize = false;
            /* Wiring up events for progress monitoring */
            bkpDBLog.PercentComplete += CompletionStatusInPercent;
            bkpDBLog.Complete += Backup_Completed;
            /* SqlBackup method starts to take back up
             * You cab also use SqlBackupAsync method to perform backup
             * operation asynchronously */
            bkpDBLog.SqlBackup(myServer);
        }
        private static void BackupDatabaseFullWithCompression(Server myServer, Database myDatabase)
        {
            Backup bkpDBFullWithCompression = new Backup();
            /* Specify whether you want to back up database or files or log */
            bkpDBFullWithCompression.Action = BackupActionType.Database;
            /* Specify the name of the database to back up */
            bkpDBFullWithCompression.Database = myDatabase.Name;
            /* You can use back up compression technique of SQL Server 2008,
             * specify CompressionOption property to On for compressed backup */
            bkpDBFullWithCompression.CompressionOption = BackupCompressionOptions.On;
            bkpDBFullWithCompression.Devices.AddDevice(@”D:\AdventureWorksFullWithCompression.bak”, DeviceType.File);
            bkpDBFullWithCompression.BackupSetName = “Adventureworks database Backup – Compressed”;
            bkpDBFullWithCompression.BackupSetDescription = “Adventureworks database – Full Backup with Compressin – only in SQL Server 2008″;
            bkpDBFullWithCompression.SqlBackup(myServer);
        }
        private static void CompletionStatusInPercent(object sender, PercentCompleteEventArgs args)
        {
            Console.Clear();
            Console.WriteLine(“Percent completed: {0}%.”, args.Percent);
        }
        private static void Backup_Completed(object sender, ServerMessageEventArgs args)
        {
            Console.WriteLine(“Hurray…Backup completed.” );
            Console.WriteLine(args.Error.Message);
        }
        private static void Restore_Completed(object sender, ServerMessageEventArgs args)
        {
            Console.WriteLine(“Hurray…Restore completed.”);
            Console.WriteLine(args.Error.Message);
        }
        private static void RestoreDatabase(Server myServer, Database myDatabase)
        {
            Restore restoreDB = new Restore();
            restoreDB.Database = myDatabase.Name;
            /* Specify whether you want to restore database or files or log etc */
            restoreDB.Action = RestoreActionType.Database;
            restoreDB.Devices.AddDevice(@”D:\AdventureWorksFull.bak”, DeviceType.File);
            /* You can specify ReplaceDatabase = false (default) to not create a new image
             * of the database, the specified database must exist on SQL Server instance.
             * If you can specify ReplaceDatabase = true to create new database image
             * regardless of the existence of specified database with same name */
            restoreDB.ReplaceDatabase = true;

            /* If you have differential or log restore to be followed, you would need
             * to specify NoRecovery = true, this will ensure no recovery is done after the
             * restore and subsequent restores are allowed. It means it will database
             * in the Restoring state. */
            restoreDB.NoRecovery = true;
            /* Wiring up events for progress monitoring */
            restoreDB.PercentComplete += CompletionStatusInPercent;
            restoreDB.Complete += Restore_Completed;
            /* SqlRestore method starts to restore database
             * You cab also use SqlRestoreAsync method to perform restore
             * operation asynchronously */
            restoreDB.SqlRestore(myServer);
        }
        private static void RestoreDatabaseLog(Server myServer, Database myDatabase)
        {
            Restore restoreDBLog = new Restore();
            restoreDBLog.Database = myDatabase.Name;
            restoreDBLog.Action = RestoreActionType.Log;
            restoreDBLog.Devices.AddDevice(@”D:\AdventureWorksLog.bak”, DeviceType.File);
            /* You can specify NoRecovery = false (default) so that transactions are
             * rolled forward and recovered. */
            restoreDBLog.NoRecovery = false;
            /* Wiring up events for progress monitoring */
            restoreDBLog.PercentComplete += CompletionStatusInPercent;
            restoreDBLog.Complete += Restore_Completed;
            /* SqlRestore method starts to restore database
             * You cab also use SqlRestoreAsync method to perform restore
             * operation asynchronously */
            restoreDBLog.SqlRestore(myServer);
        }
        private static void RestoreDatabaseWithDifferentNameAndLocation(Server myServer, Database myDatabase)
        {
            Restore restoreDB = new Restore();
            restoreDB.Database = myDatabase.Name + “New”;
            /* Specify whether you want to restore database or files or log etc */
            restoreDB.Action = RestoreActionType.Database;
            restoreDB.Devices.AddDevice(@”D:\AdventureWorksFull.bak”, DeviceType.File);
            /* You can specify ReplaceDatabase = false (default) to not create a new image
             * of the database, the specified database must exist on SQL Server instance.
             * You can specify ReplaceDatabase = true to create new database image
             * regardless of the existence of specified database with same name */
            restoreDB.ReplaceDatabase = true;
            /* If you have differential or log restore to be followed, you would need
             * to specify NoRecovery = true, this will ensure no recovery is done after the
             * restore and subsequent restores are allowed. It means it will database
             * in the Restoring state. */
            restoreDB.NoRecovery = false;
            /* RelocateFiles collection allows you to specify the logical file names and
             * physical file names (new locations) if you want to restore to a different location.*/
            restoreDB.RelocateFiles.Add(new RelocateFile(“AdventureWorks_Data”, @”D:\AdventureWorksNew_Data.mdf”));
            restoreDB.RelocateFiles.Add(new RelocateFile(“AdventureWorks_Log”, @”D:\AdventureWorksNew_Log.ldf”));
            /* Wiring up events for progress monitoring */
            restoreDB.PercentComplete += CompletionStatusInPercent;
            restoreDB.Complete += Restore_Completed;
            /* SqlRestore method starts to restore database
             * You cab also use SqlRestoreAsync method to perform restore
             * operation asynchronously */
            restoreDB.SqlRestore(myServer);
        }
    }
}

Notes:

• Location of assemblies in SQL Server 2005 is the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder.
• Location of assemblies in SQL Server 2008 is the C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder.
• In SQL Server 2005, the Backup and Restore classes are available in the Microsoft.SqlServer.Management.Smo namespace and in the Microsoft.SqlServer.Smo (microsoft.sqlserver.smo.dll) assembly.
• In SQL Server 2008, the Backup and Restore classes are available in the Microsoft.SqlServer.Management.Smo namespace and in the Microsoft.SqlServer.SmoExtended (microsoft.sqlserver.smoextended.dll) assembly.
• If you are restoring a transaction log, you can specify a particular point in time with ToPointInTime property of the Restore class.
• The Restore class methods (SqlVerify, SqlVerifyAsync and SqlVerifyLatest) to verify and validate (backup set is complete and the entire backup is readable) the backup media before restoration.
• The SQL Server service account must have access to the folders where backup or restore operations are executed.
• You need to have sufficient permissions to perform backup and restore operations. For example, for backup you need to be either in sysadmin/db_owner/db_backupoperator role or must have BACKUP DATABASE or BACKUP LOG permission on the database.
• If you try to connect SQL Server 2008 from SMO 2005, you will get an exception “SQL Server <10.0> version is not supported”.

Next Steps

• Review the following tips:
  • Getting started with SQL Server Management Objects (SMO)
  • SQL Script Generation Programmatically with SMO
  • Exploring Database Backup Compression in SQL Server 2008
• Review Backup and Restore classes on MSDN.
• Review the MSSQLTips Backup and Recovery category with 40+ tips.

Readers Who Read This Tip Also Read

• How to identify when a database was restored, the source of the backup and the date of the backup
• How to Backup and Restore a SQL Server FILESTREAM Enabled Database
• Restore a SQL Server Database to a New Database to Minimize Downtime
• Mirrored Database Backup Feature in SQL Server 2005 and SQL Server 2008
• Copy Only Backup for SQL Server 2005 and SQL Server 2008
• More…

Using PowerShell with SQL Server Management Objects (SMO)

http://www.mssqltips.com/tip.asp?tip=1745

Using PowerShell with SQL Server Management Objects (SMO) Written By: Edwin Sarmiento — 5/6/2009 — 2 comments — printer friendly — become a member             Free SQL Server Book of Your Choice             Problem I have been hearing about Microsoft’s scripting language called Windows PowerShell which is extensively used nowadays for administering and managing Windows servers and workstations. Exchange Server 2007 and Windows Server 2008 are just two of the Microsoft server products that have included this as part of their administrative tools. I want to know how Windows PowerShell can help me as a SQL Server DBA. Solution To continue this series on Introduction to Windows PowerShell for the SQL Server DBA, this tip will look at writing and running PowerShell scripts. In Part 1, we have introduced Windows PowerShell, the use of cmdlets and aliases and how we can take full advantage of the help available from the environment. Part 2 talks about using the pipeline and formatting results in PowerShell. In this tip, we will start writing PowerShell scripts and introduce SQL Server Management Objects (SMO). Writing and Running PowerShell scripts Since you can now write a basic command by piping cmdlets in Windows PowerShell, it’s time to write a script. A script is a sequence of commands and PowerShell cmdlets written together to create more complex tasks. PowerShell script files should have a .PS1 extension. While you might be wondering about the “1″ in the file extension, it was intended to be for script versioning. Since PowerShell v2.0 is still in CTP, we are still not sure whether the extension would change, but currently any script with a .PS1 extension will work in PowerShell v2.0. You can write a script using any text editor like Notepad or TextPad although there are third-party scripting tools out there like PowerShell Plus from Idera and PowerGUI from Quest Software. PowerShell v2.0 CTP comes with a Graphical Windows PowerShell that you can use as your development environment for PowerShell. The choice of a development environment for Windows PowerShell is totally up to you.   You can start writing scripts by saving the commands you’ve learned in Part 1 and Part 2 on a PS1 file. To run the script, you can call it from within Windows PowerShell by specifying either its full or relative path. By default, you wouldn’t be able to execute scripts in Windows PowerShell due to the security policy.   To enable a script to run, you have to modify the execution policy within Windows PowerShell by calling the Set-ExecutionPolicy cmdlet and passing the execution policy as a parameter. Different execution policies are described here, but the recommended setting is to use RemoteSigned as this will allow you to run scripts locally but will require a digital signature from a trusted publisher on scripts that come from a remote source. After setting the execution policy, you can now run your scripts from the PowerShell console   Notice the period at the beginning of the script together with the backslash? That simply tells PowerShell to execute the script in the current scope. This only means that you have to type the complete file path in order for you to run a script or, in this particular case since the script is in the current directory, you can start it with the”.\” notation Passing Variable Parameters To A Script In order for your scripts to be dynamic, you have to find ways to pass different values to it. Using parameters with a script allow additional data to be passed to the script in a structured way. You use the $args[i] variable to refer to arguments that are passed to a script. The [i] simply means that you can use an array of values with an index of [0] in case you want to pass multiple parameters to your script. Translating this to the example we used in Part 2, let’s say we want the script that returns all the processes with PrivateMemorySize greater than 50MB to be dynamic – a script that will return all the processes for which property and value we specify. We define variables that will contain the parameters that we will pass to the script and use that variable within the script #Variable $attrib will contain the values of the first parameter #Variable $valMB will contain the values of the second parameter $attrib = $args[0] $val = $args[1] Get-Process | Where-Object {$_.$attrib -gt $val} | SELECT ProcessName, $attrib | Format-List Notice the use of the pound/sharp (#) symbol to let PowerShell know that the current line is a comment. It is recommended to write as much comments in your scripts as possible. Also, the way to declare a variable in PowerShell starts with a $ sign. Bear in mind that PowerShell variables are objects, unlike in other scripting languages like VBScript where variables are simply containers for values. This means that you can pass objects to a script variable the same way you can pipe the results of a cmdlet to another cmdlet. The command in the script is exactly the same as what we had in Part 2 with a little variation. We used the variable $attrib instead of a fixed property like PrivateMemorySize and the variable $val instead of a fixed value Calling the script and passing parameter values to it:   Notice how I used PrivateMemorySet and 50MB as the values for $attrib and $val, respectively, on the first call to the script while using WorkingSet and 100MB on the second call. This makes your script dynamic simply by using variables and parameters. Server Management Objects (SMO): Your Gateway to SQL Server SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing SQL Server. Simply put, its a way to allow programmatic management of SQL Server instances. SQL Server client tools like Management Studio were created using SMO. You can use SMO with other scripting or programming languages that can (note that scripting languages like VBScript uses Component Object Model automation and will not be able to see SMO since it uses .NET) to build SQL Server management applications. While built for SQL Server 2005 using the .NET Framework 2.0, it is compatible with SQL Server 2000 and 2008, allowing you to manage multi-version instances across your enterprise. One thing that you should note though is that SMO does not support compatibility levels below 80. I’ve seen databases running on SQL Server 2005 but are still on compatibility 70. Hence, you will not be able to manage those databases using SMO. The first thing you need to do is load the appropriate .NET assemblies used by SMO. [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) You do have to check the versions of the SMO assemblies that you load into memory as this might cause confusion especially when you have multiple versions running on your machine. There are other assemblies that are associated with SMO for managing SQL Server, but most of the time this would suffice. After loading the SMO assemblies, you can now create an instance of the Server object to represent the SQL Server instance that you will be connecting to, passing the instance name. In the line below, I will be assigning the results to a variable $serverInstanc, so we can manipulate the results the way we have done in the previous examples. $serverInstance = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “InstanceName” You can then pipe the results to the Get-Member cmdlet to see the different properties and methods for the Server object.   One of the most common task we do as SQL Server DBAs is to audit our SQL Server installations. We can apply what we have learned from this tip to write a script to audit our SQL Server installations, retrieving the instance name, the edition, the version and the service pack level. $instance = $args[0] [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null $serverInstance = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance Write-Host “Name: ” $serverInstance.Name Write-Host “Edition: ” $serverInstance.Edition Write-Host “Version: ” $serverInstance.VersionString Write-Host “ProductLevel: ” $serverInstance.ProductLevel I used a few cmdlets in this script for formatting purposes. The Out-Null cmdlet simply deletes the output instead of sending it to the console. We don’t really need the output but rather we need the object returned by the first line so we just pipe it to the Out-Null cmdlet. Next, I used the Write-Host cmdlet to display the results in the console window for demonstration purposes. You can use the formatting options mentioned in Part 2 to generate a report to be exported in either HTML or CSV. Notice how I used the same script to audit different versions of SQL Server, from 2000 to 2008. You can also call the Information class to display all the non-configurable information about the instance you are connecting to. Since we only need a few of them, we just specify them explicitly in the script. Next Steps We have just scratched the tip of the iceberg on PowerShell and SMO. In future tips, we will look at the common DBA tasks and translate them to PowerShell with SMO Download and install Windows PowerShell Check out the PowerShell Week webcasts from Microsoft TechNet Read more on the SMO Class Library to translate SQL Server objects that we are already familiar with to SMO objects Readers Who Read This Tip Also Read Backup SQL Server Databases with a Windows PowerShell Script Generating SQL Scripts using Windows PowerShell Checking SQL Server Agent jobs using Windows PowerShell Check the Last SQL Server Backup Date using Windows PowerShell Retrieve a List of SQL Server Databases and their Properties using PowerShell More…

Retrieve a List of SQL Server Databases and their Properties using PowerShell

http://www.mssqltips.com/tip.asp?tip=1759

Problem
In a previous tip on using Using PowerShell with SQL Server Management Objects (SMO), you’ve seen how you can use Windows PowerShell and SMO to administer SQL Server databases. I would like to translate some of the Transact-SQL scripts that I use every day, starting with the simple ones like retrieving a list of databases and their properties for auditing purposes.

Solution
One of the things that we do as DBAs is to retrieve a list of databases and their properties for auditing and reporting purposes. We check for properties such as recovery model, available free space, autoshrink, etc., and generate action items based on them. We’ve already seen how to access the Server object – its properties and methods – using SMO. We will dig into the object hierarchy and look at the different members of the Server object. A SQL Server instance can be described using different properties like instance name, logins, settings, all of which are members of the Server object.

What would be of main interest for this tip is the Databases property. This property represents a collection of database objects defined on a SQL Server instance, regardless whether they are system or user databases. Let’s start by retrieving all the members of the Databases property. The script below is the same as the one in the previous scripts with the addition of a Get-Member cmdlet to retrieve the list of members for the Databases property, specifying Property as the MemberType which just means to retrieve all the database properties. Be sure not to get confused with the terms – the Databases property of the Server object is a collection of database objects and the database objects themselves have their own properties.

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null $s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “LOCALHOST\SQL2000″ $dbs=$s.Databases $dbs | Get-Member -MemberType Property

From the list of properties from the Database object, let’s select the most common ones – Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size and SpaceAvailable

$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

Notice that it is pretty simple and straight-forward. Since the $dbs variable contains a collection of database objects, you can now pipe the results so a Select-Object cmdlet, which we just refer to as SELECT. You can then make this script dynamic by introducing variables and saving it as a PowerShell script which we have done in previous tips.

Reading from a Text File

There are a couple of ways to list the servers that you manage, but for simplicity’s sake, we’ll use a simple text file. What we will do is store the names of the SQL Server instances that you administer in a text file named SQL_Servers.txt and pass the contents in a variable named $instance. We will use the Get-Content cmdlet to read thru the contents of the text file and the ForEach-Object cmdlet to iterate thru the collection. The script would look something like the one below. You can either write it in a script file or type it directly in the PowerShell console

ForEach ($instance in Get-Content “D:\SQL_Servers.txt”) {        [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null        $s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance        $dbs=$s.Databases        $dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable }

Generate Fancy Reports – Enter Microsoft Excel

Let’s admit it, Microsoft Excel happens to be the most popular application to generate spreadsheets and reports so it does make sense to make the most out of it with any reports that we need to generate. IT Managers and key stakeholders would like to see either graphs with colors instead of just numerical values within reports. What we will do is to use PowerShell to automate report creation for our database audit report with Excel. There are a few key things that we need to understand about the process. First, we will be calling Excel thru COM automation and not as a .NET application. We used the New-Object cmdlet to create an instance of the Excel object, the -ComObject parameter to create a new COM object of type Excel.Application which is the ProgID of the object we want to create and assign it the variable $Excel.

$Excel = New-Object -ComObject Excel.Application

You might be interested to find out what other COM components are available on a Windows machine for you to use. To retrieve their ProgID values, you can query the registry with a one-liner like the one below (this was derived from the PowerShell team blog)

dir  ”REGISTRY::HKEY_CLASSES_ROOT\CLSID” -include PROGID -recurse | foreach {$_.GetValue(“”)}

So much for COM objects as we only need the Excel.Application at this point. Next, we set the Visible property of the Excel object to True so we can see what is happening

$Excel.visible = $True

Next, we use the Add() method to add a new workbook to the instance of the Excel object and use the Item property to create an object reference to the first worksheet in the workbook. The variable $Sheet will hold that pointer to the worksheet and will be referred to in most of the code

$Excel = $Excel.Workbooks.Add() $Sheet = $Excel.Worksheets.Item(1)

You can add as many workbooks as you want, but one would be enough for our purpose. Now, to actually work with Excel we need to reference row and column numbers. To do that, we call the Cells property of the Excel object to return the cell or cells in the active sheet. We can then modify the cells like providing a value, changing the Font properties, etc. An example to assign a value to a cell in row 1, column 1 and change the Font property to Bold is shown below.

$Sheet.Cells.Item(1,1) = “SQL SERVER INSTANCE NAME:” $Sheet.Cells.Item(1,1).Font.Bold = $True

Saving the code and running it in PowerShell will open up an Excel spreadsheet that will look something like the one below

You can either call the SaveAs() method of the Worksheet object $Sheet to save the Excel spreadsheet or just leave it as it is.

Imagine dealing with row and column values that are dynamic since there would be a fair amount of databases on each of your SQL Server instances. Below is a PowerShell script that takes into account what we have just covered with SMO and Excel to create a fancy report

#Create a new Excel object using COM $Excel = New-Object -ComObject Excel.Application $Excel.visible = $True$Excel = $Excel.Workbooks.Add() $Sheet = $Excel.Worksheets.Item(1) #Counter variable for rows $intRow = 1 #Read thru the contents of the SQL_Servers.txt file foreach ($instance in get-content “D:\SQL_Servers.txt”) {      #Create column headers      $Sheet.Cells.Item($intRow,1) = “INSTANCE NAME:”      $Sheet.Cells.Item($intRow,2) = $instance      $Sheet.Cells.Item($intRow,1).Font.Bold = $True      $Sheet.Cells.Item($intRow,2).Font.Bold = $True      $intRow++       $Sheet.Cells.Item($intRow,1) = “DATABASE NAME”       $Sheet.Cells.Item($intRow,2) = “COLLATION”       $Sheet.Cells.Item($intRow,3) = “COMPATIBILITY LEVEL”       $Sheet.Cells.Item($intRow,4) = “AUTOSHRINK”       $Sheet.Cells.Item($intRow,5) = “RECOVERY MODEL”       $Sheet.Cells.Item($intRow,6) = “SIZE (MB)”       $Sheet.Cells.Item($intRow,7) = “SPACE AVAILABLE (MB)”      #Format the column headers      for ($col = 1; $col –le 7; $col++)      {           $Sheet.Cells.Item($intRow,$col).Font.Bold = $True           $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48           $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34      }      $intRow++       #######################################################      #This script gets SQL Server database information using PowerShell      [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null      # Create an SMO connection to the instance      $s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance      $dbs = $s.Databases      #$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable      #Formatting using Excel      ForEach ($db in $dbs)      {           #Divide the value of SpaceAvailable by 1KB           $dbSpaceAvailable = $db.SpaceAvailable/1KB           #Format the results to a number with three decimal places           $dbSpaceAvailable = “{0:N3}” -f $dbSpaceAvailable           $Sheet.Cells.Item($intRow, 1) = $db.Name           $Sheet.Cells.Item($intRow, 2) = $db.Collation           $Sheet.Cells.Item($intRow, 3) = $db.CompatibilityLevel            #Change the background color of the Cell depending on the AutoShrink property value            if ($db.AutoShrink -eq “True”)           {                $fgColor = 3           }           else           {                $fgColor = 0           }           $Sheet.Cells.Item($intRow, 4) = $db.AutoShrink           $Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor           $Sheet.Cells.Item($intRow, 5) = $db.RecoveryModel           $Sheet.Cells.Item($intRow, 6) = “{0:N3}” -f $db.Size           #Change the background color of the Cell depending on the SpaceAvailable property value           if ($dbSpaceAvailable -lt 1.00)           {                $fgColor = 3           }            else           {                $fgColor = 0           }           $Sheet.Cells.Item($intRow, 7) = $dbSpaceAvailable           $Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor           $intRow ++      } $intRow ++ } $Sheet.UsedRange.EntireColumn.AutoFit() cls

Running the script in PowerShell will generate an Excel spreadsheet similar to the one below, highlighting those properties that need immediate attention using some fancy colors from the definitions available here.

Next Steps

• Download and install Windows PowerShell
• Check out the article on How to Use Windows PowerShell to Automate Microsoft Excel from Microsoft TechNet
• Read more on the SMO Class Library to translate SQL Server objects that we are already familiar with to SMO objects
• Find out more about the Excel 2003 Object Model to incorporate it in your automated report generation

Readers Who Read This Tip Also Read

• Backup SQL Server Databases with a Windows PowerShell Script
• Generating SQL Scripts using Windows PowerShell
• Checking SQL Server Agent jobs using Windows PowerShell
• Check the Last SQL Server Backup Date using Windows PowerShell
• Using PowerShell with SQL Server Management Objects (SMO)
• More…