Advice on how to handle backup files

Hi all,
Our client wants some guidance on how to handle backup files created by
SQL Server 2005 for our product.
I was thinking that we would create
1) a nightly backup to say e:\backup
2) create a transaction log backup every hour to e:\backup
Now is it best to create a new file each time - or is it best to
overwrite the original file?
Reason I ask is the client will need to them back these files up to an
external tape drive.
It will probably be easier for them if they have the same 20+ files
each time (ie. one full backup, and hourly transaction files)
If the filenames incremented, then they would need to delete older
files that have been backed up (which can invite user error)
What are peoples opinions on this? Is there a "good way" to do this'
Cheers
DavidHi
Well, in our shop we do a full backup every night to c:\ and log backup
every 15 minutes to d:\
A log file may contain many files so you'll need to specify i like
drop database test
go
create database test
GO
create table test..test(id int identity)
insert test..test default values
backup database test to disk = 'd:\db.bak' WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak'WITH INIT
insert test..test default values
backup log test to disk = 'd:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
...............
.............
<davconts@.gmail.com> wrote in message
news:1151992078.629506.142340@.p79g2000cwp.googlegroups.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>|||This is a multi-part message in MIME format.
--070701000301070106090101
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
davconts@.gmail.com wrote:
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>
Hi David
I don't think there's a single "good way" to do it. It pretty much
depends on personal preferences and I don't think one mtethod is better
than others.
Bottomline is that no matter how you decide to do it, it will require
some degree of knowledge on how it works in order to maintain it and
restore a backup in case of a failure.
I prefer to only have one backup in each file and then name the files
with a name that are composed by date and a forth running number( for
the log files). This is because I like to know that each file only
contains one backup so when I need to restore I have to find the full
backup file and then the corresponding logfile 1, 2, 3... up to the
point in time I need. If I had more backups in the same file, I'd first
have to find out which file number I had to restore for the fulle backup
and then I'd also have to find out from which filenumber the logfile
backup would start. Another thing that I don't like about having many
backups in one file, is when the backups starts to get too big. In that
case you suddenly get some quite big backup files that can be a pain to
"administer". The backup of one of our databases are currently around 85
GB and if you have a number of these in the same file, it will easily
get quite "un-handy".
I think the biggest advantage of having several files in one backup
file, is that it's easier to let SQL server delete old backups in the
file. If you have seperate files with naming by date, you'll have to do
some scripting to delete the files.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--070701000301070106090101
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">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:davconts@.gmail.com">davconts@.gmail.com</a> wrote:
<blockquote
cite="mid1151992078.629506.142340@.p79g2000cwp.googlegroups.com"
type="cite">
<pre wrap="">Hi all,
Our client wants some guidance on how to handle backup files created by
SQL Server 2005 for our product.
I was thinking that we would create
1) a nightly backup to say e:\backup
2) create a transaction log backup every hour to e:\backup
Now is it best to create a new file each time - or is it best to
overwrite the original file?
Reason I ask is the client will need to them back these files up to an
external tape drive.
It will probably be easier for them if they have the same 20+ files
each time (ie. one full backup, and hourly transaction files)
If the filenames incremented, then they would need to delete older
files that have been backed up (which can invite user error)
What are peoples opinions on this? Is there a "good way" to do this'
Cheers
David
</pre>
</blockquote>
<font size="-1"><font face="Arial">Hi David<br>
<br>
I don't think there's a single "good way" to do it. It pretty much
depends on personal preferences and I don't think one mtethod is better
than others.<br>
Bottomline is that no matter how you decide to do it, it will require
some degree of knowledge on how it works in order to maintain it and
restore a backup in case of a failure.<br>
I prefer to only have one backup in each file and then name the files
with a name that are composed by date and a forth running number( for
the log files). This is because I like to know that each file only
contains one backup so when I need to restore I have to find the full
backup file and then the corresponding logfile 1, 2, 3... up to the
point in time I need. If I had more backups in the same file, I'd first
have to find out which file number I had to restore for the fulle
backup and then I'd also have to find out from which filenumber the
logfile backup would start. Another thing that I don't like about
having many backups in one file, is when the backups starts to get too
big. In that case you suddenly get some quite big backup files that can
be a pain to "administer". The backup of one of our databases are
currently around 85 GB and if you have a number of these in the same
file, it will easily get quite "un-handy".<br>
I think the biggest advantage of having several files in one backup
file, is that it's easier to let SQL server delete old backups in the
file. If you have seperate files with naming by date, you'll have to do
some scripting to delete the files.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
<br>
</font></font>
</body>
</html>
--070701000301070106090101--|||In addition to the other posts:
I agree that there is no "best way" for this. Understand the backup command and the options (mainly
INIT and NOINIT). Then you can consider:
Maintenance plans (to do backups). It creates a new file for each time a backup is taken and also
remove files older than X days.
Do above with your own TSQL code. This opens the ability for further customization.
Have several backups on same file. For instance, have a Monday backup file which you backup to on
Mondays and overwrite each time. This will give you 7 days worth of backup generations. Or some
variation of this. One advantage is reduced number of files. Just make sure that the people doing
restore know about RESTORE HEADERONLY and FILELISTONY and the FILE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<davconts@.gmail.com> wrote in message news:1151992078.629506.142340@.p79g2000cwp.googlegroups.com...
> Hi all,
> Our client wants some guidance on how to handle backup files created by
> SQL Server 2005 for our product.
> I was thinking that we would create
> 1) a nightly backup to say e:\backup
> 2) create a transaction log backup every hour to e:\backup
> Now is it best to create a new file each time - or is it best to
> overwrite the original file?
> Reason I ask is the client will need to them back these files up to an
> external tape drive.
> It will probably be easier for them if they have the same 20+ files
> each time (ie. one full backup, and hourly transaction files)
> If the filenames incremented, then they would need to delete older
> files that have been backed up (which can invite user error)
> What are peoples opinions on this? Is there a "good way" to do this'
> Cheers
> David
>