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…

Checking SQL Server Agent jobs using Windows PowerShell

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

Problem
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?

Solution
Similar to the task described in the tip Check the Last SQL Server Backup Date using Windows PowerShell, it would require reading the tables in the msdb database and joining them appropriately to find out which jobs failed. Two tables in particular are of interest to us to check for job execution information like job name, execution status, run date, run time, etc. – the sysjobs and sysjobhistory tables. 

 

The script below displays a list of jobs on your SQL Server instance with there status.

 

USE msdb  GO  SELECT     j.[name] AS [JobName],     run_status = CASE h.run_status     WHEN 0 THEN ’Failed’    WHEN 1 THEN ’Succeeded’    WHEN 2 THEN ’Retry’    WHEN 3 THEN ’Canceled’    WHEN 4 THEN ’In progress’    END,    h.run_date AS LastRunDate,      h.run_time AS LastRunTime FROM sysjobhistory h     INNER JOIN sysjobs j ON h.job_id = j.job_id         WHERE j.enabled = 1          AND h.instance_id IN         (SELECT MAX(h.instance_id)             FROM sysjobhistory h GROUP BY (h.job_id)) GO

 

Notice that the run_date and run_time columns of the sysjobhistory table are of type int and would be a bit challenging to convert the columns to their appropriate data types.  Server Management Objects (SMO) exposes these properties when using Windows PowerShell. The JobServer property of the Server object represents the SQL Server Agent associated with an instance of SQL Server. This includes the SQL Server jobs, operators and alerts.When translating the T-SQL query above to Windows PowerShell, we would be interested in the Name, LastRunDate and LastRunOutcome properties of the Jobs object. What’s really good to note is that the LastRunDate property is in a datetime format that no longer requires conversion to the appropriate data type, similar to what we get from the sysjobhistory table in the msdb database.  I keep trying to highlight this for every tip I’ve written that uses PowerShell with SMO.  The only aspect of the code that we have changed from the scripts in the previous tips is the last line, i.e. adding new properties for the new objects we are working with. This highlights the power and simplicity of Windows PowerShell from the script.

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null $rvs = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “LOCALHOST\SQL2000″   #Create an instance of the Jobs object collection from the JobServer property #And pipes that to the filter Where-Object cmdlet to retrieve only those jobs that are enabled but failed $srv.JobServer.Jobs | Where-Object {$_.IsEnabled -eq $TRUE} | Select Name,LastRunOutcome, LastRunDate

 

Notice that the LastRunDate property is in the correct data type. The LastRunOutcome property is returned as they are without the need for further translations as in the T-SQL script above. Let’s call to the Excel object as we did in the previous tips to format the results. Again, the script above is more than enough for what we need.

#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) = “JOB NAME”       $Sheet.Cells.Item($intRow,2) = “LAST RUN OUTCOME”       $Sheet.Cells.Item($intRow,3) = “LAST RUN DATE”        #Format the column headers      for ($col = 1; $col –le 3; $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 Agent job status information using PowerShell        [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null        # Create an SMO connection to the instance      $srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance        $jobs=$srv.JobServer.Jobs        #Formatting using Excel   ForEach ($job in $jobs)  {        # Formatting for the failed jobs        if ($job.LastRunOutcome -eq 0)        {            $fgColor = 3        }        else        {            $fgColor = 0        }              $Sheet.Cells.Item($intRow, 1) =  $job.Name        $Sheet.Cells.Item($intRow, 2) = $job.LastRunOutcome.ToString()        $Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor        $Sheet.Cells.Item($intRow, 3) =  $job.LastRunDate                        $intRow ++     }    $intRow ++ }   $Sheet.UsedRange.EntireColumn.AutoFit() cls

 

 

Next Steps

 

• Try converting your T-SQL DBA scripts to Windows PowerShell
• Read more on the SMO Class Library to translate SQL Server objects that we are already familiar with to SMO objects
• Check out some recent PowerShell scripts:
  • Check the Last SQL Server Backup Date using Windows PowerShell
  • Retrieve List of Databases and their Properties using PowerShell
  • Using PowerShell with SQL Server Management Objects (SMO)
  • Introduction to Windows PowerShell for the SQL Server DBA Part 1
  • Introduction to Windows PowerShell for the SQL Server DBA Part 2

