Database Extract and Backup

extract_backup.ps1

#=========================================================================
# Database Extract and Backup -
# This script will make create/insert SQL for user tables and data,
# execute it to create a new database and create a BAK of the new database.
#=========================================================================

# The directory where the BAK will be saved
$BACKUPDIRECTORY = "c:\temp"
# The number of INSERTs in a transactional batch
$BATCHSIZE = 50
# Where clause to limit data selection
$WHERE_CLAUSE = ""

function GetSqlServerDataPath {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=master;Password=$PWD;User ID=$USER"
    $SqlConnection.Open()
    $SqlCmd = $SqlConnection.CreateCommand()
    $SqlCmd.CommandText = "SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) AS path FROM master.sys.master_files WHERE database_id = 1 AND file_id = 1;"
    $SqlCmd.CommandType = [System.Data.CommandType]::Text
    $rdr = $SqlCmd.ExecuteReader()
    $ord = $rdr.GetOrdinal("path")
    while ($rdr.Read()) {
        $path += @($rdr.GetString($ord))
    }
    $SqlConnection.Close()
    return $path
}

function GetRowData($tablename, $whereclause) {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$DATABASE;Password=$PWD;User ID=$USER"
    $SqlConnection.Open()
    $SqlCmd = $SqlConnection.CreateCommand()
    if ([System.String]::IsNullOrEmpty($whereclause)) {
        $SqlCmd.CommandText = "select * from " + $tablename + " with (nolock) "
    }
    else {
        $SqlCmd.CommandText = "select * from " + $tablename + " with (nolock) where " + $whereclause
    }
    $SqlCmd.CommandType = [System.Data.CommandType]::Text
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $result = New-Object System.Data.DataSet
    [void]$SqlAdapter.Fill($result)
    $SqlConnection.Close()
    return $result
}

function CreateSqlFiles ($whereClause){
    $schemalist = New-Object System.Collections.ArrayList
    $tablelist = @{}

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$DATABASE;Password=$PWD;User ID=$USER"
    $SqlConnection.Open()
    $SqlCmd = $SqlConnection.CreateCommand()
    $SqlCmd.CommandText = "SELECT t.name AS tablename, s.name AS schemaname FROM sys.tables t INNER JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.type = 'U' "
    $SqlCmd.CommandType = [System.Data.CommandType]::Text
    $rdr = $SqlCmd.ExecuteReader()
    $ord1 = $rdr.GetOrdinal("tablename")
    $ord2 = $rdr.GetOrdinal("schemaname")
    while ($rdr.Read()) {
        $schemaname = $rdr.GetString($ord2)
        if (!$schemalist.Contains($schemaname)) {
            [void]$schemalist.Add($schemaname)
        }

        $tablename = $rdr.GetString($ord1)
        if (!$tablelist.ContainsKey($tablename)) {
            $tablelist.Set_Item($tablename, '[' + $schemaname + '].[' + $tablename + ']')
        }
    }
    $SqlConnection.Close()

    # Generate database DDL
    GenerateDatabaseSql $newdb

    # Generate schema DDL
    foreach ($schema in $schemalist) {
        if ($schema -ne 'dbo') {
            GenerateSchemaSql $schema
        }
    }

    foreach($table in $tablelist.GetEnumerator()) {
        Write-Host "Processing ddl/dml for table" $table.Value

        # Generate table DDL
        CreateDdl($table.Name)

        # Generate table DML
        CreateDml $table.Value $whereClause
    }
}

