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
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
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 5, 2013
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 a valid file path on your machine where SQL Server service account has read, write privileges to create and write to XEvent file.
(Related forum thread: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/feede34e-401c-44a6-8dcd-6a096ec84c38 )
T-SQL
———
CREATE EVENT SESSION [set_options] ON SERVER ADD EVENT sqlserver.existing_connection(SET collect_database_name=(1),collect_options_text=(1)) ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\set_options.xel') WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF) GO ALTER EVENT SESSION [set_options] ON SERVER STATE=START
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 6, 2012
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”
2) If Windows Live ID sign-on was prompted sign in using your live id
3) Select Language and platform, Select “LocalDB”
http://www.microsoft.com/betaexperience/pd/SQLEXPCTAV2/enus/default.aspx
4) Wait till download manager completes downloading SqlLocalDB.MSI
Installation:
Launch Installer SqlLocalDB.MSI
Unattended Installation:
1: msiexec /i SqlLocalDB.msi /qn IACCEPTSQLLOCALDBLICENSETERMS=YES
Management tools:
You can install SQL Management Studio Express from
http://www.microsoft.com/betaexperience/pd/SQLEXPCTAV2/enus/default.aspx
Connecting to LocalDB
- Launch Management studio
- In Connection dialog, connect to (localdb)\v11.0
-
References:
http://blogs.msdn.com/b/sqlexpress/archive/2011/11/28/simple-installer-for-localdb-has-shipped.aspx
http://blogs.msdn.com/b/sqlexpress/archive/2011/10/27/net-framework-4-now-supports-localdb.aspx
December 19, 2011
SQL Server 2012 Unattended installation
Note: You can download SQL 2012 Developer edition if you are a MSDN subscriber
OR
Download SQL 2012 Evaluation:
1) To download evaluation edition Goto Download
for x86, Download following files to local folder on your machine
ENU\x86\SQLFULL_x86_ENU_Core.box
ENU\x86\SQLFULL_x86_ENU_Install.exe
ENU\x86\SQLFULL_x86_ENU_Lang.box
2) Open command window, extract downladed files to local folder
CD c:\SQL2012\
SQLFULL_x86_ENU_Install.exe
You will see extracted files under C:\SQL2012\SQLFULL_x86_ENU
Installation:
1) Open Command window as Elevated Administrator
2) CD C:\SQL2012\SQLFULL_x86_ENU
Unattended setup command line
Setup /QS
/ACTION=install
/INSTANCEID="SQL2012EVAL"
/INSTANCENAME="MSSQLSERVER"
/IACCEPTSQLSERVERLICENSETERMS=1
/FEATURES=SQL,Tools
/SQLSYSADMINACCOUNTS="machine-name\Administrators"
/BROWSERSVCSTARTUPTYPE=AUTOMATIC
/SECURITYMODE=SQL
/SAPWD="Password#1234$"
/INDICATEPROGRESS
/TCPENABLED=1
/AGTSVCSTARTUPTYPE="Automatic"
Above command does the following
- Installs SQL Server & Management tools
- Grants SQL sysadmin privilege for localgroup “Administrators”
- Enables TCP protocol for SQL instance
- Autostarts SQL Browser service
- Autostarts SQL Server Agent
5) Wait for setup.exe to complete
July 12, 2011
SQL Server Denali CTP3 on Windows 2008 R2 Server Core
Windows Server Core is a minimal server installation option for computers running on the Windows Server 2008 R2 operating system. You can learn more about Server Core here – http://msdn.microsoft.com/en-us/library/ee391626(v=VS.85).aspx
SQL Server Denali supports installing on Windows server core. You can refer to following link to learn more about new features in SQL Server Denali. http://www.microsoft.com/sqlserver/en/us/future-editions/denali-mission-critical-confidence.aspx
This blog entry explains the following
- The steps you could follow to setup SQL Server Denali CTP3 on a Hyper-V based Windows 2008 R2 Server Core virtual machine for testing purpose.
- Using SYSPREP feature in SQL Server to prepare image and later clone many virtual machines.
- Enabling SQL Server instance to be remotely manageable from SQL Server Management Studio.
Requirements:
- SQL Server Denali Installation media
- Windows 2008 R2 Hyper V enabled machine with free hard disk space of at least 50 GB.
- Windows 2008 R2 Server Core installation media
Downloading Denali CTP3:
- Download Denali CTP3 from Download SQL Server “Denali” CTP3
- Save downloaded files to c:\CTP3 on host machine
- Open command prompt, CD c:\ctp3
- Run SQLFULL_x64_ENU_Install.exe – This extracts downloaded contents to c:\CTP3\SQLFULL_x64_ENU
- Create a ISO image of folder c:\CTP3\SQLFULL_x64_ENU using CD / DVD burning software. You can also use tools like Oscdimg - http://technet.microsoft.com/en-us/library/cc749036(WS.10).aspx
Preparing Server core Virtual machine:
1. Setup a Windows 2008 R2 Server Core Virtual machine ( reference: http://technet.microsoft.com/en-us/library/cc732470(WS.10).aspx )
2. Start Virtual machine, you will see a command prompt window after logging on
Installing SQL Server on Server core Virtual machine:
1. Deploy SQL Server components with prepare image option. The Prepare Image step installs the SQL Server product and features but does not configure the installation.
a) Mount SQL Server Denali CTP3 ISO image to Virtual machine, In command window, check if the mounted drive folder has access ISO image contents
b) Prepare Image
In command prompt , CD d:\
SETUP /Q /IAcceptSQLServerLicenseTerms
/ACTION=PrepareImage
/FEATURES=SQLENGINE
/INSTANCEID=”DENALICTP3″
/INDICATEPROGRESS
Sysprep & Export Virtual machine:
1. Seal Image ( Additional details on Sysprep – http://support.microsoft.com/kb/302577 )
2. Export this virtual machine so that multiple copies of this Virtual machine can be imported back. ( Reference: http://blogs.technet.com/b/chenley/archive/2011/03/23/exporting-cloning-virtual-machines-on-hyper-v-with-windows-server-2008-r2.aspx )
Complete SQL Server installation on Virtual machine:
1. Import Virtual machine, start virtual machine
2. Rename machine name (join domain if needed) – use sconfig.cmd and follow instructions
3. Complete SQL Server installation with
- In command prompt, CD d:\
Setup /QS /ACTION=CompleteImage /INSTANCEID=”DENALICTP3″
/INSTANCENAME=”MSSQLSERVER”
/IACCEPTSQLSERVERLICENSETERMS=1
/SQLSYSADMINACCOUNTS=”WIN-Q1A9DGJ2BNU\Administrators”
/BROWSERSVCSTARTUPTYPE=AUTOMATIC
/SECURITYMODE=SQL
/SAPWD=”ThisIsNotMyPassword#1234$”
/INDICATEPROGRESS
/TCPENABLED=1
Note: replace “WIN-Q1A9DGJ2BNU” with your machine name
Above command line installs default SQL Server instance with
- SQL Authentication is enabled.
- TCP protocol is enabled.
- Adds local Administrators group as sysadmin in SQL Server
4. Configure Firewall rules to allow remote clients to connect to SQL Server instance
· In command prompt , enter the following command
netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN
Reference: http://msdn.microsoft.com/en-us/library/cc646023.aspx
Remote management using SQL Management Studio:
1. On a remote machine install SQL Server Management tools
2. Connect to Server core instance like any other SQL Server instance, now you can perform administrative tasks from remote machine.
If you have any issues in these steps please email me at sethu.srinivasan@microsoft.com . We would like to hear your feedback.
This posting is provided “AS IS” with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm
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.
