Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5August 1, 2007 Part I and Part II of this series discussed PowerShell installation and simple SMO, WMI cmdlets. Part III discussed how to script PowerShell and connect to SQL Server. Part IV discussed how to use a PowerShell script to loop through the content of a file and connect to different servers. This installment of the article series illustrates how to use PowerShell and PowerShell script to create databases, etc. Method 1 Let us assume we want to create a database, ‘MyDatabase’, on the server ‘HOME’ with all defaults. Execute the following command. [Refer Fig 1.0]
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'HOME'
$DataBase = new-object ('Microsoft.SqlServer.Management.Smo.Database')
($Server, "MyDataBase")
$DataBaseDataFile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile')
($FileGrowth, "MyDatabase_Data")
$DataBaseDataFile.FileName = "D:\MyDatabase_Data.mdf"
$DataBaseLogFile = new-object ('Microsoft.SqlServer.Management.Smo.LogFile')
($DataBase, "MyDatabase_Log")
$DataBaseLogFile.FileName = "D:\NewDB_Log.ldf"
$DataBase.Create()
Click for larger image The above command created the ‘MyDatabase’ database on the ‘HOME’ server, using the default values and path. [Refer Fig 1.1 and 1.2]
Method 2 Let us assume that we have to create a new database named ‘MyDatabase1’ on the ‘HOME’ server. Let us also assume that the data file should be 25MB, with the Data file and log file on D drive. Execute the following command in PowerShell as shown below. [Refer Fig 1.3]
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'HOME'
$DataBase = new-object ('Microsoft.SqlServer.Management.Smo.Database')
($Server, "MyDataBase1")
$FileGrowth = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup')
($DataBase, "PRIMARY")
$DataBase.FileGroups.Add($FileGrowth)
$DataBaseDataFile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile')
($FileGrowth, "MyDatabas1e_Data")
$FileGrowth.Files.Add($DataBaseDataFile)
$DataBaseDataFile.FileName = "D:\MyDatabase1_Data.mdf"
$DataBaseDataFile.Size = [double](25.0 * 1024.0)
$DataBaseDataFile.GrowthType = "Percent"
$DataBaseDataFile.Growth = 25.0
$DataBaseDataFile.MaxSize = [double](100.0 * 1024.0)
$DataBaseLogFile = new-object ('Microsoft.SqlServer.Management.Smo.LogFile') ($DataBase, "MyDatabase1_Log")
$DataBaseLogFile.FileName = "D:\MyDatabase1_Log.ldf"
$DataBase.Create()
The above cmdlets creates the ‘Mydatabase1’ database on the ‘HOME’ server, with a 25MB data file and both the Log file and Data file are created on D Drive. [Refer Fig. 1.3, 1.4]
Method 3 Let us create a script that allows us to create any database on any server with any size and path we like. Create the following file as shown below and save it as createdb.ps1. [Refer Fig 1.5]
param (
[string] $ServerName,
[string] $DatabaseName,
[Double] $DataSize ,
[string] $DataPath,
[string] $LogPath
)
echo "Creating Database....."
echo "----------------------"
echo "Input..."
echo "Server Name : $ServerName"
echo "Database Name : $DatabaseName"
echo "Data Size : $DataSize"
echo "Data File Path : $DataPath"
echo "Log File Path : $LogPath"
$LogicalDataFile=$DatabaseName + "_Data"
$LogicalLogFIle=$DatabaseName + "_Log"
$datapath1=$DataPath + "\" + $DatabaseName + "_Data.mdf"
$Logpath1=$LogPath + "\" + $DatabaseName + "_Log.ldf"
$DataSize1=[double]($DataSize * 1024.0)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
$DataBase = new-object ('Microsoft.SqlServer.Management.Smo.Database') ($Server, $DatabaseName)
$FileGrowth = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($DataBase, "PRIMARY")
$DataBase.FileGroups.Add($FileGrowth)
$DataBaseDataFile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($FileGrowth, $LogicalDataFile)
$FileGrowth.Files.Add($DataBaseDataFile)
$DataBaseDataFile.FileName = $datapath1
$DataBaseDataFile.Size = [double]( $DataSize1 )
$DataBaseDataFile.GrowthType = "Percent"
$DataBaseDataFile.Growth = 25.0
$DataBaseDataFile.MaxSize = [double](100.0 * 1024.0)
$DataBaseLogFile = new-object ('Microsoft.SqlServer.Management.Smo.LogFile') ($DataBase, $LogicalLogFIle)
$DataBaseLogFile.FileName = $Logpath1
$DataBase.Create()
echo "Output...."
echo "Logical name of Data is $LogicalDataFile"
echo "Logical name of Log is $LogicalLogFile"
echo "Data File Path is $datapath1"
echo "Log file path is $LogPath1"
echo "Size of the file is $DataSize1"
Now execute the PowerShell script file as shown below. [Refer Fig 1.6] ./createdb.ps1 HOME MyDatabase2 30 D: D: Note: HOME is the server name MyDatabase2 is the database name 30 is MB data size D: is the location of the data Path D: is the location of the Log Path Output PS C:\ps> ./createdb.ps1 HOME MyDatabase2 30 D: D: Creating Database..... ---------------------- Input... Server Name : HOME Database Name : MyDatabase2 Data Size : 30 Data File Path : D: Log File Path : D: GAC Version Location --- ------- -------- True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.SMO\9.0.242.0__89845dcd8080cc91\Microsoft.Sql. Output.... Logical name of Data is MyDatabase2_Data Logical name of Log is MyDatabase2_Log Data File Path is D:\MyDatabase2_Data.mdf Log file path is D:\MyDatabase2_Log.ldf Size of the file is 30720
The following code generates the output, which actually clutters the desired output. Code [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
Output GAC Version Location --- ------- -------- True v2.0.50727 C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.SMO\9.0.242.0__89845dcd8080cc91\Microsoft.Sql... This can be avoided by redirecting the output to NULL.
param (
[string] $ServerName,
[string] $DatabaseName,
[Double] $DataSize ,
[string] $DataPath,
[string] $LogPath
)
echo "Creating Database....."
echo "----------------------"
echo "Input..."
echo "Server Name : $ServerName"
echo "Database Name : $DatabaseName"
echo "Data Size : $DataSize"
echo "Data File Path : $DataPath"
echo "Log File Path : $LogPath"
$LogicalDataFile=$DatabaseName + "_Data"
$LogicalLogFIle=$DatabaseName + "_Log"
$datapath1=$DataPath + "\" + $DatabaseName + "_Data.mdf"
$Logpath1=$LogPath + "\" + $DatabaseName + "_Log.ldf"
$DataSize1=[double]($DataSize * 1024.0)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
$DataBase = new-object ('Microsoft.SqlServer.Management.Smo.Database') ($Server, $DatabaseName)
$FileGrowth = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($DataBase, "PRIMARY")
$DataBase.FileGroups.Add($FileGrowth)
$DataBaseDataFile = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($FileGrowth, $LogicalDataFile)
$FileGrowth.Files.Add($DataBaseDataFile)
$DataBaseDataFile.FileName = $datapath1
$DataBaseDataFile.Size = [double]( $DataSize1 )
$DataBaseDataFile.GrowthType = "Percent"
$DataBaseDataFile.Growth = 25.0
$DataBaseDataFile.MaxSize = [double](100.0 * 1024.0)
$DataBaseLogFile = new-object ('Microsoft.SqlServer.Management.Smo.LogFile') ($DataBase, $LogicalLogFIle)
$DataBaseLogFile.FileName = $Logpath1
$DataBase.Create()
echo "Output...."
echo "Logical name of Data is $LogicalDataFile"
echo "Logical name of Log is $LogicalLogFile"
echo "Data File Path is $datapath1"
echo "Log file path is $LogPath1"
echo "Size of the file is $DataSize1"
Now execute the PowerShell script file as shown below. [Refer Fig 1.7] ./createdb.ps1 HOME MyDatabase2 30 D: D:
Output PS C:\ps> ./createdb.ps1 HOME MyDatabase2 30 D: D: Creating Database..... ---------------------- Input... Server Name : HOME Database Name : MyDatabase2 Data Size : 30 Data File Path : D: Log File Path : D: Output.... Logical name of Data is MyDatabase2_Data Logical name of Log is MyDatabase2_Log Data File Path is D:\MyDatabase2_Data.mdf Log file path is D:\MyDatabase2_Log.ldf Size of the file is 30720 ConclusionPart 5 of this series has illustrated how to use PowerShell and PowerShell script to create databases etc. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
