We do log backups every 20min and full each night at 9pm then copy to
remote server + netbackup.
Here is my script which does full backups each night of all db on the
server.
It looks for databases, creates backup device if it is a new db, then backs
up to the new device. Else it will just backup to the device.
This saves me heaps of messing around on Dev & Test servers where databases
are being removed and added daily.
PS* Im currently looking for a job in HK or China, if there are any going
or if anyone can point me in the right direction that would be great.
/*backup job*/
select name from master..sysdevices
Declare @DBName as Varchar(50)
Declare @ErrorSave as Varchar(50)
Declare @DBName2 as Varchar(50)
Declare @DBName3 as Varchar(50)
Declare @Failed as NVarchar(200)
Declare @Success as NVarchar(200)
DECLARE BackupCSR CURSOR FOR (SELECT name from master.dbo.sysdatabases
WHERE name NOT IN ('tempdb'))
OPEN BackupCSR
IF (@@ERROR <> 0)
SET @ErrorSave = @@ERROR
FETCH NEXT FROM BackupCSR
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DBName2 = @DBName + '_script'
SET @Failed = 'xp_logevent 60000, ''BUNG! Backup failed for:::
'+@DBName+''', informational'
SET @Success = 'xp_logevent 60000, ''BING! Backup completed for:::
'+@DBName+''', informational'
/*change this for each server's drives...*/
SET @DBName3 = 'E:\SCRIPT_BACKUP_'+@DBName+'.dat' -- ENTER PATH FOR
BACKUP!!!
IF (SELECT count(*) from master..sysdevices WHERE name = @DBName2) <
1
BEGIN
EXEC sp_addumpdevice 'disk', @DBName2,@DbName3
/*error check*/
IF (@@ERROR <> 0)
BEGIN
SET @ErrorSave = @@ERROR
Exec sp_executesql @Failed
GOTO FailedLoop
END
END
BACKUP DATABASE @DBName TO @DBName2 WITH INIT
/*error check*/
IF (@@ERROR <> 0)
BEGIN
SET @ErrorSave = @@ERROR
Exec sp_executesql @Failed
GOTO FailedLoop
END
Exec sp_executesql @Success
FailedLoop:
FETCH NEXT FROM BackupCSR INTO @DBname
END
CLOSE backupCSR
DEALLOCATE BackupCSR
Declare @ServerEmailName as Nvarchar(50)
Set @ServerEmailName = @@servername + '@aon.com.au'
IF @ErrorSave <> 0
BEGIN
EXEC master.dbo.xp_smtp_sendmail
@FROM =@ServerEmailName,
@TO =N'anthony.[Email Address Removed] @Subject =@errorSave,
@Message =N'Backup script failed',
@type =N'text/html',
@Server =N'ausmtpsyd01.ars.au.ap.aon'
END
__________________________________________
Anthony Hand
DBA | Systems Integration
Aon Risk Services Australia Ltd
Ph: +61 2 9253 7516
Fax: +61 2 92537952
"Jeff Newton"
<[Email Address Removed] To: "LazyDBA.com Discussion" <[Email Address Removed]
rgroup.com> cc:
Subject: RE: Backup/restore procedures.
29/04/2004 10:25
AM
Hi All,
Interesting the different methods of backing up.
I notice that none of the options shown bother to copy the database or
transaction logs
to another server after successful backup . . . .
We backup our databases each night @ 23:00, transaction logs every 30
minutes,
after each backup we copy the backed up file to a remote server.
Cheers
Jeff
-----Original Message-----
From: tessier.[Email Address Removed] [mailto:tessier.[Email Address Removed] Wednesday, 28 April 2004 9:46 PM
To: LazyDBA.com Discussion
Subject: Backup/restore procedures.
Hi all.
I'm considering writing backup and restore procedures. Any of you DBAs
have procedures that you would be ready to share or know about resources I
could look at to start with. Any help will be greatly appreciated.
Thanks
Pierre Tessier
IT Applications Specialist | Spécialiste des applications de TI
Information Management and Technology Directorate | Directorat de la
gestion de l'information et de la technologie
Corporate Services Branch | Direction des services ministériels
Department of Finance and Treasury Board of Canada Secretariat | Ministère
des Finances et Secrétariat du Conseil du Trésor du Canada
Ottawa, Canada K1A 0G5
613-996-8958 | Tessier.[Email Address Removed] | facsimile/télécopieur
613-995-5235
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Get today's cartoon: http://www.LazyDBA.com
To unsubscribe, e-mail: mssqldba-[Email Address Removed] ,
then respond to the confirmation email you get
For additional commands, e-mail: mssqldba-[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Get today's cartoon: http://www.LazyDBA.com
To unsubscribe, e-mail: mssqldba-[Email Address Removed] ,
then respond to the confirmation email you get
For additional commands, e-mail: mssqldba-[Email Address Removed] information contained in this email and any attached files ("Email")
is strictly private and confidential. This Email should be read by the
intended recipient only. If you are not the intended recipient of this
Email or if you believe this is an unsolicited email, please immediately
notify the sender by replying to this email or contact a representative
from the Aon Group of Companies ("Aon") on +61 2 9253 7000 and promptly
destroy this Email and any copies of this Email.
Only the intended recipient of this Email may use, reproduce, disclose or
distribute the information contained in this Email with Aon's permission.
If you are not the intended recipient, you are strictly prohibited from
using, reproducing, disclosing or distributing the information contained in
this Email.
Aon does not represent, warrant or guarantee that the integrity of this
Email has been maintained or that the communication is free of errors,
virus or interference. Aon advises that this Email should be scanned to
detect viruses. Aon accepts no liability for loss or damage (whether
caused by negligence or not) resulting from the use of this Email.
Unless the sender is expressly providing you with personal advice, any
advice contained in this Email has been prepared without taking into
account your objectives, financial situation or needs. Before acting on any
advice in this Email, you should consider whether that advice is
appropriate for your personal circumstances. If this Email contains
reference to any specific financial product(s), Aon recommends you consider
the relevant product disclosure statement or other disclosure document(s)
before making any decision regarding that product."
MS Sql Server LazyDBA home page