function CreateDdl($table) {
    $sql = New-Object System.Text.StringBuilder
    [void]$sql.Append("select  ' CREATE TABLE [' + ss.name + '].[' + so.name + '] (' + STUFF(o.list,1,2,'') + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE ' ALTER TABLE [' + ss.name + '].[' + so.name + '] ADD CONSTRAINT [' + tc.Constraint_Name  + '] PRIMARY KEY ' + ' (' + STUFF(j.List, 1,2,'') + ');' END AS [SQLString] ")
    [void]$sql.Append("from  sys.objects so ")
    [void]$sql.Append("cross apply ")
    [void]$sql.Append("    (SELECT ")
    [void]$sql.Append("        ',  ['+column_name+'] ' + ")
    [void]$sql.Append("        data_type + case data_type ")
    [void]$sql.Append("            when 'sql_variant' then '' ")
    [void]$sql.Append("            when 'text' then '' ")
    [void]$sql.Append("            when 'ntext' then '' ")
    [void]$sql.Append("            when 'xml' then '' ")
    [void]$sql.Append("            when 'image' then '' ")
    [void]$sql.Append("            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'")
    [void]$sql.Append("            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +")
    [void]$sql.Append("        case when exists ( ")
    [void]$sql.Append("        select [object_id] from sys.columns ")
    [void]$sql.Append("        where object_name([object_id])=so.name ")
    [void]$sql.Append("        and name=column_name ")
    [void]$sql.Append("        and columnproperty([object_id], name, 'IsIdentity') = 1 ")
    [void]$sql.Append("        ) then ")
    [void]$sql.Append("        'IDENTITY(' +  ")
    [void]$sql.Append("        cast(isnull(ident_seed(so.name),1) as varchar) + ',' + ")
    [void]$sql.Append("        cast(isnull(ident_incr(so.name),1) as varchar) + ')' ")
    [void]$sql.Append("        else '' ")
    [void]$sql.Append("        end + ' ' + ")
    [void]$sql.Append("         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + ")
    [void]$sql.Append("          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END ")
    [void]$sql.Append("     from information_schema.columns where table_name = so.name ")
    [void]$sql.Append("     order by ordinal_position ")
    [void]$sql.Append("    FOR XML PATH('')) o (list) ")
    [void]$sql.Append("left join information_schema.table_constraints tc ON tc.Table_name = so.Name ")
    [void]$sql.Append("AND tc.Constraint_Type  = 'PRIMARY KEY' ")
    [void]$sql.Append("cross apply ")
    [void]$sql.Append("    (select ', [' + Column_Name + ']' ")
    [void]$sql.Append("     FROM   information_schema.key_column_usage kcu ")
    [void]$sql.Append("     WHERE  kcu.Constraint_Name = tc.Constraint_Name ")
    [void]$sql.Append("     ORDER BY ")
    [void]$sql.Append("        ORDINAL_POSITION ")
    [void]$sql.Append("     FOR XML PATH('')) j (list) ")
    [void]$sql.Append("left join sys.schemas ss ON ss.schema_id = so.schema_id ")
    [void]$sql.Append("where   type = 'U' ")
    [void]$sql.Append("AND so.name = @tablename AND o.list <> ''")

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$DATABASE;Password=$PWD;User ID=$USER"
    $SqlConnection.Open()
    $SqlCmd = $SqlConnection.CreateCommand()
    $SqlCmd.CommandText = $sql.ToString()
    $SqlCmd.CommandType = [System.Data.CommandType]::Text
    [void]$SqlCmd.Parameters.AddWithValue("@tablename", $table)
    $rdr = $SqlCmd.ExecuteReader()
    $ord = $rdr.GetOrdinal("SQLString")
    while ($rdr.Read()) {
        $sqlstr = $rdr.GetString($ord)
        if (![System.String]::IsNullOrEmpty($sqlstr)) {
            $tableddl += @($sqlstr)
        }
    }
    $SqlConnection.Close()

    try {
        $stream = [System.IO.File]::AppendText($dbDirectory + $newdb + "_tables.sql")
        $stream.WriteLine($tableddl[0])
        $stream.Close()
    }
    catch {
        Write-Host "!! Error while generating DDL for " $table.Name
        $error = $_.Exception.ToString()
        $error >> ($dbDirectory + "errors.txt")
    }
    finally {
        if ($stream -ne $null) {
            $stream.Close()
        }
    }
}

function CreateDml($table, $whereClause) {
    Write-Host "  where" $whereClause

    try {
        $ds = GetRowData $table $whereClause
        if ($ds.Tables.Count -eq 0) {
            Write-Host "  table not found"
            return
        }
        if ($ds.Tables[0].Rows.Count -eq 0) {
            Write-Host "  0 records -- skipping dml file"
            return
        }

        $stream = [System.IO.File]::AppendText($dataDirectory + $table + ".sql")
        $values = New-Object System.Collections.ArrayList
        $columns = New-Object System.Collections.ArrayList

        Write-Host " " $ds.Tables[0].Rows.Count "records"

        $stream.WriteLine("USE " + $newdb + ";")
        $stream.WriteLine("SET NOCOUNT ON;")
        $stream.WriteLine("SET XACT_ABORT ON;")
        $stream.WriteLine("IF OBJECTPROPERTY(OBJECT_ID('" + $table + "'), 'TableHasIdentity') = 1 ")
        $stream.WriteLine("  SET IDENTITY_INSERT " + $table + " ON")
        $stream.WriteLine()

        $batchcount = 1
        $counter = 1
        foreach ($dr in $ds.Tables[0].Rows) {
            if ($counter -eq 1) {
                $stream.WriteLine("BEGIN TRANSACTION;")
            }

            foreach ($dc in $ds.Tables[0].Columns) {
                $colname = "[" + $dc + "]"
                [void]$columns.Add($colname)

                $datatype = $dc.DataType.ToString()
                if ($datatype -eq "System.String" -or
                    $datatype -eq "System.DateTime" -or
                    $datatype -eq "System.Guid" -or
                    $datatype -eq "System.Char") {
                    $str = $dr[$dc].ToString()
                    if ($dr.IsNull($dc)) {
                        [void]$values.Add("null")
                    }
                    else {
                        $str = SqlEscape($dr[$dc].ToString())
                        [void]$values.Add("'" + $str + "'")
                    }
                }
                elseif ($dc.DataType.ToString() -eq "System.Boolean") {
                    $str = $dr[$dc].ToString()
                    if ($dr.IsNull($dc)) {
                        [void]$values.Add("null")
                    }
                    elseif ($str -eq "True") {
                        [void]$values.Add("1")
                    }
                    else {
                        [void]$values.Add("0")
                    }
                }
                elseif ($dc.DataType.ToString() -eq "System.Byte[]") {
                    if ($dr.IsNull($dc)) {
                        [void]$values.Add("null")
                    }
                    else {
                        $output = New-Object System.Text.StringBuilder
                        [void]$output.Append("0x")
                        $count = $dr[$dc].Length
                        for ($i = 0; $i -le $count-1; $i++) {
                            $hex = "{0:x}" -f $dr[$dc][$i]
                            [void]$output.Append($hex.PadLeft(2, "0"))  # Pad any single digits
                        }
                        [void]$values.Add("CONVERT(varbinary(max), '" + $output.ToString() + "', 1)")
                    }
                }
                else {
                    $str = $dr[$dc].ToString()
                    if ($dr.IsNull($dc)) {
                        [void]$values.Add("null")
                    }
                    else {
                        [void]$values.Add($dr[$dc].ToString())
                    }
                }
            }
            $stream.WriteLine("INSERT INTO " + $table + " (" + [System.String]::Join(",", $columns) + ") ")
            $stream.WriteLine(" VALUES (" + [System.String]::Join(",", $values) + "); ")

            $values.Clear()
            $columns.Clear()

            if ($counter -eq $BATCHSIZE) {
                $stream.WriteLine("COMMIT;")
                $stream.WriteLine("RAISERROR (N'" + $table + ": Insert Batch: " + $batchcount + "...Done', 10, 1) WITH NOWAIT;")
                $stream.WriteLine()
                $counter = 1
                $batchcount++
            }
            else {
                $counter++
            }
        }

        if ($counter -lt $BATCHSIZE -and $counter -gt 1) {
            $stream.WriteLine("COMMIT;")
            $stream.WriteLine("RAISERROR (N'" + $table + ": Insert Batch: " + $batchcount + "...Done', 10, 1) WITH NOWAIT;")
            $stream.WriteLine()
        }
        $stream.WriteLine("IF OBJECTPROPERTY(OBJECT_ID('" + $table + "'), 'TableHasIdentity') = 1 ")
        $stream.WriteLine("  SET IDENTITY_INSERT " + $table + " OFF;")
    }
    catch {
        Write-Host "!! Error while generating DML for " $table
        $error = $_.Exception.ToString()
        $error >> ($dbDirectory + "errors.txt")
    }
    finally {
        if ($stream -ne $null) {
            $stream.Close()
        }
    }
}

