SQL Server backups

Author:	Brian Dill
Created:	2009-05-08
Updated:	2009-06-02
Show Until:	2009-12-31

---

This is neither rocket science, nor novel, but it should prove useful to those who aren't fortunate enough to have awesome backup utilities like Red-Gate SQL Backup or Quest LiteSpeed.
Load this procedure in your master database, and then whenever you need to backup a particular database, you can just run

EXEC master..adm_BackupDatabase 'MyDatabase'.

... and you'll get a backup like

---

IF EXISTS ( SELECT * FROM sys.procedures WHERE name = 'adm_BackupDatabase' )
	DROP PROCEDURE adm_BackupDatabase
GO
-- ================================================================================
-- Desc: Backs up specified database to <path>\<dbname>\<dbname>_<date>.bak
-- Note: <path>\<dbname>\ folder must exist first.
-- Auth: Brian Dill
-- Date: 2009-05-08
-- ================================================================================
CREATE PROCEDURE adm_BackupDatabase

	  @DatabaseName NVARCHAR(1024)
	, @BackupPath NVARCHAR(1024) = 'D:\backups\'    -- End with backslash.  Path must exist.

AS

DECLARE @ISODate NVARCHAR(50)
DECLARE @ToDisk NVARCHAR(1024)
DECLARE @Now DATETIME
SELECT @Now = GETDATE()

SELECT @ISODate = CONVERT(VARCHAR, DATEPART(yyyy, @Now)) 
	+ '-' + REPLICATE('0', 2 - DATALENGTH(CONVERT(VARCHAR, DATEPART(mm, @Now)) ) ) + CONVERT(VARCHAR, DATEPART(mm, @Now))
	+ '-' + REPLICATE('0', 2 - DATALENGTH(CONVERT(VARCHAR, DATEPART(dd, @Now)) ) ) + CONVERT(VARCHAR, DATEPART(dd, @Now))
	+ '_' + REPLICATE('0', 2 - DATALENGTH(CONVERT(VARCHAR, DATEPART(hh, @Now)) ) ) + CONVERT(VARCHAR, DATEPART(hh, @Now))
	+ '.' + REPLICATE('0', 2 - DATALENGTH(CONVERT(VARCHAR, DATEPART(mi, @Now)) ) ) + CONVERT(VARCHAR, DATEPART(mi, @Now))
	+ '.' + REPLICATE('0', 2 - DATALENGTH(CONVERT(VARCHAR, DATEPART(ss, @Now)) ) ) + CONVERT(VARCHAR, DATEPART(ss, @Now))

IF RIGHT(@BackupPath , 1) <> '\'
	SET @BackupPath = @BackupPath + '\'

SELECT @ToDisk = @BackupPath + @DatabaseName + '\' + @DatabaseName + '_' + @ISODate + '.bak'

BACKUP DATABASE @DatabaseName TO DISK=@ToDisk WITH INIT

GRANT EXEC ON adm_BackupDatabase TO [PUBLIC]