Backup Strategy

[jeffinperth]

This is our proposed backup strategy for a SQL Server 2000 Enterprise Edition / Windows 2000 Advanced Server Installation.

We have backup devices setup for transaction log and database's for each day of the week.

This is how I have setup the backups, taking Monday as an example.

Job1
This job is run every 30 minutes on a Monday.
-----------------------------------------------------------------
backup log xxxxxx to xxxxxlogmonday with noinit
copy c:\backup\xxxxxxlogmonday.bak \\backup\xxxxxx-----------------------------------------------------------------

Job2
This job is run at 23:45 on a Monday night
-----------------------------------------------------------------
backup log xxxxxx to xxxxxxlogmonday with noinit
copy c:\backup\xxxxxxlogmonday.bak \\backup\xxxxxx
backup database xxxxxx TO DISK = N'c:\backup\xxxxxxdbmonday.bak' WITH INIT , NOUNLOAD , NAME = N'xxxxxxdbmonday', SKIP , STATS = 10, NOFORMAT
copy c:\backup\xxxxxxdbmonday.bak \\backup\xxxxxx
backup log xxxxxx to xxxxxxlogtuesday with init
-----------------------------------------------------------------


The last step of Job2 is to initialise the transaction log for the following day.
The //backup server is another windows 2000 server on the network.

Any comments on the above would be appreciated.


Jeff

 

[oublie]

below is an example of the backups we run on a daily and hourly basis. as you can see there are a few more steps involved. As you will see the hourly transaction log backup is the last step in the nightly job.


Print '***Starting Nightly Routine***' select 'Time Now: ' + convert(varchar(20), getdate())
go
Print 'Starting EOB Database BACKUP for Training' select 'Time Now: ' + convert(varchar(20), getdate()) go BACKUP DATABASE TRAINING to TRAININGDBDumpEOB with init, skip
go
Print 'Starting checkdb for Training' select 'Time Now: ' + convert(varchar(20), getdate()) go DBCC CHECKDB (Training)with no_infomsgs
go
Print 'Starting UPDATEUSAGE for Training' select 'Time Now: ' + convert(varchar(20), getdate()) go DBCC UPDATEUSAGE (Training)
go
Print 'Starting update_all_stats for Training' select 'Time Now: ' + convert(varchar(20), getdate()) go use Training exec sp_updatestats
go
Print 'Starting SOB Database BACKUP for Training' select 'Time Now: ' + convert(varchar(20), getdate()) go BACKUP DATABASE TRAINING to TRAININGDBDumpSOB with init, skip
go
Print 'Starting SOB Transaction BACKUP for Training' select 'Time Now: ' + convert(varchar(20), getdate()) go BACKUP TRANSACTION Training to TrainingLogDump with init, skip
go
Print '***Finished Nightly Routine***' select 'Time Now: ' + convert(varchar(20), getdate())
go