function GetTableColumns($table) {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$DATABASE;Password=$PWD;User ID=$USER"
    $SqlConnection.Open()
    $SqlCmd = $SqlConnection.CreateCommand()
    $SqlCmd.CommandText = "select c.name from sys.columns c inner JOIN sys.tables t on t.object_id = c.object_id where c.is_computed = 0 AND t.name = @tablename "
    $SqlCmd.CommandType = [System.Data.CommandType]::Text
    [void]$SqlCmd.Parameters.AddWithValue("@tablename", $table)
    $rdr = $SqlCmd.ExecuteReader()
    $ord = $rdr.GetOrdinal("name")
    while ($rdr.Read()) {
        $cols += @($rdr.GetString($ord))
    }
    $SqlConnection.Close()
    return $cols
}

function SqlEscape($str) {
    return $str -replace "'", "''"
}

function GenerateSchemaSql($schemaname) {
    try {
        $stream = [System.IO.File]::AppendText($dbDirectory + $newdb + "_schema.sql")
        $stream.WriteLine("USE " + $newdb + ";")
        $stream.WriteLine("IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = '" + $schemaname + "')")
        $stream.WriteLine("BEGIN ")
        $stream.WriteLine("  EXECUTE( 'CREATE SCHEMA [" + $schemaname + "] AUTHORIZATION [dbo]') ")
        $stream.WriteLine("END; ")
        $stream.WriteLine()
    }
    catch {
        Write-Host "!! Error while generating CREATE SCHEMA for " $schemaname
        $error = $_.Exception.ToString()
        $error >> ($dbDirectory + "errors.txt")
    }
    finally {
        if ($stream -ne $null) {
            $stream.Close()
        }
    }
}

function GenerateDatabaseSql($newdb) {
    try {
        $datapath = GetSqlServerDataPath
        $stream = [System.IO.File]::AppendText($dbDirectory + $newdb + "_database.sql")
        $stream.WriteLine("USE master;")
        $stream.WriteLine("IF DB_ID (N'" + $newdb + "') IS NOT NULL")
        $stream.WriteLine("BEGIN ")
        $stream.WriteLine("  DROP DATABASE " + $newdb + ";")
        $stream.WriteLine("END;")
        $stream.WriteLine("CREATE DATABASE " + $newdb)
        $stream.WriteLine(" ON ( NAME = " + $newdb + "_dat, ")
        $stream.WriteLine("   FILENAME = '" + $datapath + $newdb + ".mdf', SIZE=100 ) ")
        $stream.WriteLine(" LOG ON ( NAME = " + $newdb + "_log, ")
        $stream.WriteLine("   FILENAME = '" + $datapath + $newdb + ".ldf', SIZE=10 ); ")
        $stream.WriteLine()
    }
    catch {
        Write-Host "!! Error while generating CREATE DATABASE for " $newdb
        $error = $_.Exception.ToString()
        $error >> ($dbDirectory + "errors.txt")
    }
    finally {
        if ($stream -ne $null) {
            $stream.Close()
        }
    }
}

function GetFileContents($filename) {
    [System.IO.FileInfo]$file = New-Object System.IO.FileInfo ($filename)
    $stream = $file.OpenText()
    $contents = $stream.ReadToEnd()
    $stream.Close()
    return $contents
}

