Microsoft Windows Powershell and SQL Server 2005 SMO - Part 5

This article explores Microsoft Windows powershell and SQL server 2005 smo - part 5 with straightforward explanations and useful context. It also highlights common questions and important details to consider.

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

How Microsoft Windows Powershell and SQL Server 2005 SMO - Part 5 Works

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

Figure 1.0

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

Figure 1.1

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).

Figure 1.3

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 "

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 :

• HOME Is the server name
• MyDatabase2 Is the database name
• 30 Is the data size calculated in MB
• 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: 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

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:

Figure 1.7

Output

PS C: ps>./createdb. ps1 HOME MyDatabase2 30 D: D:Creating Database.----------------------Input.Server Name: HOMEDatabase Name: MyDatabase2Data Size: 30Data File Path: D:Log File Path: D:K?t qu?.Logical name of Data is MyDatabase2_DataLogical name of Log is MyDatabase2_LogData File Path is D: MyDatabase2_Data. mdfLog file path is D: MyDatabase2_Log. ldfSize of t?p tin là 30720

Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 7 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 8 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 10 Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 11