Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 1 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 1
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 2 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 2
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 3 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 3
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 4 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 4

The MAK

Part I and Part II of this series showed you how to install PowerShell and simple SMO and WMI cmdlets. In Part III, I showed you how to script PowerShell and connect to SQL Server. In Part IV, I will show you how to use the PowerShell script to loop through the contents of the file and connect to other servers.

In this article, I will continue the discussion by showing you how to use PowerShell and PowerShell scripts in database creation and other issues.

Method 1

We acknowledge that we will create a database 'MyDatabase', on the 'HOME' server with default features.

Execute the following command:

[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 ()

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 5
Figure 1.0

The above command created a database named 'MyDatabase' on the 'HOME' server, using the default values ​​and paths.

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 6
Figure 1.1

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 7
Figure 1.2

Method 2

Suppose that we have to compose a new database named 'MyDatabase1' on the 'HOME' server. Let's assume the data file will be 25MB with the Data file and the log file located on drive D.

Execute the following command in PowerShell:

[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 Cmdlet creates a database 'Mydatabase1' on the 'HOME' server with a 25MB data file, both the Data file and the Log log file are created on drive D (Figure 1.3 and 1.4).

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 8
Figure 1.3

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 9
Figure 1.4

Method 3

Create a script that allows us to create any database on any server as well as any size and path that we like.

Create the file below as shown below and save it with the file createdb.ps1 . (Figure 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 " 

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 10
Figure 1.5

Now execute the PowerShell script file as shown in the figure below (Figure 1.6).

./createdb.ps1 HOME MyDatabase2 30 D: D:

Note :

1. HOME is the server name
2. MyDatabase2 is the database name
3. 30 is the data size calculated in MB
4. D: is the location of the data path
5. 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: WINDOWSassemblyGAC_MSILMicrosoft.SqlServer.SMO9.0.242.0__89845dcd8080cc91Microsoft.Sql. Output . Logical name of Data is MyDatabase2_Data Logical name of Log is MyDatabase2_Log Data File is D: MyDatabase2_Data.mdf Log file path is D: MyDatabase2_Log.ldf Size of the file is 30720 

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 11
Figure 1.6

The code below creates the desired output.

Command code

[System.Reflection.Assembly] :: LoadWithPartialName ("Microsoft.SqlServer.SMO")

Output

GAC Version Location
--- ------- --------
True v2.0.50727 C: WINDOWSassemblyGAC_MSILMicrosoft.SqlServer.SMO9.0.242.0__89845dcd8080cc91Microsoft.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 " 

Execute the PowerShell script file as shown below (Figure 1.7)

./createdb.ps1 HOME MyDatabase2 30 D: D:

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 12
Figure 1.7

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:
Kết quả .
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 tập tin là 30720

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 13 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 14 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 15 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 16 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 17 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 5 Picture 18 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11