Maintenance Plan tips

Fredrik Danielsson wrote:
> Hi!
> I'm trying to set up a new Maintenance Plan for a new MS SQL Server 2005.
> And would like
> some input on my backup plan.
> Background
> The server is a database development server where databases are added and
> deleted on a weekly basis. All the newly added databases has to be backupe
d
> without any need for backup configuration changes.
> In case of a crash/mistake I would like the most recent backup to be no
> older
> than 24h, as well as you have to be able to go one week back in time to
> restore a database.
> How does the below backup plan look?
> Full backups on saturdays
> Differential backups sunday - friday.
> All backups kept for 2 weeks
> Is this a good plan or is there a better way.
> pros/cons?
> /Fredrik
>
>
Hi Frederik
Is there any reason why you will run differential backups? Unless the
database are really big, I'd simply just do a full backup every night.
That makes administration easier and when you have to restore a backup,
it's only a single file you need to restore.
Regards
Steen Schlter Persson
Databaseadministrator / SystemadministratorHi!
I'm trying to set up a new Maintenance Plan for a new MS SQL Server 2005.
And would like
some input on my backup plan.
Background
The server is a database development server where databases are added and
deleted on a weekly basis. All the newly added databases has to be backuped
without any need for backup configuration changes.
In case of a crash/mistake I would like the most recent backup to be no
older
than 24h, as well as you have to be able to go one week back in time to
restore a database.
How does the below backup plan look?
Full backups on saturdays
Differential backups sunday - friday.
All backups kept for 2 weeks
Is this a good plan or is there a better way.
pros/cons?
/Fredrik|||Fredrik Danielsson wrote:
> Hi!
> I'm trying to set up a new Maintenance Plan for a new MS SQL Server 2005.
> And would like
> some input on my backup plan.
> Background
> The server is a database development server where databases are added and
> deleted on a weekly basis. All the newly added databases has to be backupe
d
> without any need for backup configuration changes.
> In case of a crash/mistake I would like the most recent backup to be no
> older
> than 24h, as well as you have to be able to go one week back in time to
> restore a database.
> How does the below backup plan look?
> Full backups on saturdays
> Differential backups sunday - friday.
> All backups kept for 2 weeks
> Is this a good plan or is there a better way.
> pros/cons?
> /Fredrik
>
>
Hi Frederik
Is there any reason why you will run differential backups? Unless the
database are really big, I'd simply just do a full backup every night.
That makes administration easier and when you have to restore a backup,
it's only a single file you need to restore.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Fredrik Danielsson wrote:
> Hi!
> I'm trying to set up a new Maintenance Plan for a new MS SQL Server 2005.
> And would like
> some input on my backup plan.
> Background
> The server is a database development server where databases are added and
> deleted on a weekly basis. All the newly added databases has to be backupe
d
> without any need for backup configuration changes.
> In case of a crash/mistake I would like the most recent backup to be no
> older
> than 24h, as well as you have to be able to go one week back in time to
> restore a database.
> How does the below backup plan look?
> Full backups on saturdays
> Differential backups sunday - friday.
> All backups kept for 2 weeks
> Is this a good plan or is there a better way.
> pros/cons?
> /Fredrik
>
Have a look at the script I've posted here: http://www.realsqlguy.com/?p=8
I'm not a big fan of maintenance plans, I think they hide certain
critical operations from the "administrator", when that person really
should understand what's going on.
The script I've linked to is used on my production servers (slightly
edited for public consumption). The script is scheduled to run every
five minutes, and each time it runs, it does the following, for each
database on the server:
- if a database is in Simple recovery mode, and it has been 24 hours
since the last full backup, a full backup is done
- if it is has been more than 7 days since a full backup was done of a
database, a full backup is done
- if it has been more than 3 days since a full backup, and it's
currently after 9:00pm on Friday, a full backup is run. This keeps your
full backups clustered around the weekends, typically non-production
time in many environments.
- if a database is in Simple recovery mode, and the log is 75% full, the
log is truncated
- if a database is not in Simple mode, and the log is 75% full OR it has
been more than 5 minutes since the last t-log backup, a transaction log
backup is done
Sounds like this would be perfect for your environment. The script will
automatically detect new databases as they are added. You shouldn't
have to touch a thing.|||Fredrik Danielsson wrote:
Hi!
I'm trying to set up a new Maintenance Plan for a new MS SQL Server 2005.
And would like
some input on my backup plan.
Background
The server is a database development server where databases are added and
deleted on a weekly basis. All the newly added databases has to be backuped
without any need for backup configuration changes.
In case of a crash/mistake I would like the most recent backup to be no
older
than 24h, as well as you have to be able to go one week back in time to
restore a database.
How does the below backup plan look?
Full backups on saturdays
Differential backups sunday - friday.
All backups kept for 2 weeks
Is this a good plan or is there a better way.
pros/cons?
/Fredrik
Hi Frederik
Is there any reason why you will run differential backups? Unless the
database are really big, I'd simply just do a full backup every night. That
makes administration easier and when you have to restore a backup, it's only
a single file you need to restore.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator
Hi Steen,
The reason why I'm thinking about doing Differential backups is because
there are a number of large databases that we receive from our customers.
These databases are only used to migrate data from, they are never altered.
It would be enterly possible to do full backups every night on the
development databases. But the databases that we receive can be quite large.
(30-40GB)
The machine only has 270 GB of space for databases and backups.
If there is a simple way of excluding some databases from the backups there
would be no problem. But it's more important that new databases are included
in the backups
/Fredrik|||Fredrik D wrote:
> Fredrik Danielsson wrote:
FYI, SQL backup compress REALLY well. You might consider creating a
compressed OS volume on which to store your backup files.|||Microsoft Compression is not supported on and SQL Server files (data,
log, backup, or otherwise). See this link:
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx[/u
rl]
You are better off using SQLBackup or LiteSpeed if you need to compress
your backups.
Tracy McKibben wrote:
> Fredrik D wrote:
> FYI, SQL backup compress REALLY well. You might consider creating a
> compressed OS volume on which to store your backup files.|||PSPDBA wrote:
> Microsoft Compression is not supported on and SQL Server files (data,
> log, backup, or otherwise). See this link:
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx[
/url]
>
Interesting, because I've been compressing my backup folders for YEARS.
You are absolutely correct that the data and log files cannot be
compressed, but the BACKUPS can be. From the article you linked to,
under the Compression heading:
"All SQL Server database log and data files should remain in an
uncompressed state."
Not a word about backups...