function LoadDatabase() {
    # create database
    Write-Host "Creating database" $newdb
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=master;Password=$PWD;User ID=$USER"
    $SqlConnection.Open()
    $SqlCmd = $SqlConnection.CreateCommand()
    $sql = GetFileContents ($dbDirectory + $newdb + "_database.sql")
    $SqlCmd.CommandText = $sql
    $SqlCmd.CommandType = [System.Data.CommandType]::Text
    [void]$SqlCmd.ExecuteNonQuery()
    $SqlConnection.Close()

    # create schema
    Write-Host "Creating schemas for" $newdb
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=master;Password=$PWD;User ID=$USER"
    $SqlConnection.Open()
    $SqlCmd = $SqlConnection.CreateCommand()
    $sql = GetFileContents ($dbDirectory + $newdb + "_schema.sql")
    $SqlCmd.CommandText = $sql
    $SqlCmd.CommandType = [System.Data.CommandType]::Text
    [void]$SqlCmd.ExecuteNonQuery()
    $SqlConnection.Close()

    # create Tables
    Write-Host "Creating tables"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$newdb;Password=$PWD;User ID=$USER"
    $SqlConnection.Open()
    $SqlCmd = $SqlConnection.CreateCommand()
    $sql = GetFileContents ($dbDirectory + $newdb + "_tables.sql")
    $SqlCmd.CommandText = $sql
    $SqlCmd.CommandType = [System.Data.CommandType]::Text
    [void]$SqlCmd.ExecuteNonQuery()
    $SqlConnection.Close()

    # insert data
    $dmlfiles = [System.IO.Directory]::GetFiles($dataDirectory)
    foreach ($file in $dmlfiles) {
        Write-Host "Inserting records for table" $file
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$newdb;Password=$PWD;User ID=$USER"
        $SqlConnection.Open()
        $SqlCmd = $SqlConnection.CreateCommand()
        $sql = GetFileContents $file
        $SqlCmd.CommandText = $sql
        $SqlCmd.CommandType = [System.Data.CommandType]::Text
        [void]$SqlCmd.ExecuteNonQuery()
        $SqlConnection.Close()
    }
}

function BackupDatabase {
    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$newdb;Password=$PWD;User ID=$USER"
        $SqlConnection.Open()

        $timestamp = Get-Date -format yyyyMMddHHmmss
        $bkfile = $newdb + "_" + $timestamp + ".bak"

        Write-Host "Backup dir: " $BACKUPDIRECTORY
        Write-Host "Backup file: " $bkfile

        $SqlCmd = $SqlConnection.CreateCommand()
        $sql = "USE " + $newdb + "; " +
            "BACKUP DATABASE " + $newdb +
            " TO DISK = '" + $BACKUPDIRECTORY + "\" + $bkfile + "'" +
            " WITH FORMAT, " +
            " MEDIANAME = 'DISK', " +
            " NAME = 'Full Backup of " + $newdb + "'; "
        $SqlCmd.CommandText = $sql
        $SqlCmd.CommandType = [System.Data.CommandType]::Text
        [void]$SqlCmd.ExecuteNonQuery()
        $SqlConnection.Close()
    }
    catch {
        Write-Host "!! Error while creating BAK for " $newdb
        $error = $_.Exception.ToString()
        $error >> ($dbDirectory + "errors.txt")
    }
}

#===============================================
# Main script body
#===============================================
try {
    $USER = read-host -Prompt "User"
    $PWD = read-host -Prompt "Password" -AsSecureString
    $PWD = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($PWD))
    $SERVER = read-host -Prompt "SQL Server Name"
    $DATABASE = read-host -Prompt "Existing Database Name"
    $newdb = read-host -Prompt "New Database Name"

    Write-Host "+-- Creating database backup of" $DATABASE "--+"

    $workingDirectory = Split-Path -Path $MyInvocation.MyCommand.Definition -Parent
    $dbDirectory = $workingDirectory + "\" + $newdb + "\"
    $dataDirectory = $dbDirectory + "data\"
    if (!(test-path -pathtype container $dataDirectory)) {
        mkdir $dataDirectory
    }

    # Generate SQL files for creating database, schemas, tables and data inserts
    Write-Host "+-- Generate SQL --+"
    CreateSqlFiles $WHERE_CLAUSE

    # Execute the SQL to create a new database and load the data
    Write-Host "+-- Load Database --+"
    LoadDatabase

    # Create a backup of the database
    Write-Host "+-- Backup Database --+"
    BackupDatabase

    Write-Host "+-- Complete --+"
}
catch {
    Write-Host "!! Backup Failed. See errors.txt for details. !!"
    $_.Exception.ToString() >> ($dbDirectory + "errors.txt")
}