· 8 years ago · May 20, 2018, 06:34 PM
1#=========================================================================
2# Database Extract and Backup -
3# This script will make create/insert SQL for user tables and data,
4# execute it to create a new database and create a BAK of the new database.
5#=========================================================================
6
7# The directory where the BAK will be saved
8$BACKUPDIRECTORY = "c:\temp"
9# The number of INSERTs in a transactional batch
10$BATCHSIZE = 50
11# Where clause to limit data selection
12$WHERE_CLAUSE = ""
13
14function GetSqlServerDataPath {
15 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
16 $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=master;Password=$PWD;User ID=$USER"
17 $SqlConnection.Open()
18 $SqlCmd = $SqlConnection.CreateCommand()
19 $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;"
20 $SqlCmd.CommandType = [System.Data.CommandType]::Text
21 $rdr = $SqlCmd.ExecuteReader()
22 $ord = $rdr.GetOrdinal("path")
23 while ($rdr.Read()) {
24 $path += @($rdr.GetString($ord))
25 }
26 $SqlConnection.Close()
27 return $path
28}
29
30function GetRowData($tablename, $whereclause) {
31 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
32 $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$DATABASE;Password=$PWD;User ID=$USER"
33 $SqlConnection.Open()
34 $SqlCmd = $SqlConnection.CreateCommand()
35 if ([System.String]::IsNullOrEmpty($whereclause)) {
36 $SqlCmd.CommandText = "select * from " + $tablename + " with (nolock) "
37 }
38 else {
39 $SqlCmd.CommandText = "select * from " + $tablename + " with (nolock) where " + $whereclause
40 }
41 $SqlCmd.CommandType = [System.Data.CommandType]::Text
42 $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
43 $SqlAdapter.SelectCommand = $SqlCmd
44 $result = New-Object System.Data.DataSet
45 [void]$SqlAdapter.Fill($result)
46 $SqlConnection.Close()
47 return $result
48}
49
50function CreateSqlFiles ($whereClause){
51 $schemalist = New-Object System.Collections.ArrayList
52 $tablelist = @{}
53
54 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
55 $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$DATABASE;Password=$PWD;User ID=$USER"
56 $SqlConnection.Open()
57 $SqlCmd = $SqlConnection.CreateCommand()
58 $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' "
59 $SqlCmd.CommandType = [System.Data.CommandType]::Text
60 $rdr = $SqlCmd.ExecuteReader()
61 $ord1 = $rdr.GetOrdinal("tablename")
62 $ord2 = $rdr.GetOrdinal("schemaname")
63 while ($rdr.Read()) {
64 $schemaname = $rdr.GetString($ord2)
65 if (!$schemalist.Contains($schemaname)) {
66 [void]$schemalist.Add($schemaname)
67 }
68
69 $tablename = $rdr.GetString($ord1)
70 if (!$tablelist.ContainsKey($tablename)) {
71 $tablelist.Set_Item($tablename, '[' + $schemaname + '].[' + $tablename + ']')
72 }
73 }
74 $SqlConnection.Close()
75
76 # Generate database DDL
77 GenerateDatabaseSql $newdb
78
79 # Generate schema DDL
80 foreach ($schema in $schemalist) {
81 if ($schema -ne 'dbo') {
82 GenerateSchemaSql $schema
83 }
84 }
85
86 foreach($table in $tablelist.GetEnumerator()) {
87 Write-Host "Processing ddl/dml for table" $table.Value
88
89 # Generate table DDL
90 CreateDdl($table.Name)
91
92 # Generate table DML
93 CreateDml $table.Value $whereClause
94 }
95}
96
97function CreateDdl($table) {
98 $sql = New-Object System.Text.StringBuilder
99 [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] ")
100 [void]$sql.Append("from sys.objects so ")
101 [void]$sql.Append("cross apply ")
102 [void]$sql.Append(" (SELECT ")
103 [void]$sql.Append(" ', ['+column_name+'] ' + ")
104 [void]$sql.Append(" data_type + case data_type ")
105 [void]$sql.Append(" when 'sql_variant' then '' ")
106 [void]$sql.Append(" when 'text' then '' ")
107 [void]$sql.Append(" when 'ntext' then '' ")
108 [void]$sql.Append(" when 'xml' then '' ")
109 [void]$sql.Append(" when 'image' then '' ")
110 [void]$sql.Append(" when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'")
111 [void]$sql.Append(" else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +")
112 [void]$sql.Append(" case when exists ( ")
113 [void]$sql.Append(" select [object_id] from sys.columns ")
114 [void]$sql.Append(" where object_name([object_id])=so.name ")
115 [void]$sql.Append(" and name=column_name ")
116 [void]$sql.Append(" and columnproperty([object_id], name, 'IsIdentity') = 1 ")
117 [void]$sql.Append(" ) then ")
118 [void]$sql.Append(" 'IDENTITY(' + ")
119 [void]$sql.Append(" cast(isnull(ident_seed(so.name),1) as varchar) + ',' + ")
120 [void]$sql.Append(" cast(isnull(ident_incr(so.name),1) as varchar) + ')' ")
121 [void]$sql.Append(" else '' ")
122 [void]$sql.Append(" end + ' ' + ")
123 [void]$sql.Append(" (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + ")
124 [void]$sql.Append(" case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END ")
125 [void]$sql.Append(" from information_schema.columns where table_name = so.name ")
126 [void]$sql.Append(" order by ordinal_position ")
127 [void]$sql.Append(" FOR XML PATH('')) o (list) ")
128 [void]$sql.Append("left join information_schema.table_constraints tc ON tc.Table_name = so.Name ")
129 [void]$sql.Append("AND tc.Constraint_Type = 'PRIMARY KEY' ")
130 [void]$sql.Append("cross apply ")
131 [void]$sql.Append(" (select ', [' + Column_Name + ']' ")
132 [void]$sql.Append(" FROM information_schema.key_column_usage kcu ")
133 [void]$sql.Append(" WHERE kcu.Constraint_Name = tc.Constraint_Name ")
134 [void]$sql.Append(" ORDER BY ")
135 [void]$sql.Append(" ORDINAL_POSITION ")
136 [void]$sql.Append(" FOR XML PATH('')) j (list) ")
137 [void]$sql.Append("left join sys.schemas ss ON ss.schema_id = so.schema_id ")
138 [void]$sql.Append("where type = 'U' ")
139 [void]$sql.Append("AND so.name = @tablename AND o.list <> ''")
140
141 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
142 $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$DATABASE;Password=$PWD;User ID=$USER"
143 $SqlConnection.Open()
144 $SqlCmd = $SqlConnection.CreateCommand()
145 $SqlCmd.CommandText = $sql.ToString()
146 $SqlCmd.CommandType = [System.Data.CommandType]::Text
147 [void]$SqlCmd.Parameters.AddWithValue("@tablename", $table)
148 $rdr = $SqlCmd.ExecuteReader()
149 $ord = $rdr.GetOrdinal("SQLString")
150 while ($rdr.Read()) {
151 $sqlstr = $rdr.GetString($ord)
152 if (![System.String]::IsNullOrEmpty($sqlstr)) {
153 $tableddl += @($sqlstr)
154 }
155 }
156 $SqlConnection.Close()
157
158 try {
159 $stream = [System.IO.File]::AppendText($dbDirectory + $newdb + "_tables.sql")
160 $stream.WriteLine($tableddl[0])
161 $stream.Close()
162 }
163 catch {
164 Write-Host "!! Error while generating DDL for " $table.Name
165 $error = $_.Exception.ToString()
166 $error >> ($dbDirectory + "errors.txt")
167 }
168 finally {
169 if ($stream -ne $null) {
170 $stream.Close()
171 }
172 }
173}
174
175function CreateDml($table, $whereClause) {
176 Write-Host " where" $whereClause
177
178 try {
179 $ds = GetRowData $table $whereClause
180 if ($ds.Tables.Count -eq 0) {
181 Write-Host " table not found"
182 return
183 }
184 if ($ds.Tables[0].Rows.Count -eq 0) {
185 Write-Host " 0 records -- skipping dml file"
186 return
187 }
188
189 $stream = [System.IO.File]::AppendText($dataDirectory + $table + ".sql")
190 $values = New-Object System.Collections.ArrayList
191 $columns = New-Object System.Collections.ArrayList
192
193 Write-Host " " $ds.Tables[0].Rows.Count "records"
194
195 $stream.WriteLine("USE " + $newdb + ";")
196 $stream.WriteLine("SET NOCOUNT ON;")
197 $stream.WriteLine("SET XACT_ABORT ON;")
198 $stream.WriteLine("IF OBJECTPROPERTY(OBJECT_ID('" + $table + "'), 'TableHasIdentity') = 1 ")
199 $stream.WriteLine(" SET IDENTITY_INSERT " + $table + " ON")
200 $stream.WriteLine()
201
202 $batchcount = 1
203 $counter = 1
204 foreach ($dr in $ds.Tables[0].Rows) {
205 if ($counter -eq 1) {
206 $stream.WriteLine("BEGIN TRANSACTION;")
207 }
208
209 foreach ($dc in $ds.Tables[0].Columns) {
210 $colname = "[" + $dc + "]"
211 [void]$columns.Add($colname)
212
213 $datatype = $dc.DataType.ToString()
214 if ($datatype -eq "System.String" -or
215 $datatype -eq "System.DateTime" -or
216 $datatype -eq "System.Guid" -or
217 $datatype -eq "System.Char") {
218 $str = $dr[$dc].ToString()
219 if ($dr.IsNull($dc)) {
220 [void]$values.Add("null")
221 }
222 else {
223 $str = SqlEscape($dr[$dc].ToString())
224 [void]$values.Add("'" + $str + "'")
225 }
226 }
227 elseif ($dc.DataType.ToString() -eq "System.Boolean") {
228 $str = $dr[$dc].ToString()
229 if ($dr.IsNull($dc)) {
230 [void]$values.Add("null")
231 }
232 elseif ($str -eq "True") {
233 [void]$values.Add("1")
234 }
235 else {
236 [void]$values.Add("0")
237 }
238 }
239 elseif ($dc.DataType.ToString() -eq "System.Byte[]") {
240 if ($dr.IsNull($dc)) {
241 [void]$values.Add("null")
242 }
243 else {
244 $output = New-Object System.Text.StringBuilder
245 [void]$output.Append("0x")
246 $count = $dr[$dc].Length
247 for ($i = 0; $i -le $count-1; $i++) {
248 $hex = "{0:x}" -f $dr[$dc][$i]
249 [void]$output.Append($hex.PadLeft(2, "0")) # Pad any single digits
250 }
251 [void]$values.Add("CONVERT(varbinary(max), '" + $output.ToString() + "', 1)")
252 }
253 }
254 else {
255 $str = $dr[$dc].ToString()
256 if ($dr.IsNull($dc)) {
257 [void]$values.Add("null")
258 }
259 else {
260 [void]$values.Add($dr[$dc].ToString())
261 }
262 }
263 }
264 $stream.WriteLine("INSERT INTO " + $table + " (" + [System.String]::Join(",", $columns) + ") ")
265 $stream.WriteLine(" VALUES (" + [System.String]::Join(",", $values) + "); ")
266
267 $values.Clear()
268 $columns.Clear()
269
270 if ($counter -eq $BATCHSIZE) {
271 $stream.WriteLine("COMMIT;")
272 $stream.WriteLine("RAISERROR (N'" + $table + ": Insert Batch: " + $batchcount + "...Done', 10, 1) WITH NOWAIT;")
273 $stream.WriteLine()
274 $counter = 1
275 $batchcount++
276 }
277 else {
278 $counter++
279 }
280 }
281
282 if ($counter -lt $BATCHSIZE -and $counter -gt 1) {
283 $stream.WriteLine("COMMIT;")
284 $stream.WriteLine("RAISERROR (N'" + $table + ": Insert Batch: " + $batchcount + "...Done', 10, 1) WITH NOWAIT;")
285 $stream.WriteLine()
286 }
287 $stream.WriteLine("IF OBJECTPROPERTY(OBJECT_ID('" + $table + "'), 'TableHasIdentity') = 1 ")
288 $stream.WriteLine(" SET IDENTITY_INSERT " + $table + " OFF;")
289 }
290 catch {
291 Write-Host "!! Error while generating DML for " $table
292 $error = $_.Exception.ToString()
293 $error >> ($dbDirectory + "errors.txt")
294 }
295 finally {
296 if ($stream -ne $null) {
297 $stream.Close()
298 }
299 }
300}
301
302function GetTableColumns($table) {
303 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
304 $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$DATABASE;Password=$PWD;User ID=$USER"
305 $SqlConnection.Open()
306 $SqlCmd = $SqlConnection.CreateCommand()
307 $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 "
308 $SqlCmd.CommandType = [System.Data.CommandType]::Text
309 [void]$SqlCmd.Parameters.AddWithValue("@tablename", $table)
310 $rdr = $SqlCmd.ExecuteReader()
311 $ord = $rdr.GetOrdinal("name")
312 while ($rdr.Read()) {
313 $cols += @($rdr.GetString($ord))
314 }
315 $SqlConnection.Close()
316 return $cols
317}
318
319function SqlEscape($str) {
320 return $str -replace "'", "''"
321}
322
323function GenerateSchemaSql($schemaname) {
324 try {
325 $stream = [System.IO.File]::AppendText($dbDirectory + $newdb + "_schema.sql")
326 $stream.WriteLine("USE " + $newdb + ";")
327 $stream.WriteLine("IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = '" + $schemaname + "')")
328 $stream.WriteLine("BEGIN ")
329 $stream.WriteLine(" EXECUTE( 'CREATE SCHEMA [" + $schemaname + "] AUTHORIZATION [dbo]') ")
330 $stream.WriteLine("END; ")
331 $stream.WriteLine()
332 }
333 catch {
334 Write-Host "!! Error while generating CREATE SCHEMA for " $schemaname
335 $error = $_.Exception.ToString()
336 $error >> ($dbDirectory + "errors.txt")
337 }
338 finally {
339 if ($stream -ne $null) {
340 $stream.Close()
341 }
342 }
343}
344
345function GenerateDatabaseSql($newdb) {
346 try {
347 $datapath = GetSqlServerDataPath
348 $stream = [System.IO.File]::AppendText($dbDirectory + $newdb + "_database.sql")
349 $stream.WriteLine("USE master;")
350 $stream.WriteLine("IF DB_ID (N'" + $newdb + "') IS NOT NULL")
351 $stream.WriteLine("BEGIN ")
352 $stream.WriteLine(" DROP DATABASE " + $newdb + ";")
353 $stream.WriteLine("END;")
354 $stream.WriteLine("CREATE DATABASE " + $newdb)
355 $stream.WriteLine(" ON ( NAME = " + $newdb + "_dat, ")
356 $stream.WriteLine(" FILENAME = '" + $datapath + $newdb + ".mdf', SIZE=100 ) ")
357 $stream.WriteLine(" LOG ON ( NAME = " + $newdb + "_log, ")
358 $stream.WriteLine(" FILENAME = '" + $datapath + $newdb + ".ldf', SIZE=10 ); ")
359 $stream.WriteLine()
360 }
361 catch {
362 Write-Host "!! Error while generating CREATE DATABASE for " $newdb
363 $error = $_.Exception.ToString()
364 $error >> ($dbDirectory + "errors.txt")
365 }
366 finally {
367 if ($stream -ne $null) {
368 $stream.Close()
369 }
370 }
371}
372
373function GetFileContents($filename) {
374 [System.IO.FileInfo]$file = New-Object System.IO.FileInfo ($filename)
375 $stream = $file.OpenText()
376 $contents = $stream.ReadToEnd()
377 $stream.Close()
378 return $contents
379}
380
381function LoadDatabase() {
382 # create database
383 Write-Host "Creating database" $newdb
384 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
385 $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=master;Password=$PWD;User ID=$USER"
386 $SqlConnection.Open()
387 $SqlCmd = $SqlConnection.CreateCommand()
388 $sql = GetFileContents ($dbDirectory + $newdb + "_database.sql")
389 $SqlCmd.CommandText = $sql
390 $SqlCmd.CommandType = [System.Data.CommandType]::Text
391 [void]$SqlCmd.ExecuteNonQuery()
392 $SqlConnection.Close()
393
394 # create schema
395 Write-Host "Creating schemas for" $newdb
396 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
397 $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=master;Password=$PWD;User ID=$USER"
398 $SqlConnection.Open()
399 $SqlCmd = $SqlConnection.CreateCommand()
400 $sql = GetFileContents ($dbDirectory + $newdb + "_schema.sql")
401 $SqlCmd.CommandText = $sql
402 $SqlCmd.CommandType = [System.Data.CommandType]::Text
403 [void]$SqlCmd.ExecuteNonQuery()
404 $SqlConnection.Close()
405
406 # create Tables
407 Write-Host "Creating tables"
408 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
409 $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$newdb;Password=$PWD;User ID=$USER"
410 $SqlConnection.Open()
411 $SqlCmd = $SqlConnection.CreateCommand()
412 $sql = GetFileContents ($dbDirectory + $newdb + "_tables.sql")
413 $SqlCmd.CommandText = $sql
414 $SqlCmd.CommandType = [System.Data.CommandType]::Text
415 [void]$SqlCmd.ExecuteNonQuery()
416 $SqlConnection.Close()
417
418 # insert data
419 $dmlfiles = [System.IO.Directory]::GetFiles($dataDirectory)
420 foreach ($file in $dmlfiles) {
421 Write-Host "Inserting records for table" $file
422 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
423 $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$newdb;Password=$PWD;User ID=$USER"
424 $SqlConnection.Open()
425 $SqlCmd = $SqlConnection.CreateCommand()
426 $sql = GetFileContents $file
427 $SqlCmd.CommandText = $sql
428 $SqlCmd.CommandType = [System.Data.CommandType]::Text
429 [void]$SqlCmd.ExecuteNonQuery()
430 $SqlConnection.Close()
431 }
432}
433
434function BackupDatabase {
435 try {
436 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
437 $SqlConnection.ConnectionString = "Data Source=$SERVER;Initial Catalog=$newdb;Password=$PWD;User ID=$USER"
438 $SqlConnection.Open()
439
440 $timestamp = Get-Date -format yyyyMMddHHmmss
441 $bkfile = $newdb + "_" + $timestamp + ".bak"
442
443 Write-Host "Backup dir: " $BACKUPDIRECTORY
444 Write-Host "Backup file: " $bkfile
445
446 $SqlCmd = $SqlConnection.CreateCommand()
447 $sql = "USE " + $newdb + "; " +
448 "BACKUP DATABASE " + $newdb +
449 " TO DISK = '" + $BACKUPDIRECTORY + "\" + $bkfile + "'" +
450 " WITH FORMAT, " +
451 " MEDIANAME = 'DISK', " +
452 " NAME = 'Full Backup of " + $newdb + "'; "
453 $SqlCmd.CommandText = $sql
454 $SqlCmd.CommandType = [System.Data.CommandType]::Text
455 [void]$SqlCmd.ExecuteNonQuery()
456 $SqlConnection.Close()
457 }
458 catch {
459 Write-Host "!! Error while creating BAK for " $newdb
460 $error = $_.Exception.ToString()
461 $error >> ($dbDirectory + "errors.txt")
462 }
463}
464
465#===============================================
466# Main script body
467#===============================================
468try {
469 $USER = read-host -Prompt "User"
470 $PWD = read-host -Prompt "Password" -AsSecureString
471 $PWD = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($PWD))
472 $SERVER = read-host -Prompt "SQL Server Name"
473 $DATABASE = read-host -Prompt "Existing Database Name"
474 $newdb = read-host -Prompt "New Database Name"
475
476 Write-Host "+-- Creating database backup of" $DATABASE "--+"
477
478 $workingDirectory = Split-Path -Path $MyInvocation.MyCommand.Definition -Parent
479 $dbDirectory = $workingDirectory + "\" + $newdb + "\"
480 $dataDirectory = $dbDirectory + "data\"
481 if (!(test-path -pathtype container $dataDirectory)) {
482 mkdir $dataDirectory
483 }
484
485 # Generate SQL files for creating database, schemas, tables and data inserts
486 Write-Host "+-- Generate SQL --+"
487 CreateSqlFiles $WHERE_CLAUSE
488
489 # Execute the SQL to create a new database and load the data
490 Write-Host "+-- Load Database --+"
491 LoadDatabase
492
493 # Create a backup of the database
494 Write-Host "+-- Backup Database --+"
495 BackupDatabase
496
497 Write-Host "+-- Complete --+"
498}
499catch {
500 Write-Host "!! Backup Failed. See errors.txt for details. !!"
501 $_.Exception.ToString() >> ($dbDirectory + "errors.txt")
502}