Readers Who Read This Tip Also Read

• Backup SQL Server Databases with a Windows PowerShell Script
• Generating SQL Scripts 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
• Using PowerShell with SQL Server Management Objects (SMO)
• More…

Backup SQL Server Databases with a Windows PowerShell Script

http://www.mssqltips.com/tip.asp?tip=1862&home

Problem
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.

 

Solution
Generating and maintaining backups are the most important tasks that any DBA has to fulfill. And, as mentioned in the previous tip, SMO can be used to backup and restore a database. There are different ways to backup a database, depending on company policies on disaster recovery. We will be introducing a new SMO namespace that will allow us to create the Backup object. This is an added line in our previous PowerShell scripts to create a new instance of the Backup object.

 

$dbBackup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)

The first property of the Backup object that we need to assign is the name of the database that we need to do a backup on. This is defined by the Database property of the Backup object. The code snippet below defines a Database property for the Backup object to perform a backup on the Northwind database.

 

$dbBackup.Database = “Northwind”

Whenever you generate or create backups, SMO considers this as a Device. This requires us to add the Backup file to the Devices collection of the Backup object, specifying the DeviceType as File. The first parameter in the AddDevice method of the Devices collection is the file name of the backup file with the full path as stored in the file system whereas the second parameter is the DeviceType. While SQL Server supports the use of devices, I normally recommend using the file system to easily identify the backup sets based on the file name using the proper naming convention (one thing you would not want to be worried about during disaster recovery is scrambling to find the valid backup sets).

 

$dbBackup.Devices.AddDevice(“yourDBbackupFile.bak”, “File”)

The next important property of the Backup object that we are interested in is the Action property. The Action property sets the type of backup to be performed – Database, Log or File. The default value of the Action property is set to Database. This property is of importance based on the definition of our service level agreement as you don’t just generate backups without understanding the defined SLAs for a specific database or application. For our example, we will define a FULL database backup

 

$dbBackup.Action = “Database”

Once we have defined the properties of our Backup object, it’s just a matter of calling the SqlBackup method of the Backup object. The SqlBackup method performs the backup operation as defined by the properties you have set in the Backup object. It accepts a parameter of type Server which represents the SQL Server instance that you have defined as the source of the backup operation.

 

$dbBackup.SqlBackup($s)

Combining the code snippets above, here is a working Windows PowerShell script to perform a FULL database backup against the Northwind database, storing the backup file in your file system.

 

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null $s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “LOCALHOST\SQL2005_1″   #Create a Backup object instance with the Microsoft.SqlServer.Management.Smo.Backup namespace $dbBackup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)   #Set the Database property to Northwind $dbBackup.Database = “Northwind”   #Add the backup file to the Devices collection and specify File as the backup type $dbBackup.Devices.AddDevice(“D:\PSScripts\backups\NWind_FULL.bak”, “File”)   #Specify the Action property to generate a FULL backup $dbBackup.Action=”Database”   #Call the SqlBackup method to generate the backup $dbBackup.SqlBackup($s)

Now, since you won’t be performing backups of just a single database, it would be better if we loop the entire script in a For-Each cmdlet iterating thru the Databases collection of the Server object.

 

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null $s = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $instance   $bkdir = “D:\PSScripts\backups” #We define the folder path as a variable $dbs = $s.Databases foreach ($db in $dbs) {      if($db.Name -ne “tempdb”) #We don’t want to backup the tempdb database      {      $dbname = $db.Name      $dt = get-date -format yyyyMMddHHmm #We use this to create a file name based on the timestamp      $dbBackup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)      $dbBackup.Action = “Database”      $dbBackup.Database = $dbname      $dbBackup.Devices.AddDevice($bkdir + “\” + $dbname + “_db_” + $dt + “.bak”, “File”)      $dbBackup.SqlBackup($s)      } }

While my only condition for generating backups is to exclude the tempdb database, you can include other conditions such as querying the database property if it is used for database mirroring (IsMirroringEnabled property) or specifying database names if you are sure enough that you won’t be needing backups for them. While not really necessary, you also might want to include other Backup object properties such as BackupSetName, BackupSetDescription, and MediaDescription as they can be of help in case you wanted to find out the contents of your backup sets.

 

