Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup SQL server databases Expand / Collapse
Author
Message
Posted Friday, January 31, 2014 11:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 19, Visits: 182
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.
Post #1536918
Posted Friday, January 31, 2014 12:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 9:28 AM
Points: 130, Visits: 244
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

Post #1536930
Posted Friday, January 31, 2014 12:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 19, Visits: 182
Thank you... I will review it.
Post #1536941
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse