Madhivanan's TSQL Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

sql_server	217
t-sql	211
tsql	116
sqlserver	96
BRH	78
#SQLServer	66
#TSQL	56
SQL Server	34
function	11
SSMS	9

Archive · View All

August 2007	17
August 2010	8
June 2012	7
June 2011	7
November 2007	7
August 2012	6
May 2012	6
November 2011	6
August 2011	6
October 2011	6

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (387) · Preferences

Backup All databases

May 10 2010 12:00AM by Madhivanan

Here is the script that takes backup of all databases excluding system databases

The file name would be databasename_yyyymmdd where yyyymmdd is the formatted value of current date

create procedure Backup_all_databases
as
Declare @sql varchar(8000)
set @sql=''
select @sql=@sql+
	'
	Backup database ['+name+'] to disk=''E:\backup\'+name+'_'+convert(varchar(8),getdate(),112)+'.bak''' 
	from master..sysdatabases
where
	name not in ('master','tempdb','model','msdb','pubs','Northwind')
--print @sql
EXEC(@sql)

You may need to change E:\backup\ to specific folder you wish to take backup

Tags: t-sql, sql_server, backup, BRH,

Previous: Extracting numbers - Part 2
Next: Derived Table - New approach to avoid SQL Injection

Madhivanan

3 · 39% · 12440

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

3 Comments

If you're running >= sql2005 you might want to use sys.databases instead... Also there are some dbs that will cause your script to fail so you'd want to eliminate them. Offline dbs will cause an error. Maybe you don't want to back up a read-only db either. Try this where clause:

where status & ( -- FOR EACH DB PROPERTY SHOWN BELOW, UNREM THE LINE ONLY IF YOU DO NOT WANT TO GET A DB WITH THAT PROPERTY -- + 1 -- autoclose (ALTER DATABASE) -- + 4 -- select into/bulkcopy (ALTER DATABASE using SET RECOVERY) -- + 8 -- trunc. log on chkpt (ALTER DATABASE using SET RECOVERY) -- + 16 -- torn page detection (ALTER DATABASE) + 32 -- loading + 64 -- pre recovery + 128 -- recovering + 256 -- not recovered + 512 -- offline (ALTER DATABASE) + 1024 -- read only (ALTER DATABASE) -- + 2048 -- dbo use only (ALTER DATABASE using SET RESTRICTED_USER) -- + 4096 -- single user (ALTER DATABASE) -- + 32768 -- emergency mode -- + 4194304 -- autoshrink (ALTER DATABASE) -- + 1073741824 -- cleanly shutdown ) = 0 and name not in ('master','tempdb','model','msdb','pubs','Northwind')

commented on May 18 2010 2:58PM

mstjn

1979 · 0% · 7

mstjn,

Thanks. You can use them when needed

commented on May 19 2010 6:42AM

Madhivanan

3 · 39% · 12440

I like this short code. Yet for more dbs and with autodelete options there might be more complicated approach. I prefer the simple tool that does scheduled backup jobs with auto delete old backups. It also features in sending compressed backup files over FTP and into Dropbox or to Amazon WS. sqlbackupandftp.com

commented on Feb 3 2013 10:21AM

JeffFreedman

2895 · 0% · 2

Your Comment

Sign Up or Login to post a comment.

"Backup All databases" rated 5 out of 5 by 1 readers +Madhivanan