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?
/FredrikThis is a multi-part message in MIME format.
--090500030204080306040605
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
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 Schlüter Persson
Databaseadministrator / Systemadministrator
--090500030204080306040605
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Fredrik Danielsson wrote:
<blockquote cite="mideyPuwE3mGHA.4960@.TK2MSFTNGP04.phx.gbl" type="cite">
<pre wrap="">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
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi Frederik<br>
<br>
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.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
</font></font>
</body>
</html>
--090500030204080306040605--|||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
>
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 Schlüter 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:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
You are better off using SQLBackup or LiteSpeed if you need to compress
your backups.
Tracy McKibben wrote:
> 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.|||PSPDBA wrote:
> Microsoft Compression is not supported on and SQL Server files (data,
> log, backup, or otherwise). See this link:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>
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 D wrote:
Hi Frederik
By using a Maintenance Plan, there're no way to "automate" wgich
database are being backed up. Instead you can script you backup jobs and
then in some way here select which databases you want to backup. That
will require that you in some way "mark" which databases you need to
backup and which you don't. That can be e.g. by naming. You could maybe
also look for a create date or something like that, but I don't know how
safe that will be.
On one of my servers, I'm using a script that back up all database that
starts with a certain string. This server is a Cognos BI database server
and all the applications/databases that are created by the users are
named with e.g. 'pl_xxxxxx'. The script then just backup all databases
where the name starts with "pl_xxxx". By doing this, I don't have to
worry about any new databases that are being created without my knowledge.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Tracy, please read:
http://support.microsoft.com/default.aspx?scid=kb;en-us;231347
Tracy McKibben wrote:
> PSPDBA wrote:
> > Microsoft Compression is not supported on and SQL Server files (data,
> > log, backup, or otherwise). See this link:
> > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> >
> 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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment