PowerShell.com
Library » Script Library » SQL Server » CreateDB-MSSQL-UsingSMO

CreateDB-MSSQL-UsingSMO

Previous | Next | View all files | View Slideshow
posted by Richard Giles
10-06-2008
Downloads: 519
File size: 4kB
Views: 2,587
Embed
CreateDB-MSSQL-UsingSMO
  1. ## ===================================================================== 
  2. ## Title       : CreateDB-MSSQL-UsingSMO 
  3. ## Description : Create and empty database, drop if existing 
  4. ## Author      : Idera 
  5. ## Date        : 6/27/2008 
  6. ## Input       : -server <server\instance> 
  7. ##               -dbName <database name> 
  8. ##               -verbose  
  9. ##               -debug     
  10. ## Output      : Formatted table with database name and creation date 
  11. ## Usage            : PS> . CreateDB-MSSQL-UsingSMO -server MyServer -dbName SMOTestDB -v -d 
  12. ## Notes            : Adapted from Allen White script 
  13. ## Tag            : SQL Server, SMO, Create database 
  14. ## Change Log  : 
  15. ## ===================================================================== 
  16.  
  17. param 
  18. ( 
  19.       [string]$server = "(local)", 
  20.     [string]$dbName = "SMOTestDB", 
  21.     [switch]$verbose, 
  22.     [switch]$debug 
  23. ) 
  24.  
  25. function main() 
  26. { 
  27.     if ($verbose) {$VerbosePreference = "Continue"} 
  28.     if ($debug) {$DebugPreference = "Continue"} 
  29.     CreateDB-MSSQL-UsingSMO $server $dbName  
  30. } 
  31.  
  32. function CreateDB-MSSQL-UsingSMO($server, $dbName) 
  33. { 
  34.     trap [Exception]  
  35.     { 
  36.         write-error $("TRAPPED: " + $_.Exception.Message); 
  37.         continue; 
  38.     } 
  39.      
  40.     # Load-SMO assemblies 
  41.     [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 
  42.     [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") 
  43.     [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")  
  44.     [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")  
  45.      
  46.     # Get a server object for the server instance 
  47.     Write-Debug "Creating SMO Server object for $server" 
  48.     $NamedInstance = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server ` 
  49.         -argumentList $server 
  50.      
  51.     cls 
  52.      
  53.     # Connect to the server with Windows Authentication and drop database if exist 
  54.     # TIP: using PowerShell "not equal" operator 
  55.     if ($NamedInstance.Databases[$dbName] -ne $null)  
  56.     { 
  57.         Write-Debug "The test database already exists on $DefaultServer" 
  58.         Write-Debug "Deleting it now..." 
  59.         $NamedInstance.Databases[$dbName].drop() 
  60.     } 
  61.      
  62.     # Instantiate a database object 
  63.     Write-Debug "Createing SMO server, database and filegroup objects..." 
  64.     $namedInstance = new-object -typename Microsoft.SqlServer.Management.Smo.Server ` 
  65.         -argumentlist $server 
  66.     $database = new-object -typename Microsoft.SqlServer.Management.Smo.Database ` 
  67.         -argumentlist $namedInstance, $dbName 
  68.     $filegroup = new-object -typename Microsoft.SqlServer.Management.Smo.FileGroup ` 
  69.         -argumentlist $database, "PRIMARY" 
  70.  
  71.     # Add the PRIMARY filegroup to the database 
  72.     $database.FileGroups.Add($filegroup) 
  73.      
  74.     # Instantiate the data file object and add it to the PRIMARY filegroup 
  75.     $dbfile = $dbName + "_Data" 
  76.     $dbdfile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($filegroup, $dbfile) 
  77.     $filegroup.Files.Add($dbdfile) 
  78.      
  79.     Write-Debug "Set properties of the data and log file" 
  80.      
  81.     #Set the properties of the data file 
  82.     $masterDBPath = $namedInstance.Information.MasterDBPath 
  83.     $dbdfile.FileName = $masterDBPath + "\" + $dbfile + ".mdf" 
  84.     $dbdfile.Size = [double](25.0 * 1024.0) 
  85.     $dbdfile.GrowthType = "Percent" 
  86.     $dbdfile.Growth = 25.0 
  87.     $dbdfile.MaxSize = [double](100.0 * 1024.0) 
  88.      
  89.     #Instantiate the log file object and set its properties 
  90.     $masterDBLogPath = $namedInstance.Information.MasterDBLogPath 
  91.     $logfile = $dbName + "_Log" 
  92.     $dblfile = new-object ('Microsoft.SqlServer.Management.Smo.LogFile') ($database, $logfile) 
  93.     $dblfile.FileName = $masterDBLogPath + "\" + $logfile + ".ldf" 
  94.     $dblfile.Size = [double](10.0 * 1024.0) 
  95.     $dblfile.GrowthType = "Percent" 
  96.     $dblfile.Growth = 25.0 
  97.  
  98.     # Create the new database on the server 
  99.     $Database.Create() 
  100.      
  101.     # List the database on the server that was just added to confirm that it was added 
  102.     # TIP: using PowerShell to pipe an object list to a Where-Object filtering on a variable 
  103.     #      and then building a formated table with properties as output to the console 
  104.     $NamedInstance.Databases | Where-Object {$_.name -eq "$dbName"} | Format-Table -property name, createdate 
  105. } 
  106.  
  107. main 

Create a SQL Server empty database.

Concentrated Tech NSoftware Dell Compellent Sponsored by Idera and Concentrated Tech and NSoftware and Dell Compellent
Copyright 2011 PowerShell.com. All rights reserved.