Backup Strategy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Backup Strategy

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-09-19 : 13:09:52

I would like any comments or suggestions on the following:

I have three stored procedures that backup a database. The first does a full backup, the second a differential and the third a transaction log backup. I will probably combine these into one single sp at some point.
The sp's are scheduled to run as jobs and they are scheduled as follows.

Transaction Log Backup occurs once every 15 minutes.
Differential Backup occurs once every 1 hours.
Full Backup occurs once every day.

This should provides me with a recovery to within 15 minutes.

Here are the sp's
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CREATE PROCEDURE [dbo].[FullBackup]
AS

DECLARE @DiskLocation AS NVARCHAR(255)

SET @DiskLocation = N'D:\SQLServer\Backups\Full' + CONVERT(NVARCHAR,GETDATE(),112) + CAST(DATEPART(hh,GETDATE()) AS NVARCHAR)+ RIGHT('0' + CAST(DATEPART(mi,GETDATE()) AS NVARCHAR),2)

BACKUP DATABASE [MyDB] TO DISK = @DiskLocation WITH INIT , NAME = N'FullBackup', SKIP , STATS = 10, NOFORMAT
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CREATE PROCEDURE [dbo].[DiffBackup]
AS
DECLARE @DiskLocation AS NVARCHAR(255)

SET @DiskLocation = N'D:\SQLServer\Backups\Diff' + CONVERT(NVARCHAR,GETDATE(),112) + CAST(DATEPART(hh,GETDATE()) AS NVARCHAR)+ RIGHT('0' + CAST(DATEPART(mi,GETDATE()) AS NVARCHAR),2)

BACKUP DATABASE [MyDB] TO DISK = @DiskLocation WITH INIT, DIFFERENTIAL, NAME = N'DiffBackup', SKIP , STATS = 10, NOFORMAT
GO

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

CREATE PROCEDURE [dbo].[LogBackup]
AS

DECLARE @DiskLocation AS NVARCHAR(255)

SET @DiskLocation = N'D:\SQLServer\Backups\Log' + CONVERT(NVARCHAR,GETDATE(),112) + CAST(DATEPART(hh,GETDATE()) AS NVARCHAR)+ RIGHT('0' + CAST(DATEPART(mi,GETDATE()) AS NVARCHAR),2)

BACKUP LOG [MyDB] TO DISK = @DiskLocation WITH INIT, NAME = N'Log Backup', SKIP , STATS = 10, NOFORMAT
GO

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Anyone see any problems.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-19 : 13:16:44

Good to see your not using the maint exe :).

I usually include the database name in the file name so

DBName_full_yyyymmdd_hhmm.bak

and also include something at the beginning to delete backups that are too old - but may you rely on the tape transfer to do that.

I also put this in an admin database and pass in the database name to back up.

As you say you may as well combine them into one SP with a backup type parameter.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 09/19/2002 13:17:32

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-19 : 13:16:44

Sounds good. The only thing I can think of is to look at your recovery model if you're using SQL 2000. If you are comfortable with a 15 minute margin of recovery then you can safely switch to Simple or Bulk Insert recovery (if you're on Full now) and potentially improve the performance of any bulk-load or large transaction operations. Also some maintenance procedures like CHECKDB or DBREINDEX could run faster.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-09-19 : 13:36:01

Rob,

Are you saying that by doing transaction log backups I can get better recovery interval than the frequency of those backups???
Because I'd like to be able to recover to within the smallest period possible but I don't want to backup my transaction log any more than every 15 minutes.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-19 : 14:08:15

What I meant was, if you're willing to sacrfice up to 15 minutes worth of data, then you can get off of Full recovery mode using that schedule, and improve performance.

Full recovery will let you get up-to-the second changes, at the cost of logging EVERYTHING, including DBCC maintenance and index rebuilds, bulk inserts, etc., and will fill up the transaction log faster and with more stuff.

If you can't or don't want to lose a single row, then stick with Full recovery. If you're not having performance or log problems then stick with Full recovery.

In any case, you've got a good backup schedule. It might be overkill with Full recovery set up, but it won't interfere with it either.

Hope this clears it up.

gwhiz
Yak Posting Veteran

78 Posts

Posted - 2002-09-19 : 14:16:28

Rob

I was under the impression that you can't pull transaction log backups if you go to a simple model.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-19 : 14:33:21

I don't think it prevents you backing up the log, but it does render a log backup kinda useless. Log backups are still useful under Bulk Insert recovery.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources