Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #=========================================================================
- # 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")
- }
Add Comment
Please, Sign In to add comment