This is a quick blog on how you can use PowerShell and SMO to change some of the SQL Server database properties you may find useful.
In my scenario I’m working on some legacy SQL Server 2005 databases existing properties have the following values:
1. “Recovery Model” = ‘FULL’.
2. “Compatibility Level” = ‘Version80’ (for SQL Server 2000).
3. “PageVerify” = ‘TornPageDetection’.
As a proof of concept I created a dummy database named ‘testdatabase’ with the above properties so I can test my *PowerShell script and alter the values for all three properties.
*Note: If you have SQL Server Management Studio (SSMS) 2005 (or Greater), then you already have SMO (SQL Server Management Objects) assemblies already installed in your machine.
First thing I needed to do is to list all databases that will meet the above criteria. This way we can changes the following values to:
1. “Recovery Model” = ‘Simple’.
2. “Compatibility Level” = ‘Version90’ (for SQL Server 2005).
3. “Page Verify” = ‘CHECKSUM’.
The following script will list all databases meeting these criterias:
[sourcecode language=”powershell”]
## – Loads the SQL Server SMO Assembly and connect the your SQL Server instance:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$MySQL = new-Object Microsoft.SqlServer.Management.Smo.Server ‘YourSQLServerInstanceName’;
## – List all database meeting criteria:
$MySQL.Databases `
| where{ `
(($_.PageVerify -ne ‘Checksum’) `
-or ($_.CompatibilityLevel -ne ‘Version90’) `
-or ($_.RecoveryModel -ne ‘Simple’))} `
| Select-Object name, @{label=’dbSize(MB)’; `
Expression={($_.Size).ToString("###,###,###");}} `
,CompatibilityLevel, Pageverify `
| ft -AutoSize;
[/sourcecode]
Of course, this list let me know that in some of the databases not all three properties need to change. (see sample list below)
Name dbSize(MB) RecoveryModel CompatibilityLevel PageVerify
—- ———- ————- —————— ———-
xxxxP 1,386,603 Simple Version80 TornPageDetection
xxw 700 Simple Version90 TornPageDetection
xxxxxxin 200 Simple Version80 None
xxxxxxxx_SZ 200 Simple Version80 None
testdatabase 20 Full Version80 TornPageDetection
I can proceed to test my PowerShell SMO script lines to make some database property value changes. As you seen in the originating script I’ve created a PowerShell objects named ‘$MySQL’ which contains all of my SQL Server instance objects. So, by navigating thru my Database objects I can go directly to my ‘testdatabase’ properties and make the changes with the following lines:
[sourcecode language=”powershell”]
## – Changing the properties values:
$MySQL.Databases[‘testdatabase’].RecoveryModel = ‘Simple’;
$MySQL.Databases[‘testdatabase’].CompatibilityLevel = ‘Version90’;
$MySQL.Databases[‘testdatabase’].PageVerify = ‘CHECKSUM’;
## – Update database properties and refresh the database:
$MySQL.Databases[‘testdatabase’].Alter();
$MySQL.Databases[‘testdatabase’].Refresh();
[/sourcecode]
Now, I have succesfully updated the database properties on a fly and listed the updated properties with the following script:
[sourcecode language=”powershell”]
## – list the updated properties:
$MySQL.Databases[‘testdatabase’].GetPropertySet() `
| where{ `
(($_.name -eq ‘PageVerify’) `
-or ($_.name -eq ‘CompatibilityLevel’) `
-or ($_.name -eq ‘RecoveryModel’))} `
| Select-Object name, value | Format-Table -AutoSize;
[/sourcecode]
Name Value
—- —–
CompatibilityLevel Version90
PageVerify Checksum
RecoveryModel Simple
In summary, after making my changes, I’ve used both SMO methods of ‘.Alter()‘ and ‘.Refresh()‘ to secure the updated values. Also, is good to validate these changes by going back to SSMS application.
On my next part 2 of this blog, I’ll be creating a PowerShell function to automate this process so it can be executed against multiple databases and SQL Servers.
Here’s full script to change all three properties on database ‘testdatabase’:
[sourcecode language=”powershell”]
## – Loads the SQL Server SMO Assembly:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
## – Connect SQLServer instance:
$MySQL = new-Object Microsoft.SqlServer.Management.Smo.Server ‘YourSQLServerInstanceName’;
## – Changing properties in Databases:
$MySQL.Databases[‘testdatabase’].GetPropertySet() | Select-Object name, value | Format-Table -AutoSize;
$MySQL.Databases[‘testdatabase’].RecoveryModel = ‘Simple’;
$MySQL.Databases[‘testdatabase’].CompatibilityLevel = ‘Version90’;
$MySQL.Databases[‘testdatabase’].PageVerify = ‘CHECKSUM’;
$MySQL.Databases[‘testdatabase’].Alter();
$MySQL.Databases[‘testdatabase’].Refresh();
## – List the updated properties:
$MySQL.Databases[‘testdatabase’].GetPropertySet() `
| where{ `
(($_.name -eq ‘PageVerify’) `
-or ($_.name -eq ‘CompatibilityLevel’) `
-or ($_.name -eq ‘RecoveryModel’))} `
| Select-Object name, value | Format-Table -AutoSize;
[/sourcecode]
If you like to learn more about PowerShell working with SQL Server SMO, check my SQL Server Pro article at:
http://sqlmag.com/powershell/using-sql-server-management-objects-powershell
Bonus – Another way to list a database property names and values:
[sourcecode language=”powershell”]
## – Loads the SQL Server SMO Assembly and connect the your SQL Server instance:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$MySQL = new-Object Microsoft.SqlServer.Management.Smo.Server ‘YourSQLServerInstanceName’;
## – List everything:
$MySQL.Databases[‘testdatabase’].GetPropertySet();
## – Or, List the database only property: name and value:
$MySQL.Databases[‘testdatabase’].GetPropertySet() `
| Select-Object name, value | Format-Table -AutoSize;
[/sourcecode]