TSQL — Backup all your databases or selected databases

Howdy Everyone,

Today one of my friend asked me to take a look into his backup script that he is using, to take backup of  all databases in his testing environment.The script is hard coded with the database names (50 of them) and the poor fellow needs to maintain the script as well  add  / delete databases on the request of his team.

I advised my friend to change the script and use the dynamic SQL for this purpose and re-wrote the script for him.

I thought it might be helpful to some one like my dear friend. So I pasted the same in here as my new blog post.

How it Works?

There are few choices I have given to the users for this script

  1. The user can restrict the databases that are needed to be taken a backup
  2. The user should give the backup location in which the backup files needs to be placed
  3. The user can choose the backup file trailer incase if he wishes to add something there & if it is left blank current date in the format YYYYMMDD will be added to the end of the databasename.
What if my friend X above want to take a backup of only certain databases like 10 out of his 50 right now… I thought of that a wrote a lookup table for this (databases_To_be_backedUp).  Insert the database names (10 in my case) that needs to be backed up into the lookup table, so that it will take care of the backup part for you. (Follow Example 4)
All the backup files will be created with the extension of “BAK”
IF EXISTS ( SELECT 1 FROM SYSOBJECTS WHERE name = 'databases_To_be_backedUp' and xtype = 'U')
	DROP TABLE databases_To_be_backedUp
CREATE TABLE databases_To_be_backedUp
	ID				INT		Identity(1,1)
	,database_name	varchar(200)

IF EXISTS ( SELECT 1 FROM SYSOBJECTS where name = 'backup_all_databases' and xtype = 'P')
	DROP PROCEDURE backup_all_databases
CREATE procedure backup_all_databases
	@backupName_trailer	varchar(20) = '' --- If the parameter is passed the backup names will have the trailer with this
										  -- else by default the proc will add the YYYYMMDD trailer.
	,@backupLocation	varchar(100)
	declare @databaseName	Varchar(200)
			,@lookUpCount	int
			,@sql			varchar(1000)

	SELECT @lookUpCount = count(1)
	FROM databases_To_be_backedUp

	IF (@lookUpCount > 0) -- Some databases are inserted into the databases_To_be_backedUp, that means only these databases are needed to be backed up.
		DECLARE database_cursor CURSOR
		FOR SELECT database_name FROM databases_To_be_backedUp ORDER BY ID
						  -- The databases_To_be_backedUp is empty so all the databases are needed to backed up.. apart from the system databases
						  -- In this script we are considering the MASTER, MODEL,TEMPDB, MSDB as system databases..
						  -- Incase if you want to change this change the not in parameter for the below select statement.
		DECLARE database_cursor CURSOR
		FOR select name from sys.databases where name not in('master','model','msdb', 'tempdb')
	OPEN database_cursor
	FETCH database_cursor into @databaseName
		Select @sql = 'BACKUP DATABASE ' + @databaseName + ' TO DISK = ' + '''' + @backupLocation + @databaseName
										+ CASE @backupName_trailer
												WHEN '' THEN convert(varchar(10),getdate(),112)
												ELSE @backupName_trailer
										+ '.bak'			-- extension  for the backup file
										+ ''''
										+ ' WITH INIT,SKIP'
		print @sql
		FETCH database_cursor into @databaseName
	CLOSE database_cursor
	DEALLOCATE database_cursor

-- examples
-- 1 backup all your databases to d:\
exec backup_all_databases @backupLocation = 'D:\'

-- 2 backup with backup as a trailer in the file name
exec backup_all_databases @backupName_trailer = 'backup',@backupLocation = 'D:\'

-- 3 if you want to take backup of only choosen databases rather than every thing
create database a -- creates a temp database a
create database b -- creates a temp database b

insert into databases_To_be_backedUp(database_name) values ('a');
insert into databases_To_be_backedUp(database_name) values ('b');

exec backup_all_databases @backupLocation = 'D:\'

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s