Launch SSMS
Right click on databases node,
Select Menu item “Start PowerShell”
Type in following command to backup all databases to Default backup folder
dir | Backup-SqlDatabase
Databases are backed up to Default backup folder
January 21, 2013
January 18, 2013
Set autoclose property for all database
in SSMS , Right click on database node in Object Explorer, Select menu item “Start PowerShell”
Type in following in power shell window to set Auto Close on all databases
dir | foreach {$_.AutoClose = $true; $_.Alter()}
If you would prefer to set on specific database that starts with name “DB”
dir | where-object {$_.Name.StartsWith("DB")} | foreach {$_.AutoClose = $true; $_.Alter()}
Related forum thread: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/c2780156-7a6f-4b29-a2f6-f4bfa682379c/#0c0c375b-e584-4c3f-9d40-482ca1a15999
January 3, 2013
Scripting database using Smo.Scripter
You can automate script generation using SMO and power shell using the following power shell script. Script generated would be similar to the script generated using “Generate Script Wizard” in ssms
You can download script from here
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
February 14, 2012
Index rebuild on large database – SQL Agent Powershell job
Index rebuild on a large database could take longer time. One of our customer was looking for a solution to rebuild index one by one when index fragmentation is greater than 30%. Related newsgroup thread: http://social.msdn.microsoft.com/Forums/en-US/sqlkjmanageability/thread/4faae7c8-3e58-4117-8538-f397e342743f
In this blog post, I have written a SQL Powershell script that identifies indexes on tables that have fragmentation greater than 30% and performs a Index Rebuild one by one. You can also control the number of indexes to process in single script run. By this approach, you can run this SQL powershell script as a recurring scheduled SQL Agent job in non-peak hours.
PowerShell code:
You can download powershell script from rebuildIndexes.ps1
SQL agent job from Indexrebuild_agentjob.sql
January 11, 2012
Powershell script to run query against many servers and send combined output as email
This powershell script is sample code to run query against multiple SQL Servers and export the combined output to Excel. This Excel file is later sent as email attachment using hotmail.
Related email thread: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/987d48c0-a82d-4424-8699-cdd0b2bc9c51
# This posting is provided “AS IS” with no warranties, and confers no rights.
Script can be also downloaded from emailResultsMultiServerQuery.ps1
October 20, 2010
SQL 2008 Power Shell script – Databases that were not backed up in last 7 days
Here is a simple powershell command to list databases that were not backed up in last 7 days.