NOTE: If you have SQL Server 2008 Client Tools installed on your workstation where you intend to run this PowerShell script, it is important that you add a reference to the Microsoft.SqlServer.SmoExtended namespace. The Backup object and a few other objects were moved from the Microsoft.SqlServer.Smo to the Microsoft.SqlServer.SmoExtended. In the example I provided, I have SQL Server 2005 and SQL Server 2008 running on my machine, thus, requiring that I add a reference to the Microsoft.SqlServer.SmoExtended namespace. SQL Server MVP Allen White has blogged about Loading SMO Assemblies in PowerShell and explains using a custom script to load appropriate SMO assemblies depending on the SQL Server version.

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

Generating Differential and Log Backups

Depending on your service level agreement, you would also need to define either Differential or Log backups to complement your Full backups. This can be done by changing the Action property of the Backup object to either Log for Log backups or setting the Incremental property of the Backup object to a value of 1. Below is a code snippet for generating Log backups, simply by changing the Action property.

 

$dbBackup.Action = “Log”

Note that you can only do Log backups on databases that are not configured to use the SIMPLE recovery model. This means that your condition for generating Log backups would include a check on the RecoveryModel property

 

if($db.RecoveryModel -ne 3) #Don’t issue Log backups for DBs with RecoveryModel=3 or SIMPLE      {      $dbname = $db.Name      $dt = get-date -format yyyyMMddHHmm #Create a file name based on the timestamp      $dbBackup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)      $dbBackup.Action = “Log”      $dbBackup.Database = $dbname      $dbBackup.Devices.AddDevice($bkdir + “\” + $dbname + “_log_” + $dt + “.trn”, “File”)      $dbBackup.SqlBackup($s)      }

For Differential backups, you would need to replace the Action property with the Incremental property and set the value to 1

 

