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

Setting up OAuth2 for Gmail

Make sure you setup a project on the Google Developers Page using the account from which you want to send email.

See the following link describing the process of obtaining the required authorization tokens for nodemailer.

Extract of important steps

Step 1 : Installation

npm install nodemailer

Step 2 : Register your Application at Google APIs Console

XOAuth2 is similar with OAuth2, so you need to register your application to Google. Jump to Google APIs Console, then create a project if you don’t have, and open “API Access” page.

In this here, when you create a client ID, put “https://developers.google.com/oauthplayground” into the text box for Redirect URIs.

Step 3 : Open Google OAuth2.0 Playground.

You will obtain refreshToken & accessToken on this step at Google OAuth2.0 Playground. Open the page from https://developers.google.com/oauthplayground, then click the gear button on the right-top.

Set your client ID & client secret that obtained on step2, and select “Access token location:” as “Authorization header w/ Bearer prefix”. Then close it.

And set up the scopes. Put “https://mail.google.com/” into the textbox below the service scope list.

Then click [Authorize APIs] button.

Step 4 : Obtain the “refresh token”.

After OAuth2.0 authorization, click [Exchange authorization code for tokens] button. You can get your refresh token.

Use the following javascript template:

    var nodemailer = require("nodemailer");

    var smtpTransport = nodemailer.createTransport("SMTP", {
      service: "Gmail",
      auth: {
        XOAuth2: {
          user: "[email protected]", // Your gmail address.
                                                // Not @developer.gserviceaccount.com
          clientId: "your_client_id",
          clientSecret: "your_client_secret",
          refreshToken: "your_refresh_token"
        }
      }
    });

    var mailOptions = {
      from: "[email protected]",
      to: "[email protected]",
      subject: "Hello",
      generateTextFromHTML: true,
      html: "<b data-preserve-html-node="true">Hello world</b>"
    };

    smtpTransport.sendMail(mailOptions, function(error, response) {
      if (error) {
        console.log(error);
      } else {
        console.log(response);
      }
      smtpTransport.close();
    });

View comments.

more ...