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

LVL 1
martineitAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tpi007Commented:
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.  
0
martineitAuthor Commented:
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.
0
RiteshShahCommented:
I have given full strategy at

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24336568.html

for full backup, transaction log backup, differential backup and automatic delete old backup. you can refer post # 24184054 in above link.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Anthony PerkinsCommented:
>>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.
0
martineitAuthor Commented:
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.
0
RiteshShahCommented:
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.
0
Anthony PerkinsCommented:
>>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>
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.