Day: July 16, 2011

Script Indexes for a Publication

I like to script the Indexes manually to apply to a subscriber when creating a publication.  Call me a sadist, but I am what I am.  I whipped up a quick script to connect to a published database, grab the articles in the publication and script out the indexes on said articles (obviously, not the clustered index).  It’s very very slow, but I’m too tired to try to optimize this now.  Use at your own risk.  Minimally tested and not a lot of error handling is involved.

param(
	[string]$InstanceName = {throw "You must enter an Instance Name."},
	[string]$PubName = {throw "You must enter the name of the publication for which to script the indexes."},
	[string]$DistDBName = {throw "You must enter the name of the published database."},
	[string]$FileLocation = {throw "You must enter the location of where you want the scripts to be put."}
)

function ScriptIndexes([string]$TableName)
{	
	$Table = Get-ChildItem | where {$_.Name -eq $TableName}
	foreach($ix in $Table.Indexes)
	{
		if($ix.IsClustered -eq $false)
		{
			$ix.Script() | out-file "$FileLocation\$($Table.Name).sql" -append; "GO`n$($separator)`n" | out-file "$FileLocation\$($Table.Name).sql" -append -Force
		}
	}
}

function Main()
{
	# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
	$smo = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
	if ((($smo.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') 
	{
	  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
	  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
	}

	# Handle any errors that occur
	Trap 
	{
		# Handle the error
		$err = $_.Exception
		write-host $err.Message
		while( $err.InnerException ) 
		{
			$err = $err.InnerException
			write-output $err.Message
		};
		# End the script.
		break
	}

	$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName

	$DistQuery = @"
SELECT art.name AS TableName FROM dbo.sysarticles art (NOLOCK) 
INNER JOIN dbo.syspublications pub 
ON art.pubid = pub.pubid 
WHERE  pub.name = '$PubName'
"@

	$PubRes = invoke-sqlcmd -ServerInstance $InstanceName -Database $DistDBName -Query $DistQuery
	sl SQLSERVER:SQL\$InstanceName\DATABASES\$DistDBName\TABLES
	foreach($row in $PubRes)
	{
		ScriptIndexes $row.TableName
	}	
}

cls
Main

Full Backup of User Databases with Litespeed

Quick script to show how to backup all user databases using litespeed.  This particular one was for sql server 2k (ya, I know, will upgrade this to 2008R2 on Monday).  If using it for 2005+ best change the sysdatabases reference to sys.databases.  

ALTER PROCEDURE dbo.lsp_FullBackupUserDBs(
	@BackupPath		VARCHAR(255) = 'F:\MSSQL\BACKUP\Full\User\'
)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @mkdir VARCHAR(4000)
	DECLARE @timestamp VARCHAR(50)
	DECLARE @filenamein VARCHAR(512)
	DECLARE @IDENT INT
	DECLARE @DBNAME VARCHAR(200)
	
	SELECT @IDENT = MIN([DBID]) 
	FROM sysdatabases 
	WHERE [DBID] > 0
	AND NAME NOT IN ('master', 'model', 'msdb', 'tempdb')
	GROUP BY DBID
	
	WHILE @IDENT IS NOT NULL
	BEGIN
		SELECT @DBNAME = NAME FROM sysdatabases WHERE [DBID] = @IDENT

		SET @mkdir = 'mkdir ' + @BackupPath + @DBNAME + '"'

		SELECT @timestamp = DATENAME(MONTH, GETDATE()) + '_' + DATENAME(DAY, GETDATE()) + '_'  + DATENAME(YEAR, GETDATE()) + '__' + STUFF('00', 3-LEN(HOUR), LEN(HOUR), HOUR ) + '_' + STUFF('00', 3-LEN(MINUTE), LEN(MINUTE), MINUTE )
		FROM (SELECT DATENAME(HOUR, GETDATE()) AS HOUR, DATENAME(MINUTE, GETDATE()) AS MINUTE) AS timedata

		SET @filenamein = @BackupPath + @DBNAME + '\' + @DBNAME + '_db_%DateTimePostfix.bak'
		SET @filenamein = REPLACE(@filenamein, '%DateTimePostfix', @timestamp)

		EXECUTE xp_procedure @mkdir, no_output

		EXECUTE master.dbo.xp_backup_database 
			@database=@DBNAME, 
			@filename=@filenamein, 
			@threads=3, 
			@init=1, 
			@priority=0, 
			@logging=0, 
			@affinity=0, 
			@throttle=90, 
			@buffercount=20, 
			@maxtransfersize=1048576, 
			@compressionlevel=6

	SELECT @IDENT=MIN([DBID]) 
	FROM sysdatabases 
	WHERE [DBID] > 0 
	AND [DBID]>@IDENT
	AND NAME NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution')

	END

END
GO