Backup retention period Setting

In sql2005 the database backup retention has been added in sql server properties in database setting.

In 2000 we had a comfortable option to set retention based on maintenance plan,files and also our space availabilty.It has helped the dba's a lot.But it has been removed in sql 2005.

Is that sql server setting is the only retention period setting or do we have to set in anyother tabs..

Thanks

There are two places where backup retention is specified:

First, as you discovered, there is an instance-wide default setting for backup media retention.

Secondly, that value can be overridden for any backup operation by using the "BACKUP DATABASE WITH EXPIREDATE = " option. or the WITH RETAINDAYS option.

Thus, you can specify for each backup, and each backup job, what the retention should be.

Backup Question Old Files still attached

I was setting up the backup for a new database last evening and seem
to have created a small problem. Although I was able to create a
backup and restore it, in the process I seem to have left a couple of
other copies of the database files in a state where they cannot be
moved, deleted or copied.
I started by right clicking on the database and selecting Back Up. I
then did a Full Backup of the Database (not the Files and
Filegroups). I then detached the original database (probably a
mistake) and went to the Databases level, right-clicked and selected
Restore Database. I did not find the name of my database in the "To
Database:" list so I typed it in and proceeded to select the From
Database (which did have the name of my database and the correct
backup time) and restored the database.
The database seemed to work fine.
However when I checked the files I found not only my original
DatabaseName_Data.MDF and DatabaseName_Log.LDF files, but two new
ones
DatabaseName.MDF and DatabaseName_1.LDF. These last two files are
the
actual files being used by the restored database.
I would now like to do two things:
1) Move the original DatabaseName_Data.MDF and DatabaseName_Log.LDF
files to a new location, and
2) Rename the files that are actually being used from
DatabaseName.MDF
and DatabaseName_1.LDF to DatabaseName_Data.MDF and
DatabaseName_Log.LDF as they were before.
However I can't seem to do anything with the original files because
they seem to still be attached to the server. How can I SAFELY
accomplish what I want without endangering my good database (with the
wrong file names)?
Thanks.Detach the databases and do whatevery you want. Move them, rename them or
whatever and then reattach whichever one you want to use.
--
Ekrem Önsoy
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188538011.871157.326450@.q5g2000prf.googlegroups.com...
> I was setting up the backup for a new database last evening and seem
> to have created a small problem. Although I was able to create a
> backup and restore it, in the process I seem to have left a couple of
> other copies of the database files in a state where they cannot be
> moved, deleted or copied.
> I started by right clicking on the database and selecting Back Up. I
> then did a Full Backup of the Database (not the Files and
> Filegroups). I then detached the original database (probably a
> mistake) and went to the Databases level, right-clicked and selected
> Restore Database. I did not find the name of my database in the "To
> Database:" list so I typed it in and proceeded to select the From
> Database (which did have the name of my database and the correct
> backup time) and restored the database.
>
> The database seemed to work fine.
>
> However when I checked the files I found not only my original
> DatabaseName_Data.MDF and DatabaseName_Log.LDF files, but two new
> ones
> DatabaseName.MDF and DatabaseName_1.LDF. These last two files are
> the
> actual files being used by the restored database.
>
> I would now like to do two things:
>
> 1) Move the original DatabaseName_Data.MDF and DatabaseName_Log.LDF
> files to a new location, and
> 2) Rename the files that are actually being used from
> DatabaseName.MDF
> and DatabaseName_1.LDF to DatabaseName_Data.MDF and
> DatabaseName_Log.LDF as they were before.
>
> However I can't seem to do anything with the original files because
> they seem to still be attached to the server. How can I SAFELY
> accomplish what I want without endangering my good database (with the
> wrong file names)?
>
> Thanks.
>

Backup Plan

I am having a hard time setting up a backup plan for my sql server databases. Right now I create a copy of each database on another machine on the network once a day. Once a week I upload a MSAccess version to a remote site. Obviously there are huge amounts of potential data loss if a crash of some sort would occur locally.

My new plan is looking like this:

1) Run a complete database backup once a day

2) Run Transaction Log backups every hour

My questions are as follows:

1) What role does a "Backup Device" play in this process (I currently do everything through the Enterprise Manager interface)?

2) I am having a hard time scheduling mydatabase_complete.bak and mydatabase_trans.TRN at all. I get several errors.

3) I would like to run these backups on another network machine but am not seeing how to access "My Network Places" from the "Backup Database" utility in Enterprise Manager.

Any help in the form of answers or pointing me towards a useful resource for this process would be much appreciated!

Thanks in advance~1)A backup device is a logical name for a physical backup location that can be either on disk (a disk device) or tape (a tape device).
Rather than than having to specify the full pathname to the physical device, you can refrer to it by the Backup Device name.

2) What errors are you getting? Assuming that you have created a Job for both your Full and Transaction Log backups, you can simply set the schedule you want on the 'Schedules' tab. If you are using SQL Server 2005 you can pick from a list of existing schedules.

3) If you want to backup to a share on a network, you will need to specify the location using the full UNC path. You will also need to make sure that the MSSQLServer service is running under a Domain account rather than LocalSystem and that the Domain account has sufficient access to the share (i.e. Write permission).

I would advise you to take a look at BOL (Books On Line) to find out more about Backup/Restore.

HTH

Backup of Transaction Logs

When setting up a backup of the database the option to
backup the transaction logs is grayed out. How do I set up
a seperate backup for the T-Logs after I have set up
backup jobs for the data?Most probable reason is that the database is in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ray Ellers" <rellers@.flexiblesystems.com> wrote in message
news:40bf01c3aac7$6000b9e0$a601280a@.phx.gbl...
> When setting up a backup of the database the option to
> backup the transaction logs is grayed out. How do I set up
> a seperate backup for the T-Logs after I have set up
> backup jobs for the data?|||This is a multi-part message in MIME format.
--=_NextPart_000_01DB_01C3AA9E.4191E160
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
You will have to change the recovery model for the database to full or
bulk-logged.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Ray Ellers" <rellers@.flexiblesystems.com> wrote in message
news:40bf01c3aac7$6000b9e0$a601280a@.phx.gbl...
When setting up a backup of the database the option to
backup the transaction logs is grayed out. How do I set up
a seperate backup for the T-Logs after I have set up
backup jobs for the data?
--=_NextPart_000_01DB_01C3AA9E.4191E160
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You will have to change the recovery =model for the database to full or bulk-logged.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Ray Ellers" wrote in message news:40bf01c3aac7$60=00b9e0$a601280a@.phx.gbl...When setting up a backup of the database the option to backup the =transaction logs is grayed out. How do I set up a seperate backup for the T-Logs =after I have set up backup jobs for the data?

---

--=_NextPart_000_01DB_01C3AA9E.4191E160--