asked on

SQL Backups: what strategy?

Dear Experts,
I would like to receive feedbacks on the backup strategy I decided to apply for our SQL Server.
OS: Win 2003 SP2
SQL Server 2000 SP4

I searched the net for a while before posting this question and it seems to me that there is no clear statement on how to backup a database with "full recovery" in order to minimize data loss in case of disk failure.
Here are my questions (and following the strategy I decided to apply based on my assumptions):
- Is it not possible to backup a database on a backup device (file) keeping the latest 3 daily backups in the same file and deleting the older ones?
I tried playing around with the INIT and SKIP options (my best guess was to set the RETAINDAYS to 3 and "NO INIT" and "NO SKIP") but It didn't work.

Assuming that the previous statement is true I decided to go for this strategy (I'm talking about the Full recovery DBs):
- Backup Database on disk naming the file with the current date
- VB script to delete backups older than 3 days
- Backup transaction Logs (every hour) to a different location (remote server) naming the file with the current date
- VB script to delete t-logs backups older than 3 days

I would like to know what do you think about this strategy and if you think it could be made better somehow,

Thanks,
Roberto.

declare @name VARCHAR(80)
declare @file VARCHAR(80)
 
set @name = 'ACMDB'
set @file = 'D:\MSSQL\MSSQL\BACKUP\' + @name + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'
BACKUP DATABASE @name TO  
DISK = @file 
WITH  RETAINDAYS = 2, NOFORMAT, NOINIT,  
NAME = N'ACMDB-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
=====================================================
declare @file VARCHAR(80)
 
set @file = '\\eolbck\tlogs$\ACMDB_tlog' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'
 
BACKUP LOG [ACMDB] 
TO DISK = @file 
WITH  RETAINDAYS = 2, 
NOFORMAT, NOINIT,  
NAME = N'ACMDB-Transaction Log  Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Open in new window

tpi007

I would recommend you use Maintence Plans within SQL Agent section of Enterprise Manager in SQL Server. All the above can be achieved via Miantenece Plans, no need for VB Script. I personally use 10min interval on Transaction Log Backups to keep transaction log small.

martineit

ASKER

Thanks for your answer but as I previously stated this is a SQL Server 2000 therefore no maintenance plans.
For the t-log backup interval I could change it to 10 min but it wouldn't make my strategy any different (that's easy to change).

Any other idea?

Thanks,
Roberto.

ASKER CERTIFIED SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

>>Thanks for your answer but as I previously stated this is a SQL Server 2000 therefore no maintenance plans.<<
Can you clarify this? I would not be seen dead using a Maintenance plan in 2000 (when we had 2000), but they do exist and useful if you are just starting out.

martineit

ASKER

Hello Acperkins,
I must rephrase what I said before, maintenance plans are available in SQL Server 2000 but as far as I know, the "Maintenance Cleanup Task" (which I'm guessing is the one tpi007 was referring to) is only available for 2005, please correct me if I'm wrong.
For RiteshShah: you scripts are very interesting and I'm willing to accept yours as definitive answer, but for my peace of mind I would like someone to tell me loud and clear if:
"the only way to backup a database keeping an history of x number of days in the same file is programmatically (t-sql or vbscript doesn't really matter)".
and
"RETAINDAYS is basically a useless keyword".

Thanks a lot for your help,
Roberto.

RiteshShah

well, it is not a good practice to have more than one backup in one .BAK file rather keep only one full backup in one .BAK, move older file to somewhere else or delete it, I have already specified code to delete old file.

Anthony Perkins

>>someone to tell me loud and clear if:
"the only way to backup a database keeping an history of x number of days in the same file is programmatically (t-sql or vbscript doesn't really matter)".
<<
Unfortunately, we cannot do that without knowing your situation in detail. Anybody who does is rash to say the least. For example, here we run nightly Full backups for all 200+ databases with hourly Transaction log backups. We retain 7 days on site and 2 months off-site. If you are a bigger shop with more transactional info you may need more frequent Transaction Log Backups. If you cannot wait until an old backup file is retrieved offsite, then you may need to keep more days. It varies.

>>"RETAINDAYS is basically a useless keyword".<<
I suspect, it is your expectations of what that keyword accomplishes that may be incorrect. Here is the definition from BOL:

<quote>
RETAINDAYS = { days | @days_var }
Specifies the number of days that must elapse before this backup media set can be overwritten. If supplied as a variable (@days_var), it must be specified as an integer.

Important:
If EXPIREDATE or RETAINDAYS is not specified, expiration is determined by the media retention configuration setting of sp_configure. These options only prevent SQL Server from overwriting a file. Tapes can be erased using other methods, and disk files can be deleted through the operating system. For more information about expiration verification, see SKIP and FORMAT in this topic.
</quote>