Category Archives: SQL

Set multiple SQL databases Recovery MODE to Simple

It is always handy to have an automated script to perform task for you. Manual click vis SSMS could be tedious and prone to error.

I came out with a simple SQL which allow me to set recovery mode of all database in one instance to Simple or Full.

GO
DECLARE @name varchar(255),@sql varchar (500)
DECLARE databases CURSOR FOR
SELECT name FROM sys.databases where name not in (‘master’,’tempdb’,’model’,’msdb’)

OPEN databases

— Perform the first fetch.
FETCH NEXT FROM databases
INTO @name
— Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
— This is executed as long as the previous fetch succeeds.
SET @sql =
‘USE [‘+@name+’];
ALTER DATABASE [‘+ @name + ‘] SET RECOVERY SIMPLE;’
+ ‘DBCC SHRINKFILE([‘+@name+’_log], 1)’
print @sql
exec(@sql)
FETCH NEXT FROM databases
INTO @name
END

CLOSE databases
DEALLOCATE databases
GO

 

Note that if you are doing Log Shipping, you need to use FULL recovery mode or Bulk-logged

Using Powershell to make MS SQL query

Making SQL query is pretty simple, hope the following give you a quickest way to get sql data without using SQL Management Tool. (Note: Windows 7 does come with Powershell 2.0, Hurray!)


$sql = New-Object System.Data.SqlClient.SqlConnection
$sql.ConnectionString = "Server=localhostsqlexpress;Integrated Security=true;"
$sql.Open()
$cmd = $sql.CreateCommand()
#Here is where you insert your own sql query
$cmd.CommandText = "exec sp_databases"
$cmd.ExecuteScalar()