if($db.Name -ne “tempdb”)      {      $dbname = $db.Name      $dt = get-date -format yyyyMMddHHmm #Create a file name based on the timestamp      $dbBackup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)      $dbBackup.Incremental = 1      $dbBackup.Database = $dbname      $dbBackup.Devices.AddDevice($bkdir + “\” + $dbname + “_diff_” + $dt + “.bak”, “File”)      $dbBackup.SqlBackup($s)      }

Your backup strategies, depending on your service level agreement, will definitely include a combination of either Full and Differential and/or Log backups.

 

Next Steps

 

• Read more on the Backup class in SMO
• Review the tip Backup and Restore SQL Server databases programmatically with SMO
• Test how you can use Windows PowerShell to generate your SQL Server backups.

Readers Who Read This Tip Also Read

• 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
• Using PowerShell with SQL Server Management Objects (SMO)
• More…

SQL Server 2005 : Verify table already exists

http://gchandra.wordpress.com/2007/12/04/sql-server-2005-verify-table-already-exists/

Verifying that an object exists

If the table exists, it is deleted. If the table does not exist, the DROP TABLE statement is not executed.

IF OBJECT_ID (N’dbo.AWBuildVersion’, N’U’) IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO

Syntax :

OBJECT_ID (’object_name’, ['object_type'])

Object Type is optional, and possible values it can hold are.. (Items in bold are frequently used)

Object type:

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

PK = PRIMARY KEY constraint

P = SQL stored procedure

PC = Assembly (CLR) stored procedure

FN = SQL scalar function

FS = Assembly (CLR) scalar function

FT = Assembly (CLR) table-valued function

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TR = SQL DML trigger

IF = SQL inlined table-valued function

TF = SQL table-valued-function

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

IT = Internal table

---

Possibly related posts: (automatically generated)

• SQL2000 : Drop Database Contents from Query Analyzer
• SQL Server, Clean your Database Records and reset Identity Columns, The Sho…
• Organize your SQL Server 2005 tables in a better way !!
• Re-naming objects in SQL Server

FieldInfo.IsStatic Property

http://msdn.microsoft.com/en-us/library/system.reflection.fieldinfo.isstatic(VS.80).aspx

using System;
using System.Reflection;

// Make two fields.
public class Myfielda
{
    private string field = “A private field”;
    public string Field
    {
        get{return field;}
        set{if(field!=value){field=value;}}
    }
}
public class Myfieldb
{
    static string field = “B private static field”;
    public string Field
    {
        get{return field;}
        set{if(field!=value){field=value;}}
    }
}
 
public class Myfieldinfo
{
    public static int Main()
    {
        Console.WriteLine(“\nReflection.FieldInfo”);
        Myfielda Myfielda = new Myfielda();
        Myfieldb Myfieldb = new Myfieldb();
 
        // Get the Type and FieldInfo.
        Type MyTypea = typeof(Myfielda);
        FieldInfo Myfieldinfoa = MyTypea.GetField(“field”, BindingFlags.NonPublic|BindingFlags.Instance);
        Type MyTypeb = typeof(Myfieldb);
        FieldInfo Myfieldinfob = MyTypeb.GetField(“field”, BindingFlags.NonPublic|BindingFlags.Static);
 
        // For the first field, get and display the name, field, and IsStatic property value.
        Console.Write(“\n{0} – “, MyTypea.FullName);
        Console.Write(“{0}; “, Myfieldinfoa.GetValue(Myfielda));
        Console.Write(“IsStatic – {0}”, Myfieldinfoa.IsStatic);
 
        // For the second field get and display the name, field, and IsStatic property value.
        Console.Write(“\n{0} – “, MyTypeb.FullName);
        Console.Write(“{0}; “, Myfieldinfob.GetValue(Myfieldb));
        Console.Write(“IsStatic – {0}”, Myfieldinfob.IsStatic);
 
        return 0;
    }
}

FieldInfo Class

http://msdn.microsoft.com/en-us/library/system.reflection.fieldinfo(VS.80).aspx

C#

Copy Code

using System;
using System.Reflection;

public class FieldInfoClass
{
    public int myField1 = 0;
    protected string myField2 = null;
    public static void Main()
    {
        FieldInfo[] myFieldInfo;
        Type myType = typeof(FieldInfoClass);
        // Get the type and fields of FieldInfoClass.
        myFieldInfo = myType.GetFields(BindingFlags.NonPublic | BindingFlags.Instance
            | BindingFlags.Public);
        Console.WriteLine("\nThe fields of " +
            "FieldInfoClass are \n");
        // Display the field information of FieldInfoClass.
        for(int i = 0; i < myFieldInfo.Length; i++)
        {
            Console.WriteLine("\nName            : {0}", myFieldInfo[i].Name);
            Console.WriteLine("Declaring Type  : {0}", myFieldInfo[i].DeclaringType);
            Console.WriteLine("IsPublic        : {0}", myFieldInfo[i].IsPublic);
            Console.WriteLine("MemberType      : {0}", myFieldInfo[i].MemberType);
            Console.WriteLine("FieldType       : {0}", myFieldInfo[i].FieldType);
            Console.WriteLine("IsFamily        : {0}", myFieldInfo[i].IsFamily);
        }
    }
}

Generic Enum to List converter (C#)

http://devlicio.us/blogs/joe_niland/archive/2006/10/10/Generic-Enum-to-List_3C00_T_3E00_-converter.aspx

One for the utility library…

Here’s a small function I’ve found to be quite useful recently. It takes an enum type and returns a generic list populated with each enum item.

        public static List<T> EnumToList<T>()
        {
            Type enumType = typeof (T);

            // Can’t use type constraints on value types, so have to do check like this
            if (enumType.BaseType != typeof(Enum))
                throw new ArgumentException(“T must be of type System.Enum”);
            
            Array enumValArray = Enum.GetValues(enumType);

            List<T> enumValList = new List<T>(enumValArray.Length);

            foreach (int val in enumValArray) {
                enumValList.Add((T)Enum.Parse(enumType, val.ToString()));
            }

            return enumValList;
        }

Here’s one of the ways that I’ve used it:

List<DayOfWeek> weekdays =
    EnumHelper.EnumToList<DayOfWeek>().FindAll(
        delegate (DayOfWeek x)
        {
            return x != DayOfWeek.Sunday && x != DayOfWeek.Saturday;
        });