Welcome to Day 11 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.
Yesterday we took a look at the SMO object model diagram and dug down into servers and databases. Today, let’s play around with databases a bit more.
Adding Databases
Yesterday we looked at some of the database properties that are available. So what’s left to do with databases? Today, let’s add one so that we can play around with it in future episodes of this series. (The basis of this script is from Allen White as he blogged at //sqlblog.com/blogs/allen_white/archive/2008/04/28/create-database-from-powershell.aspx.)
|
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 |
# Inspiration for this script from Allen White as blogged at # //sqlblog.com/blogs/allen_white/archive/2008/04/28/create-database-from-powershell.aspx # get the server $server = New-Object ('Microsoft.SqlServer.Management.SMO.Server') ".\SQL2012" # assign the database name to a variable $MyDBName = "PoSh" $MyDB = $server.Databases[$MyDBName] #if ($MyDB) {$MyDB.Drop()} if (!($MyDB)) # database doesn't exist yet, so create it { Write-Host "Creating database: $MyDBName" # First, get the default locations for the data / log files. $DataLoc = $server.Settings.DefaultFile $LogLoc = $server.Settings.DefaultLog # If these are not set, then use the location of the master db mdf/ldf if ($DataLoc.Length -EQ 0) {$DataLoc = $server.Information.MasterDBPath} if ($LogLoc.Length -EQ 0) {$LogLoc = $server.Information.MasterDBLogPath} # If these paths don't end with a backslash character, add one. if (!$DataLoc.EndsWith("\")) {$DataLoc = $DataLoc + "\"} if (!$LogLoc.EndsWith("\")) {$LogLoc = $LogLoc + "\"} # Get a new database object $MyDB = New-Object ('Microsoft.SqlServer.Management.SMO.Database') ($server, $MyDBName) # Get a new filegroup object $MyPrimaryDBFG = New-Object ('Microsoft.SqlServer.Management.SMO.FileGroup') ($MyDB, 'PRIMARY') # Add the filegroup object to the database object $MyDB.FileGroups.Add($MyPrimaryDBFG) # Best practice is to separate the system objects from the user objects. # Get another filegroup to separate system stuff from user stuff $MySecondaryDBFG= New-Object ('Microsoft.SqlServer.Management.SMO.FileGroup') ($MyDB, 'UserFG') # Add the filegroup object to the database object $MyDB.FileGroups.Add($MySecondaryDBFG) # Create the database files # First, create the data file for holding the system objects, on the primary filegroup. # (also... need to set the primary filegroup to be the default) $MyDBSystemFile = $MyDBName + "_System" # Get a new datafile object $DBSysFile = New-Object ('Microsoft.SqlServer.Management.SMO.DataFile') ($MyPrimaryDBFG, $MyDBSystemFile) # Add it to the Primary filegroup $MyPrimaryDBFG.Files.Add($DBSysFile) # Set the file name to put this file in the same place as the master db files $DBSysFile.FileName = $DataLoc + $MyDBSystemFile + ".MDF" # Make the file size 5MB (sizes are in KB, so multiply here to MB) $DBSysFile.Size = [double](5.0 * 1024.0) # No growth on this file $DBSysFile.GrowthType = "None" # Make this the primary file $DBSysFile.IsPrimaryFile = 'True' # Now create the data file for the user objects $MyDBUserFile = $MyDBName + "_User" # Get a new datafile object $DBUserFile = New-Object ('Microsoft.SqlServer.Management.SMO.Datafile') ($MySecondaryDBFG, $MyDBUserFile) # Add this to the application filegroup $MySecondaryDBFG.Files.Add($DBUserFile) # Set the file name, same path as the system file above $DBUserFile.FileName = $DataLoc + $MyDBUserFile + ".NDF" # We're just playing around, so make this file size 5mb also. $DBUserFile.Size = [double] (5.0 * 1024.0) # Set the file growth to 5mb also. $DBUserFile.GrowthType = "KB" $DBUserFile.Growth = [double] (5.0 * 1024.0) # Set a max size of 100 MB $DBUserFile.MaxSize = [double] (100.0 * 1024.0) # Now we need a log file for this database $MyDBLogFile = $MyDBName + "_Log" $DBLogFile = New-Object ('Microsoft.SqlServer.Management.SMO.LogFile') ($Mydb, $MyDBLogFile) # Add this file to the database $MyDB.LogFiles.Add($DBLogFile) # Set the filename, size, growth $DBLogFile.FileName = $LogLoc + $MyDBLogFile + ".LDF" $DBLogFile.Size = [double] (5.0 * 1024.0) $DBLogFile.GrowthType = "KB" $DBLogFile.Growth = [double] (5.0 * 1024.0) #Create the database in the simple recovery model $MyDB.RecoveryModel = "SIMPLE" #Okay, now we are ready to create the database $MyDB.Create() #And the last step is to make the user filegroup the default $MySecondaryDBFG = $MyDB.FileGroups['UserFG'] $MySecondaryDBFG.IsDefault = $true $MySecondaryDBFG.Alter() $MyDB.Alter() } #Let's see what we've got: $MyDB | ` Select-Object Parent, Name, AutoShrink, LastBackupDate, PageVerify, RecoveryModel |` Format-Table -AutoSize #show the filegroups $MyDB.FileGroups |` Select-Object Parent, Name, IsDefault, IsFileStream, ReadOnly |` Format-Table -AutoSize #show the files in the filegroups $MyDB.FileGroups.Files | ` Select-Object Parent, Name, Size, GrowthType, Growth, FileName |` Format-Table -AutoSize #show the database log files $MyDB.LogFiles |` Select-Object Parent, Name, Size, GrowthTYpe, Growth, Filename |` Format-Table -AutoSize |
Alter database using SMO class objects
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
#Assign variables $Instance = "localhost\SQL2012" $DBName = "PoSh" # get the server $Server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "$Instance" # assign the database name to a variable $MyDB = $Server.Databases[$DBName] $MyDB.SetOwner("sa") $MyDB.AutoCreateStatisticsEnabled = $true $MyDB.AutoUpdateStatisticsAsync = $true $MyDB.AutoUpdateStatisticsEnabled = $true $MyDB.IsReadCommittedSnapshotOn = $true $MyDB.Alter() |
Alter database using SQLSERVER PSDrive
The above can also be performed using the PSDrive SQLSERVER:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
#Assign variables $Instance = "localhost\SQL2012" $DBName = "PoSh" # get the database $MyDB = Get-Item "SQLSERVER:\SQL\$Instance\Databases\$DBName" $MyDB.SetOwner("sa") $MyDB.AutoCreateStatisticsEnabled = $true $MyDB.AutoUpdateStatisticsAsync = $true $MyDB.AutoUpdateStatisticsEnabled = $true $MyDB.IsReadCommittedSnapshotOn = $true $MyDB.Alter() |
Drop database
If you wanted to drop the database:
|
1 2 3 4 5 6 7 8 |
#Assign variables $Instance = "localhost\SQL2012" $DBName = "PoSh" # get the database $MyDB = Get-Item "SQLSERVER:\SQL\$Instance\Databases\$DBName" $MyDB.Drop() |
