SQL Server Alias management with PowerShell and WMI – Update for SQL Server 2012

In 02/2011 I created a PowerShell script to set / get / remove SQL Server aliases:

https://blog.kenaro.com/2011/02/09/enumerate-add-update-and-remove-sql-server-aliases-by-using-powershell/

Today I updated it for SQL Server 2012 and tested it on Windows Server 2012 and Windows Server 2008 R2.

You can download it here:

http://gallery.technet.microsoft.com/SQL-Server-2008-2012-Alias-baf05737

Truncate (Shrink) LOG Files of Databases in SQL Server 2008 R2

Today I got this error while trying to shrink my SharePoint 2010 databases on my dev machine:

‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

I searched for the error and found different sites, e.g.:

But there was no complete script for “truncating” all of my databases  at once.

Here is my script:

[sourcecode language=”sql”]–TRUNCATE_LOG for all databases at once on SQL SERVER 2008 R2
–https://blog.kenaro.com/2011/08/28/truncate-shrink-log-files-of-databases-in-sql-server-2008-r2/

DECLARE c CURSOR FOR SELECT database_id, name, recovery_model_desc FROM sys.databases — WHERE name=’sharepoint_config’; 
DECLARE @dbname VARCHAR(1024); 
DECLARE @rmod VARCHAR(1024); 
DECLARE @id INT; 
DECLARE @lfile VARCHAR(1024);  

OPEN c;  

FETCH NEXT FROM c INTO @id, @dbname, @rmod; 

WHILE @@FETCH_STATUS = 0 
BEGIN
IF @rmod = ‘FULL’
BEGIN
SET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1)
PRINT @lfile
EXEC(‘ALTER DATABASE [‘ + @dbname + ‘] SET RECOVERY SIMPLE’)
EXEC(‘USE [‘+@dbname+’]; DBCC SHRINKFILE([‘+@lfile+’], 1)’)
EXEC(‘ALTER DATABASE [‘ + @dbname + ‘] SET RECOVERY FULL ‘)
END ELSE
IF @rmod = ‘SIMPLE’
BEGIN
SET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1)  
PRINT @lfile
EXEC(‘USE [‘+@dbname+’]; DBCC SHRINKFILE([‘+@lfile+’], 1)’)
END
FETCH NEXT FROM c INTO @id, @dbname,@rmod; 
END;  

CLOSE c 
DEALLOCATE c  
[/sourcecode]

For me it works like expected.

Enumerate, add, update and remove SQL Server aliases by using PowerShell

I’ve created a PowerShell script for adding, updating, enumerating und removing SQL Server aliases. – You can use the script to create/remove/enumerate both 32bit und 64bit aliases in one step or only 32bit or only 64bit aliases.

You can change this script into a PowerShell module. Just see the comment below inside the script.

I’ve tested the script on Windows Server 2008 R2 with SQL Server 2008 R2 on it. – Please post your comments how it works on other systems.

Here is the script:

#region Author: Ingo Karstein / Blog: blog.kenaro.com