forked from Iristyle/Midori
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Sql.psm1
1051 lines (892 loc) · 34.2 KB
/
Sql.psm1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
function Get-CurrentDirectory
{
$thisName = $MyInvocation.MyCommand.Name
[IO.Path]::GetDirectoryName((Get-Content function:$thisName).File)
}
$script:typesLoaded = $false
function Load-Types
{
if ($script:typesLoaded) { return }
#Requires SQL SMO goop - http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16177
#requires MSXML 6, SQL CLR types and SQL Native Client
#9.0 needed for 2005, 10.0 needed for 2008
Add-Type -AssemblyName 'System.Data',
('Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, ' +
'PublicKeyToken=89845dcd8080cc91'),
('Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, ' +
'PublicKeyToken=89845dcd8080cc91'),
('Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral,' +
' PublicKeyToken=89845dcd8080cc91'),
('Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, ' +
'PublicKeyToken=89845dcd8080cc91'),
('Microsoft.SqlServer.SmoExtended, Version=10.0.0.0, Culture=neutral, ' +
'PublicKeyToken=89845dcd8080cc91')
$script:typesLoaded = $true
}
function Start-ServiceAndWait
{
param
(
[Parameter(Mandatory=$true)]
[string]
[ValidateScript({Get-Service | ? { $_.Name -eq $serviceName }})]
$ServiceName,
[Parameter(Mandatory=$false)]
[int]
[ValidateRange(1, 30)]
$MaximumWaitSeconds = 15
)
$service = Get-Service | ? { $_.Name -eq $serviceName }
if (-not $service) { throw "Service $ServiceName does not exist" }
if ($service.Status -ne 'Running')
{
$identity = [Security.Principal.WindowsPrincipal] `
([Security.Principal.WindowsIdentity]::GetCurrent())
$isAdmin = $identity.IsInRole(
[Security.Principal.WindowsBuiltInRole]::Administrator)
if (! $isAdmin) { throw "Must be in Administrator role to start service" }
$service | Start-Service
$seconds = 0
while (($seconds -lt $MaximumWaitSeconds) -and `
((Get-Service $serviceName).Status -ne 'Running'))
{
Write-Host "Waiting on [$serviceName] to start..."
sleep 1
$seconds++
}
if ((Get-Service $serviceName).Status -ne 'Running')
{
throw { "Failed to start service in $seconds seconds" }
}
}
Write-Host -ForeGroundColor Magenta `
"Service [$serviceName] is running"
}
function New-SqlDatabase
{
<#
.Synopsis
Will generate a new SQL server database using SMO and a given script.
.Description
Any additional customization may be performed by providing a
ScriptBlock to the CustomizationCallback.
The given service is checked to ensure it's running and then the script
is executed. If the database already exists, an error occurs.
After initial creation, the database is set into single user mode to
lock the database from additional access, and restored to multiple user
access (configurable) after shrinking and running user customizations.
The database is shrunk and detached after the cmdlet is run, but the
detachment can be disabled with the use of a switch.
Requires that SMO and some SQL server be installed on the local machine
.Parameter CreateScriptPath
The path of the SQL creation script.
Note that the script should only generally execute against the given
DatabaseName. Only the database named DatabaseName will be detached
from the server.
.Parameter DatabasePath
The final output database path.
.Parameter DatabaseName
The final output database filename. Both MDF and LDF will assume this
name.
.Parameter ServiceName
The name of the SQL Server service name - will default to
MSSQL$SQLEXPRESS if left unspecified.
.Parameter InstanceName
The name of the SQL server instance. By default, .\SQLEXPRESS
.Parameter CustomizationCallback
A ScriptBlock that will be given the instance of the database for the
explicit purpose of additional customization before the database is
created. The instance is of type
Microsoft.SqlServer.Management.Smo.Database.
.Parameter UserAccess
An enumeration value that determines whether the database should be put
into Single, Restricted or Multiple user access. As the database is
built, single user mode is configured to prevent additional concurrent
access from other build processes, and it is restored to multiple just
before detaching from the SQL service.
.Parameter NoDetach
Will disable the database from being detached after creation. This will
allow the database to be used in, for instance, integration tests.
.Example
New-SqlDatabase -CreateScriptPath .\foo.sql `
-DatabasePath c:\db -DatabaseName 'newdb'
Description
-----------
Will use the default localhost SQLEXPRESS instance and setup a new
SQL MDF on disk, running foo.sql against the given database.
After the script completes, the database files will be placed into
c:\db\newdb.mdf
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string]
[ValidateScript({ Test-Path $_ })]
$CreateScriptPath,
[Parameter(Mandatory=$true)]
[string]
[ValidateScript({ (Test-Path $_) -and (Get-Item $_).PSIsContainer })]
$DatabasePath,
[Parameter(Mandatory=$true)]
[string]
$DatabaseName,
[Parameter(Mandatory=$false)]
[string]
$ServiceName = 'MSSQL$SQLEXPRESS',
[Parameter(Mandatory=$false)]
[string]
$InstanceName = '.\SQLEXPRESS',
[Parameter(Mandatory=$false)]
[ScriptBlock]
$CustomizationCallback,
[Parameter(Mandatory=$false)]
#[Microsoft.SqlServer.Management.Smo.DatabaseUserAccess]
$UserAccess = 0,
[Parameter(Mandatory=$false)]
[Switch]
$NoDetach = $false
)
Load-Types
if (($UserAccess -eq $null) -or (@(0,1,2) -notcontains [int]$UserAccess))
{ throw '$UserAccess must be a valid DatabaseUserAccess value' }
Start-ServiceAndWait $ServiceName
$dbFile = Join-Path $DatabasePath "$DatabaseName.mdf"
if (Test-Path $dbFile) { throw "$dbFile already exists!" }
Write-Host "Creating database [$dbFile] with service [$serviceName]"
try
{
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($InstanceName)
$database = New-Object Microsoft.SqlServer.Management.Smo.Database($server,
$DatabaseName)
$database.FileGroups.Add(
(New-Object Microsoft.SqlServer.Management.Smo.FileGroup(
$database, 'PRIMARY')))
$primaryFileGroup = $dataBase.FileGroups['PRIMARY']
$datafile = New-Object Microsoft.SqlServer.Management.Smo.Datafile(
$primaryFileGroup, $DatabaseName, $dbFile)
$datafile.Size = 4096
$datafile.Growth = 1024
$datafile.GrowthType = [Microsoft.SqlServer.Management.Smo.FileGrowthType]::KB
$options = $database.DatabaseOptions
#set single user mode so that we have exclusive access while we build db
$options.UserAccess =
[Microsoft.SqlServer.Management.Smo.DatabaseUserAccess]::Single
$primaryFileGroup.Files.Add($datafile)
Write-Host "Added PRIMARY filegroup [$dbFile] to database..."
if ($CustomizationCallback)
{
&$CustomizationCallback $database
Write-Host 'Customized database using callback'
}
$database.Create()
Write-Host 'Created database, executing SQL to setup tables and friends...'
#$server.ConnectionContext.StatementTimeout = $TimeoutSeconds
$commands = [IO.File]::ReadAllText($CreateScriptPath)
[Void]$database.ExecuteNonQuery($commands)
Write-Host "Shrinking [$DatabaseName]..."
#DBCC SHRINKFILE / DBCC SHRINKDATABASE
$database.Shrink(0,
[Microsoft.SqlServer.Management.Smo.ShrinkMethod]::TruncateOnly)
#restore multiuser access
$options.UserAccess = $UserAccess
$options.Alter()
}
finally
{
if ($server)
{
if ($DatabaseName -and (-not $NoDetach))
{
#$database.TruncateLog() -- only works on 2005 or earlier
$server.DetachDatabase($DatabaseName, $true)
}
$server.ConnectionContext.Disconnect()
}
}
}
#http://stackoverflow.com/questions/5123423/error-restoring-database-backup-to-new-database-with-smo-and-powershell
function Backup-SqlDatabase
{
<#
.Synopsis
Will generate a copy of an existing SQL server database using the SMO
backup methods.
.Description
The given service is checked to ensure it's running.
The given database name is copied to a backup file using the SMO
Backup class. This will work on a live database; what is generated
is considered a snapshot appropriate for a restore.
The backup mechanism is much faster than Transfer, but may be less
appropriate for a live database.
The written file is named $DatabaseName.bak and is written to the
given BackupPath.
Requires that SMO and some SQL server be installed on the local machine
.Parameter DatabaseName
The original name of the database.
.Parameter BackupPath
The directory to write the backup file to, not including file name.
.Parameter ServiceName
The name of the SQL Server service name - will default to
MSSQL$SQLEXPRESS if left unspecified.
.Parameter InstanceName
The name of the SQL server instance. By default, .\SQLEXPRESS
.Outputs
A string containing the backup filename.
.Example
Backup-SqlDatabase -DatabaseName MyDatabase -BackupPath c:\db
Description
-----------
Will use the default localhost SQLEXPRESS instance and will create a
backup file named c:\db\MyDatabase.bak
Outputs c:\db\MyDatabase.bak
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string]
$DatabaseName,
[Parameter(Mandatory=$true)]
[string]
[ValidateScript({ (Test-Path $_) -and (Get-Item $_).PSIsContainer })]
$BackupPath,
[Parameter(Mandatory=$false)]
[string]
$ServiceName = 'MSSQL$SQLEXPRESS',
[Parameter(Mandatory=$false)]
[string]
$InstanceName = '.\SQLEXPRESS'
)
try
{
$backupFilePath = "$BackupPath\$DatabaseName.bak"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($InstanceName)
$smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup
$smoBackup.Action =
[Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$smoBackup.BackupSetDescription = "Full Backup of $DatabaseName"
$smoBackup.BackupSetName = "$DatabaseName Backup"
$smoBackup.Database = $DatabaseName
$smoBackup.Incremental = $false
$smoBackup.LogTruncation =
[Microsoft.SqlServer.Management.Smo.BackupTruncateLogType]::Truncate
$smoBackup.Devices.AddDevice($backupFilePath,
[Microsoft.SqlServer.Management.Smo.DeviceType]::File)
Write-Host "Generating [$backupFilePath] for [$DatabaseName]"
$smoBackup.SqlBackup($server)
return $backupFilePath
}
finally
{
if ($server) { $server.ConnectionContext.Disconnect() }
}
}
function Restore-SqlDatabase
{
<#
.Synopsis
Will restore a given SQL backup file to a new database using SMO, and
a backup file generated by Backup-SqlDatabase.
.Description
The given service is checked to ensure it's running and then a new
database is created with the given destination name, as restored from
the backup file. If the database already exists, an error occurs.
This is not allowed to replace an existing database of the same name.
The database is detached after the cmdlet is run, unless -NoDetach is
specified.
Requires that SMO and some SQL server be installed on the local machine
.Parameter BackupPath
The complete path to the SQL backup file.
.Parameter DestinationDatabasePath
The final output database path, not including the file name.
.Parameter DestinationDatabaseName
The final output database filename. Both MDF and LDF will assume this
name.
.Parameter NoDetach
Will disable the database from being detached after creation. This will
allow the database to be used in, for instance, integration tests.
Default is to detach the database.
.Parameter KillAllProcesses
Will instruct the SQL Server instance to kill all the processes
associated with the DestinationDatabaseName, should there be any. In
build scenarios, this is not typically needed.
Default is to not kill all processes
.Parameter ServiceName
The name of the SQL Server service name - will default to
MSSQL$SQLEXPRESS if left unspecified.
.Parameter InstanceName
The name of the SQL server instance. By default, .\SQLEXPRESS
.Example
Restore-SqlDatabase -BackupPath c:\db\foo.bak `
-DestinationDatabasePath c:\db -DestinationDatabaseName MyDatabase2
Description
-----------
Will use the default localhost SQLEXPRESS instance and setup a new
SQL MDF on disk, based on a restore from the given .bak file.
The database files will be placed into c:\db\MyDatabase2.mdf
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string]
[ValidateScript({ (Test-Path $_) -and (!(Get-Item $_).PSIsContainer) })]
$BackupPath,
[Parameter(Mandatory=$true)]
[string]
[ValidateScript({ (Test-Path $_) -and (Get-Item $_).PSIsContainer })]
$DestinationDatabasePath,
[Parameter(Mandatory=$true)]
[string]
$DestinationDatabaseName,
[Parameter(Mandatory=$false)]
[Switch]
$NoDetach = $false,
[Parameter(Mandatory=$false)]
[Switch]
$KillAllProcesses = $false,
[Parameter(Mandatory=$false)]
[string]
$ServiceName = 'MSSQL$SQLEXPRESS',
[Parameter(Mandatory=$false)]
[string]
$InstanceName = '.\SQLEXPRESS'
)
try
{
$dbFilePath = Join-Path $DestinationDatabasePath "$DestinationDatabaseName.mdf"
$logFilePath = Join-Path $DestinationDatabasePath "$($DestinationDatabaseName)_Log.ldf"
if (Test-Path $dbFilePath) { throw "$dbFilePath already exists!" }
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($InstanceName)
# http://www.sqlmusings.com/2009/06/01/how-to-restore-sql-server-databases-using-smo-and-powershell/
#http://stackoverflow.com/questions/1466651/how-to-restore-a-database-from-c-sharp
$backupDevice = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem(
$BackupPath, 'File')
$smoRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore
$smoRestore.Database = $DestinationDatabaseName
$smoRestore.NoRecovery = $false
$smoRestore.ReplaceDatabase = $true
$smoRestore.Action =
[Microsoft.SqlServer.Management.Smo.RestoreActionType]::Database
$smoRestore.Devices.Add($backupDevice)
# Get the details from the backup device for the database name
$smoRestoreDetails = $smoRestore.ReadBackupHeader($server)
#must use logical file name stored in backup - can't construct on the fly
$existingDatabaseName = $smoRestoreDetails.Rows[0]["DatabaseName"]
@(@($existingDatabaseName, $dbFilePath),
@("$($existingDatabaseName)_Log", $logFilePath)) |
% {
[Void]$smoRestore.RelocateFiles.Add(
(New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(
$_[0], $_[1])))
}
if ($server.Databases.Contains($DestinationDatabaseName))
{
throw "Database $DestinationDatabaseName already exists!"
}
if ($KillAllProcesses) { $server.KillAllProcesses() }
Write-Host ("Restoring [$BackupPath] to [$DestinationDatabaseName]" +
"at [$dbFilePath]")
$smoRestore.SqlRestore($server)
}
finally
{
if ($server)
{
if ($DestinationDatabaseName -and (-not $NoDetach))
{
$server.DetachDatabase($DestinationDatabaseName, $true)
}
$server.ConnectionContext.Disconnect()
}
}
}
function Transfer-SqlDatabase
{
<#
.Synopsis
Will generate a copy of an existing SQL server database using the SMO
Transfer class.
.Description
The given service is checked to ensure it's running and then a new
database is created with the given destination name. If the database
already exists, an error occurs.
The given database name is copied using the SMO Transfer object to a
new database. This will work on a live database, and is therefore
slower than a simple backup / restore.
The database is shrunk and detached after the cmdlet is run.
Requires that SMO and some SQL server be installed on the local machine
.Parameter DatabaseName
The original name of the database.
.Parameter DestinationDatabasePath
The final output database path, not including the file name.
.Parameter DestinationDatabaseName
The final output database filename. Both MDF and LDF will assume this
name.
.Parameter NoDetach
Will disable the database from being detached after creation. This will
allow the database to be used in, for instance, integration tests.
.Parameter ServiceName
The name of the SQL Server service name - will default to
MSSQL$SQLEXPRESS if left unspecified.
.Parameter InstanceName
The name of the SQL server instance. By default, .\SQLEXPRESS
.Example
Transfer-SqlDatabase -DatabaseName MyDatabase `
-DestinationDatabasePath c:\db -DestinationDatabaseName MyDatabase2
Description
-----------
Will use the default localhost SQLEXPRESS instance and setup a new
SQL MDF on disk, copying the given database name into a new name.
The database files will be placed into c:\db\newdb.mdf
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string]
$DatabaseName,
[Parameter(Mandatory=$true)]
[string]
[ValidateScript({ (Test-Path $_) -and (Get-Item $_).PSIsContainer })]
$DestinationDatabasePath,
[Parameter(Mandatory=$true)]
[string]
$DestinationDatabaseName,
[Parameter(Mandatory=$false)]
[Switch]
$NoDetach = $false,
[Parameter(Mandatory=$false)]
[string]
$ServiceName = 'MSSQL$SQLEXPRESS',
[Parameter(Mandatory=$false)]
[string]
$InstanceName = '.\SQLEXPRESS'
)
Load-Types
Start-ServiceAndWait $ServiceName
$dbFilePath = Join-Path $DestinationDatabasePath "$DestinationDatabaseName.mdf"
if (Test-Path $dbFilePath) { throw "$dbFilePath already exists!" }
Write-Host "Copying [$DatabaseName] to [$dbFilePath] with service [$serviceName]"
try
{
#http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.transfer_members(v=sql.100)
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($InstanceName)
$server.SetDefaultInitFields($true)
#SMO is a real PITA, since Transfer.CreateTargetDatabase doesn't accept a
#filename, but only a root path, so we have to create a new db by hand
#otherwise the original db can't be backed up to same directory and the
#internal filenames in the MDF get totally bungled
$database = New-Object Microsoft.SqlServer.Management.Smo.Database($server,
$DestinationDatabaseName)
$database.FileGroups.Add(
(New-Object Microsoft.SqlServer.Management.Smo.FileGroup(
$database, 'PRIMARY')))
$primaryFileGroup = $dataBase.FileGroups['PRIMARY']
$datafile = New-Object Microsoft.SqlServer.Management.Smo.Datafile(
$primaryFileGroup, $DatabaseName, $dbFilePath)
$datafile.Size = 4096
$datafile.Growth = 1024
$datafile.GrowthType = [Microsoft.SqlServer.Management.Smo.FileGrowthType]::KB
$primaryFileGroup.Files.Add($datafile)
Write-Host "Added PRIMARY filegroup [$dbFilePath] to database..."
$database.Create()
$transfer = New-Object Microsoft.SqlServer.Management.Smo.Transfer(
$server.Databases[$DatabaseName])
$transfer.CopyAllObjects = $false #this is a *very* misleading property...
$transfer.CopyAllDatabaseTriggers = $true
$transfer.CopyAllDefaults = $true
$transfer.CopyAllFullTextCatalogs = $true
$transfer.CopyAllFullTextStopLists = $true
#weird errors if this is enabled -- service principal already exists
$transfer.CopyAllLogins = $true
$transfer.CopyAllPartitionFunctions = $true
$transfer.CopyAllPartitionSchemes = $true
$transfer.CopyAllPlanGuides = $true
$transfer.CopyAllRoles = $true
$transfer.CopyAllRules = $true
$transfer.CopyAllSchemas = $true
#not available in 2008
#$transfer.CopyAllSearchPropertyLists = $true
#$transfer.CopyAllSequences = $true
$transfer.CopyAllSqlAssemblies = $true
$transfer.CopyAllStoredProcedures = $true
$transfer.CopyAllSynonyms = $true
$transfer.CopyAllTables = $true
$transfer.CopyAllUserDefinedAggregates = $true
$transfer.CopyAllUserDefinedDataTypes = $true
$transfer.CopyAllUserDefinedFunctions = $true
$transfer.CopyAllUserDefinedTableTypes = $true
$transfer.CopyAllUserDefinedTypes = $true
$transfer.CopyAllUsers = $true
$transfer.CopyAllViews = $true
$transfer.CopyAllXmlSchemaCollections = $true
$transfer.CopyData = $true
$transfer.CopySchema = $true
#this cannot be set to true per a bug in TransferData()
#http://stackoverflow.com/questions/6227305/sql-server-copy-database-issue
#$transfer.Options.IncludeDatabaseRoleMemberships = $true
$transfer.Options.Indexes = $true
$transfer.Options.DriAll = $true
$transfer.Options.Permissions = $true
$transfer.Options.SchemaQualify = $true
$transfer.Options.SchemaQualifyForeignKeysReferences = $true
$transfer.Options.Statistics = $true
#$transfer.Options.TargetServerVersion =
# [SqlServer.Management.Smo.SqlServerVersion]::Version90
$transfer.Options.WithDependencies = $true
$transfer.Options.IncludeIfNotExists = $true
$transfer.Options.FullTextIndexes = $true
$transfer.Options.ExtendedProperties = $true
$transfer.DestinationDatabase = $DestinationDatabaseName
$transfer.DestinationServer = $server.Name
#TODO: consider surfacing DestinationLogin / DestinationPassword
#if those are specified, then DestinationLoginSecure is $false
$transfer.DestinationLoginSecure = $true
$transfer.PreserveLogins = $true
$transfer.PreserveDbo = $true
$transfer.Options.ContinueScriptingOnError = $true
Write-Host "Initiating transfer from [$DatabaseName]..."
$transfer.TransferData()
#DBCC SHRINKFILE / DBCC SHRINKDATABASE
Write-Host "Shrinking [$DestinationDatabaseName]..."
$database.Shrink(0,
[Microsoft.SqlServer.Management.Smo.ShrinkMethod]::TruncateOnly)
}
finally
{
if ($server)
{
if ($DestinationDatabaseName -and (-not $NoDetach))
{
$server.DetachDatabase($DestinationDatabaseName, $true)
}
$server.ConnectionContext.Disconnect()
}
}
}
function Copy-SqlDatabase
{
<#
.Synopsis
Will generate a copy of an existing SQL server database using either a
SMO backup/restore, or by using the SMO Transfer class.
.Description
The given service is checked to ensure it's running and then a new
database is created with the given destination name. If the database
already exists, an error occurs.
The given database name is copied using the SMO Transfer object to a
new database. This will work on a live database, and is therefore
slower than a simple backup / restore.
The database is shrunk and detached after the cmdlet is run.
Requires that SMO and some SQL server be installed on the local machine
.Parameter DatabaseName
The original name of the database.
.Parameter DestinationDatabasePath
The final output database path, not including the file name.
.Parameter DestinationDatabaseName
The final output database filename. Both MDF and LDF will assume this
name.
.Parameter CopyMethod
The SMO technique used to copy the database.
* BackupRestore generates a .bak file, then restores to a new database
This is most suitable in a build scenario, where there are no users
attached to the database / executing queries
* Transfer uses the SMO Transfer object, which is much slower, but can
be used against live databases
.Parameter ServiceName
The name of the SQL Server service name - will default to
MSSQL$SQLEXPRESS if left unspecified.
.Parameter InstanceName
The name of the SQL server instance. By default, .\SQLEXPRESS
.Parameter NoDetach
When using the default BackupRestore copy method, this will disable
the new database from being detached after creation.
.Example
Copy-SqlDatabase -DatabaseName MyDatabase `
-DestinationDatabasePath c:\db -DestinationDatabaseName MyDatabase2
Description
-----------
Will use the default localhost SQLEXPRESS instance and setup a new
SQL MDF on disk, copying the given database name into a new name.
The database files will be placed into c:\db\newdb.mdf
By default, the copy will be made with the faster Backup / Restore.
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)]
[string]
$DatabaseName,
[Parameter(Mandatory=$true)]
[string]
[ValidateScript({ (Test-Path $_) -and (Get-Item $_).PSIsContainer })]
$DestinationDatabasePath,
[Parameter(Mandatory=$true)]
[string]
$DestinationDatabaseName,
[Parameter(Mandatory=$false)]
[string]
[ValidateSet('BackupRestore', 'Transfer')]
$CopyMethod = 'BackupRestore',
[Parameter(Mandatory=$false)]
[string]
$ServiceName = 'MSSQL$SQLEXPRESS',
[Parameter(Mandatory=$false)]
[string]
$InstanceName = '.\SQLEXPRESS',
[Parameter(Mandatory=$false)]
[switch]
$NoDetach=$false
)
switch ($CopyMethod)
{
'BackupRestore'
{
$BackupPath = Backup-SqlDatabase -DatabaseName $DatabaseName `
-BackupPath $DestinationDatabasePath -ServiceName $ServiceName `
-InstanceName $InstanceName
Restore-SqlDatabase -BackupPath $BackupPath `
-DestinationDatabasePath $DestinationDatabasePath `
-DestinationDatabaseName $DestinationDatabaseName `
-ServiceName $ServiceName -InstanceName $InstanceName `
-NoDetach:$NoDetach
Remove-Item $BackupPath
}
'Transfer'
{
$params = @{
DatabaseName = $DatabaseName;
DestinationDatabasePath = $DestinationDatabasePath;
DestinationDatabaseName = $DestinationDatabaseName;
ServiceName = $ServiceName;
InstanceName = $InstanceName;
}
Transfer-SqlDatabase @params
}
}
}
function Remove-SqlDatabase
{
<#
.Synopsis
Will detach an existing SQL server database using SMO.
.Description
The given service is checked to ensure it's running and then the script
is executed. If the database already exists, an error occurs.
The database is detached using the DetachDatabase SMO Api call.
Requires that SMO and some SQL server be installed on the local machine
.Parameter DatabaseName
The name of the database to detach.
.Parameter ServiceName
The name of the SQL Server service name - will default to
MSSQL$SQLEXPRESS if left unspecified.
.Parameter InstanceName
The name of the SQL server instance. By default, .\SQLEXPRESS
.Parameter Force
Optionally forces detachment of the database. When leaving a DB in
multi-user mode, it is possible to have lingering connections from
integration tests. This option will ensure detachment of the DB by
forcibly killing all active processes on the database.
.Example
Remove-SqlDatabase -DatabaseName MyDatabase
Description
-----------
Will use the default localhost SQLEXPRESS instance and will detach
MyDatabase.
#>
param(
[Parameter(Mandatory=$true)]
[string]
$DatabaseName,
[Parameter(Mandatory=$false)]
[string]
$ServiceName = 'MSSQL$SQLEXPRESS',
[Parameter(Mandatory=$false)]
[string]
$InstanceName = '.\SQLEXPRESS',
[Parameter(Mandatory=$false)]
[switch]
$Force = $false
)
Load-Types
Start-ServiceAndWait $ServiceName
Write-Host "Detaching $DatabaseName from $InstanceName"
try
{
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($InstanceName)
if($Force)
{
$server.KillAllProcesses($DatabaseName)
$server.KillDatabase($DatabaseName)
}
else
{
$server.DetachDatabase($DatabaseName, $true)
}
}
finally
{
if ($server) { $server.ConnectionContext.Disconnect() }
}
}
function Invoke-SqlFileSmo
{
<#
.Synopsis
Will run a given SQL script via SMO against a given database. Output
from the ServerMessage and InfoMessage events are automatically sent to
Write-Host and Write-Verbose respectively. If the ServerMessage events
represent errors, their output is sent to Write-Error.
.Description
ExecuteNonQuery is used against the database to execute the SQL script.
Requires that SMO be installed on the local machine.
.Parameter Path
The name of the script file to run.
.Parameter Database
The name of the database instance to connect to. Note that is
InstanceName in the other cmdlets and will be renamed in a future ver.
.Parameter UserName
The username used to connect to the database.
.Parameter Password
The password used to connect ot the database.
.Parameter TimeoutSeconds
Defaults to 30 seconds. The timeout of the given script.
.Parameter UseTransaction
Defaults to false. Whether to wrap script execution in a transaction.
.Parameter Width
Defaults to the current $Host.UI.RawUI.BufferSize.Width if available,
otherwise 80
Optional width of output strings.
.Parameter InitialCatalog
Specifies the name of the SQL Database to connect to
.Example
Invoke-SqlFileSmo -Path .\foo.sql -Database MyDb -UserName sa `
-Password secret -UseTransaction -InstanceName '.\SQLEXPRESS'
Description
-----------
Will execute foo.sql against the given database, wrapping it in a
transaction.
#>
param(
[Parameter(Mandatory=$true)]
[string]
[ValidateScript({ (Test-Path $_) -and (!(Get-Item $_).PSIsContainer) })]
$Path,
[Parameter(Mandatory=$true)]
[string]
$Database = '.\SQLEXPRESS',
[Parameter(Mandatory=$true)]
[string]
$UserName,
[Parameter(Mandatory=$true)]
[string]
$Password,
[Parameter(Mandatory=$false)]
[int]
$TimeoutSeconds = 30,
[Parameter(Mandatory=$false)]
[switch]
$UseTransaction = $false,
[Parameter(Mandatory=$false)]
$Width = { if ($Host -and $Host.UI -and $Host.UI.RawUI)
{ $Host.UI.RawUI.BufferSize.Width } else { 80 }},
[Parameter(Mandatory=$false)]
[string]
$InitialCatalog = 'master'
)
Load-Types
$Width = if ($Width -is [ScriptBlock]) { [int](&$Width) }
else { [int]$Width }
$msg = "`n`n[START] - Running $Path against $Database on db $InitialCatalog "
if ($UseTransaction) { $msg += 'with transactions' }
Write-Host -ForeGroundColor Magenta $msg
$eventIds = @()
$serverConnection = $null
try
{
$serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($Database, $UserName, $Password)
$server = New-Object Microsoft.SqlServer.Management.SMO.Server($serverConnection)
#3 potential events to hook - InfoMessage, ServerMessage and StatementExecuted
#StatementExecuted is extremely verbose and contains all SQL executed
#InfoMessage returns same content as ServerMessage, except only for class 0 error
$params = @{
InputObject = $serverConnection;
EventName = 'ServerMessage';
Action = {
$lastError = $Event.SourceEventArgs.Error
if (-not $lastError)
{
Write-Error "[ERROR]: $($Event | Select * | Out-String -Width $Width)"
}
switch ($lastError.Class)
{
0 {
Write-Host "[INFO]: $($lastError.Message)" -ForeGroundColor Yellow
}
default {
Write-Error "[ERROR]: $($lastError | Select * | Out-String -Width $Width)"
}
}
}
}
$eventIds += (Register-ObjectEvent @params).Id
#extremely verbose
$params.EventName = 'InfoMessage'
$params.Action = { Write-Verbose "$($Event.SourceEventArgs)" }
$eventIds += (Register-ObjectEvent @params).Id
if ($InitialCatalog -ne 'master')
{
Write-Host "Setting initial catalog to $InitialCatalog"
$server.ConnectionContext.DatabaseName = $InitialCatalog
}
if ($UseTransaction)
{
Write-Host 'Starting transaction...'
$serverConnection.BeginTransaction()
}
$errors = $global:Error.Count
$server.ConnectionContext.StatementTimeout = $TimeoutSeconds
$affected = $server.ConnectionContext.ExecuteNonQuery([IO.File]::ReadAllText($Path))
Write-Host "[INFO] : Invoke-SqlFileSmo affected [$affected] total records..."