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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: