How to script an entire SQL Server instance

I have just posted a C# project called SQLServerScripts that will create script files from every object on an MS SQL Server instance.

You can find the project in my Github repository.

SQLServerScripts

Uses Microsoft.SqlServer.Management.Smo to create scripts for all objects in SQL Server. Useful for tracking database changes in GIT or SVN.

Overview

Requirements

This project uses assemblies from the Microsoft.SqlServer.Management.Smo namespace. You must have SQL Server Management Studio installed.

Description

Will create scripts for every (useful) object on SQL Server (tested on SQL2008R2).

This includes:

  • Logins
  • SQLAgent Jobs
  • For each (non-system Database):
    • CREATE DATABASE scripts
    • Users
    • Schemas
    • Database Roles
    • Application Roles
    • Tables - including:
      • Indexes
      • Triggers
      • Grant/Revoke statements
    • Views
    • Stored Procedures
    • Functions
    • Synonyms
    • User-Defined Types
    • User-Defined Data Types
    • User-Defined Table Types

Output

From the working folder, it will create the following folder structure containing individual scripts for each SQL Server Object:

  • {SERVERNAME}
    • Databases
      • {DBNAME1} - Root folder contains the CREATE DATABASE Script
        • Functions
        • Procs
        • Roles - Application
        • Roles - Database
        • Schemas
        • Synonyms
        • Tables
        • Types
          • User-Defined Types
          • User-Defined Data Types
          • User-Defined Table Types
        • Users
        • Views
      • {DBNAME2}…
    • Logins
    • SQLAgent

Things to note:

  • System databases are ignored
  • System owned objects are ignored
  • Bad file name characters are stripped from object names before creating the files
  • Login scripts have the random password removed and changed to **CHANGEME** (avoids unnecessary source control commits)
  • IMPORTANT - To help track deleted database objects, all *.sql files are deleted from the target folder before creating the new version

Interface

Example usage:

SQLServerScripter srv = new SQLServerScripter();
srv.LogMessage += LogMessage; // Event handler for LogMessages

//
// Setup the connection details
// NB: If you do not set SQLServerScripterConnection.User the connection will use
// ActiveDirectory credentials
//
SQLServerScripterConnection c = new SQLServerScripterConnection();
c.Server = "MYSQLSRV01";

// c.User = "MyUser";
// c.Password = "MyPassword";

//
// List of databases to exclude from scripting
// NB: System databases are automatically excluded
//
List<string> DBExclusions = new List<string> { "DBName1", "DBName2" };

//
// Create the scripts
//
srv.ScriptEverything(c, DBExclusions);

Example LogMessage Event Handler:

static void LogMessage(object sender, SQLServerScripterMessageArgs m)
{
    Console.WriteLine("----");
    Console.WriteLine(String.Format("Server: {0}", m.Server));
    if (m.Database != null)
        Console.WriteLine(String.Format("Database: {0}", m.Database));

    Console.WriteLine(String.Format("Object: {0}.{1}", m.ObjectType, m.ObjectName));
    Console.WriteLine(String.Format("Output File: {0}", m.Path));
}

View comments.

more ...

Scottish Independence - Can the Yes camp win?

Looking at the Scottish Independence polling data from 29/01/2012 to 7/8/2014, it is difficult to see how the Yes camp can swing a victory in the Scottish Independence vote. If you look at a 5 point moving average of all polls, you will see that whilst there has indeed been a recent upswing for “Yes”, there has been an even bigger upswing for “No”.

Polling Data MA5 Trend

Averaging out the poll data also shows that whilst individual polls might have the Yes camp pushing the low 40s, the trend has never peaked over 40%.

If you take a 10 poll average, the recent trend is pretty much flat for “Yes” and broadly rising for “No”.

Polling Data MA10 Trend

By my estimation, the “Yes” camp will need to swing well over 80% of the “Don’t Knows” just to get a marginal victory. That is not enough. To get a decisive win, they will have to convince a significant chunk of “No”voters to change their mind. That is a pretty tall order this close to the election.

Source: http://ukpollingreport.co.uk/scottish-independence-referendum

View comments.

more ...

Exchange 2010 - How to Monitor Mailbox Sizes

How to schedule a PowerShell script to automatically email a report listing user mailbox sizes.

Required Powershell Scripts

The following PowerShell script will list all mailboxes sorting by size (descending):

ExchGetMailboxSizes.ps1

Get-Mailbox -ResultSize Unlimited |
    Get-MailboxStatistics |
    Select DisplayName,StorageLimitStatus, `
        @{name="TotalItemSize (MB)"; `
            expression={[math]::Round(($_.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","") / 1MB) ,2)} `
        }, `
       ItemCount |
    Sort "TotalItemSize (MB)" -Descending

Example Output

DisplayName  StorageLimitStatus  TotalItemSize (MB)  ItemCount
-----------  ------------------  ------------------  ---------
User 1               NoChecking            10942.95     123888
User 2               NoChecking            10307.82     122734
User 3               NoChecking            10105.58      86925
User 4               NoChecking             9746.91     162055

MailboxAlerts.ps1

This next script will send the output of the above script as an email:

$messageParameters = @{
    Subject = "[Exchange Report] Mailbox Sizes"
    Body = (.\ExchGetMailboxSizes.ps1 | Out-String)
    From = "your@yourdomain.com"
    To = "alerts@yourdomain.com"
    SmtpServer = "smtp.yourdomain.com"
}
Send-MailMessage @messageParameters

Now you can setup a scheduled task to run this script from your Exchange Server.

Creating a Scheduled Task

First up, copy both of the above scripts to a folder on a local disk of the Exchange Server. In the examples below I am assuming C:\Scripts.

Next, create a new Task from the Windows 2008 Task Scheduler. You should configure this task as a user who has Exchange Admin privileges.

Task Settings

General - Security Options

  • Name the task: e.g.: Weekly Mailbox Size Alerts
  • Select Run whether user is logged in or not (will require the user’s password to save the task)
  • Leave Do not store password unchecked
  • Check Run with highest privileges

Action Settings

  • Go to the Action tab and click New…

Mike Pfeiffer does a great job of explaining how to set up scheduled tasks for Exchange 2010 PowerShell Scripts. In summary, use the following settings:

Program/Script

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Add Arguments (optional)

-version 2.0 -NonInteractive -WindowStyle Hidden -command ". 'C:\Program Files\Microsoft\Exchange Server\V14\bin\RemoteExchange.ps1'; Connect-ExchangeServer -auto;c:\Scripts\MailboxAlerts.ps1"

Start in (optional)

C:\Scripts

Triggers

  • Setup a sensible schedule that works for you, e.g.: Weekly - Monday at 9am
  • Save the Task be clicking OK.
  • You will be prompted for your password at this stage - this is required so that the task can run when the user is logged off.

View comments.

more ...