Backup strategy

I have to build a backup method for my factory.
I have sql server 2000 and I have to save a DB that is used from 8.00
to 20.00.
Now I have a complete backup that run every night and a differential at
12.30 every day.
The complete have the name of the day, so I have seven different files
for every day of the week. Also the differential one.
I have not log backups.
So, if I'd like to make more backup in the working time (when users use
DB) I have to increase differential backups (with time of backup in the
name of file) or I have to add log backups?
If I make log backups, I have to create one day file (with no
overwrite) or I have to create in a day many files with day of week and
time of backup in the name of file?
In this scenario, I have to use NO_TRUNCATE or TRUNCATE for log
backups?
Thanks to all.If you don't want log backups then set the recovery model to simple - that
will truncate the log on every backup.
Include the date rather than the day in the backup files.
mydb_full_yyyymmdd_hhmmss.bak
mydb_diff_yyyymmdd_hhmmss.bak
If you want to include log backups then do the same
mydb_log_yyyymmdd_hhmmss.bak
You can now add diff backups whenever you wish.
From what you say you might want to consider log backups though.
Have a look at this sp
It will take full, diff and log backups for all databases on the server and
delete old files - you can control what it does from a table in the admin
database.
http://www.nigelrivett.net/SQLAdmin/BackupAllDatabases.html
There is also an sp there to do the restores including the log sequence and
latest full/diff.
"giminera@.libero.it" wrote:
> I have to build a backup method for my factory.
> I have sql server 2000 and I have to save a DB that is used from 8.00
> to 20.00.
> Now I have a complete backup that run every night and a differential at
> 12.30 every day.
> The complete have the name of the day, so I have seven different files
> for every day of the week. Also the differential one.
> I have not log backups.
> So, if I'd like to make more backup in the working time (when users use
> DB) I have to increase differential backups (with time of backup in the
> name of file) or I have to add log backups?
> If I make log backups, I have to create one day file (with no
> overwrite) or I have to create in a day many files with day of week and
> time of backup in the name of file?
> In this scenario, I have to use NO_TRUNCATE or TRUNCATE for log
> backups?
> Thanks to all.
>|||giminera@.libero.it wrote:
> I have to build a backup method for my factory.
> I have sql server 2000 and I have to save a DB that is used from 8.00
> to 20.00.
> Now I have a complete backup that run every night and a differential at
> 12.30 every day.
> The complete have the name of the day, so I have seven different files
> for every day of the week. Also the differential one.
> I have not log backups.
> So, if I'd like to make more backup in the working time (when users use
> DB) I have to increase differential backups (with time of backup in the
> name of file) or I have to add log backups?
> If I make log backups, I have to create one day file (with no
> overwrite) or I have to create in a day many files with day of week and
> time of backup in the name of file?
> In this scenario, I have to use NO_TRUNCATE or TRUNCATE for log
> backups?
> Thanks to all.
>
I have a script that you can schedule to run at frequent intervals (I
run it every 5 minutes) that will automatically handle full backups and
t-log backups for you, for all databases on the server.
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/AutomaticBackupOfAllDatabases
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||A transaction log backup is actually recommended for your scenario.
Just a follow up question on this one. I have a full database backup and
log shipping configured. I want to include a differential backup so as to
make restoration a lot easy for the server since I am looking at around 50
databases on single server. Will the differential backup work with my
existing plan?
"Nigel Rivett" wrote:
> If you don't want log backups then set the recovery model to simple - that
> will truncate the log on every backup.
> Include the date rather than the day in the backup files.
> mydb_full_yyyymmdd_hhmmss.bak
> mydb_diff_yyyymmdd_hhmmss.bak
> If you want to include log backups then do the same
> mydb_log_yyyymmdd_hhmmss.bak
> You can now add diff backups whenever you wish.
> From what you say you might want to consider log backups though.
> Have a look at this sp
> It will take full, diff and log backups for all databases on the server and
> delete old files - you can control what it does from a table in the admin
> database.
> http://www.nigelrivett.net/SQLAdmin/BackupAllDatabases.html
> There is also an sp there to do the restores including the log sequence and
> latest full/diff.
>
>
>
> "giminera@.libero.it" wrote:
> > I have to build a backup method for my factory.
> > I have sql server 2000 and I have to save a DB that is used from 8.00
> > to 20.00.
> > Now I have a complete backup that run every night and a differential at
> > 12.30 every day.
> > The complete have the name of the day, so I have seven different files
> > for every day of the week. Also the differential one.
> > I have not log backups.
> > So, if I'd like to make more backup in the working time (when users use
> > DB) I have to increase differential backups (with time of backup in the
> > name of file) or I have to add log backups?
> > If I make log backups, I have to create one day file (with no
> > overwrite) or I have to create in a day many files with day of week and
> > time of backup in the name of file?
> > In this scenario, I have to use NO_TRUNCATE or TRUNCATE for log
> > backups?
> > Thanks to all.
> >
> >