> You are better off using SQLBackup or LiteSpeed if you need to compress
> your backups.
Why? If you want to do real-time compression without using the OS
compression, pipe the backup through GZip. Does EXACTLY the same thing
that either of those products do, with a much lower price tag.|||Fredrik Danielsson wrote:
> Hi!
> I'm trying to set up a new Maintenance Plan for a new MS SQL Server 2005.
> And would like
> some input on my backup plan.
> Background
> The server is a database development server where databases are added and
> deleted on a weekly basis. All the newly added databases has to be backupe
d
> without any need for backup configuration changes.
> In case of a crash/mistake I would like the most recent backup to be no
> older
> than 24h, as well as you have to be able to go one week back in time to
> restore a database.
> How does the below backup plan look?
> Full backups on saturdays
> Differential backups sunday - friday.
> All backups kept for 2 weeks
> Is this a good plan or is there a better way.
> pros/cons?
> /Fredrik
>
Have a look at the script I've posted here: http://www.realsqlguy.com/?p=8
I'm not a big fan of maintenance plans, I think they hide certain
critical operations from the "administrator", when that person really
should understand what's going on.
The script I've linked to is used on my production servers (slightly
edited for public consumption). The script is scheduled to run every
five minutes, and each time it runs, it does the following, for each
database on the server:
- if a database is in Simple recovery mode, and it has been 24 hours
since the last full backup, a full backup is done
- if it is has been more than 7 days since a full backup was done of a
database, a full backup is done
- if it has been more than 3 days since a full backup, and it's
currently after 9:00pm on Friday, a full backup is run. This keeps your
full backups clustered around the weekends, typically non-production
time in many environments.
- if a database is in Simple recovery mode, and the log is 75% full, the
log is truncated
- if a database is not in Simple mode, and the log is 75% full OR it has
been more than 5 minutes since the last t-log backup, a transaction log
backup is done
Sounds like this would be perfect for your environment. The script will
automatically detect new databases as they are added. You shouldn't
have to touch a thing.|||Fredrik D wrote:
> Fredrik Danielsson wrote:
FYI, SQL backup compress REALLY well. You might consider creating a
compressed OS volume on which to store your backup files.