Blog Archives

Backup all databases using Backup-SqlDatabase cmdlet


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   Advertisements

Posted in Backup, Scripting, SQL Power shell, SQL Server

DMV – Windows Operating System version information


You can use the DMV on SQL 2008 R2 and above to query Windows OS information SELECT * FROM sys.dm_os_windows_info References: MSDN: http://msdn.microsoft.com/en-us/library/hh204565.aspx Related Forum thread: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/9753f897-82e3-4602-b7c9-650c7239e83d

Posted in DMV, SQL Server

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

Posted in DB Management, SQL Power shell, SQL Server, SSMS

Capturing session level options settings on all sessions in SQL 2012 using XEvents


You could use XEvents to capture options set at all existing sessions & any future new sessions. Here is sample session definition. (I have tested it on SQL 2012 SP1). Please replace the file path in the following script to

Posted in SQL Server, XEvents

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

Tagged with:
Posted in Scripting, SQL Power shell, SQL Server

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

Posted in DB Management, Powershell, SQL Agent, SQL Power shell, SQL Server

SQL Server 2012 Express LocalDB


  Highlights: – Small download footprint – Very quick installation – Simple Installer – No installer options – (Just Accept EULA)   About LocalDB: http://blogs.msdn.com/b/sqlexpress/archive/2011/11/28/simple-installer-for-localdb-has-shipped.aspx   Download LocalDB 1) Goto : http://www.microsoft.com/sqlserver/en/us/editions/express.aspx , click on “Download 2012 RC0 EXPRESS”  

Tagged with:
Posted in LocalDB, SQL Server