January 31, 2014 at 11:34 am
I use SQL 2008 R2 and I would like to find a script where I can backup multiple user databases, with copy_only option and also compress the databases. So looking for all these in one single script. Does anyone have any idea about this script and please send me if you have any.
January 31, 2014 at 12:29 pm
You will naturally need to supply some of your own values, but here is what I use: You can get rid of the final if exists, if you have no mail profile set up, but it is awfully nice to know when something fails.
declare @dbname sysname
declare @backupdir nvarchar(200)
declare @fileextension nvarchar(20)
declare @backupcommand nvarchar(1000)
declare @init nvarchar(10)
create table ##backuplogerrors (dbname sysname, errornumber int, errormessage varchar(200))
set @backupdir = N'E:\MSSQL10\MSSQL10.MSSQLSERVER\MSSQL\Backup\'
set @fileextension = 'Bkup.bak'
declare dbs cursor for select name from sys.databases where name not in ('master', 'msdb', 'tempdb')
open dbs
fetch next from dbs into @dbname
while @@fetch_status = 0
begin
set @backupcommand = 'backup database [' + @dbname + '] to disk = ''' + @backupdir + @dbname + @fileextension + ''' with init'
--select @backupcommand
begin try
exec (@backupcommand)
end try
begin catch
insert into ##backuplogerrors (dbname, errornumber, errormessage)
values (@dbname, error_number(), error_message())
end catch
fetch next from dbs into @dbname
end
close dbs
deallocate dbs
if exists (select * from ##backuplogerrors)
begin
exec msdb..sp_send_dbmail @recipients = 'your email address', @query = 'select * from ##backuplogerrors', @profile_name = 'SQL Server Alerts'
end
drop table ##backuplogerrors
January 31, 2014 at 12:59 pm
Thank you... I will review it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply