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


Upload Image Close it
Select File

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

Madhivanan's TSQL Blog

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,


Madhivanan
3 · 40% · 12947
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
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
    2085 · 0% · 7
  • mstjn,

    Thanks. You can use them when needed

    commented on May 19 2010 6:42AM
    Madhivanan
    3 · 40% · 12947
  • 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
    3078 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"Backup All databases" rated 5 out of 5 by 1 readers
Backup All databases , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]