purging old tranlogs

hi !
I have a job that creates tranlog backups every 15 mins and makes a full database backup at midnight.
I want to purge the old logs after the full backup , how can I do that ?
thanks
SamiHave you considered instead using the Database Maintenance Wizard? You can suppy a value to specify how long to retain both full backups and transaction log backups. It won't precisely meet your requirements since unneeded transaction logs will be retained even after a full backup, but it is simple to create, easy to maintain and easy for anyone to understand (since the database maintenance wizard is pretty well documented in MS texts).

I generally create two maintenance plans: one for system databases (where transaction logs do not need to be backed up) and one for user databases (where transaction logs are backed up).

Regards,

hmscott

hi !
I have a job that creates tranlog backups every 15 mins and makes a full database backup at midnight.
I want to purge the old logs after the full backup , how can I do that ?

thanks
Sami|||The maintenance wizard simply creates a job the class the xp_sqlmaint procedure. Frankly, you are better off bypassing the wizard and creating a the job yourself. You can look up all the parameters available (including purging old files) in Books Online under "sqlmaint Utility".

Purging data from database every 3 months

Every 3 months I need to backup that sql database and archive it off. I only want to keep 3 months of data in the database and want to know how you can set a backup to run and then remove all the data older than a specified date. I am guessing you will have to back it all up then run a query to remove the data I dont want. ANy ideas if this can be done with a simple backup routine ?You can't purge the old data using BACKUP. You have to execute TSQL commands to delete the old data,
which in turn of course mean that the data need some datetime column from which you can determine if
it is > 3 months old. Basically, the TSQL script will look something like:
DECLARE @.now datetime
SET @.now = CURRENT_TIMESTAMP
DELETE FROM tbl1 WHERE DATEDIFF(month, dtcol, @.now) > 3
DELETE FROM tbl2 WHERE DATEDIFF(month, dtcol, @.now) > 3
Make sure you check that I get the DATEDIFF right. Also, you might want to protect the delete's
inside a transaction. And if you have relationships between the tables you need to start with the
referencing table before you can delete from the referenced table (unless you have cascading foreign
keys). If you aren't well versed in TSQL, I suggest you get someone who is which can help you with
that TSQL script.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"c1tr1cks" <anonymous@.discussions.microsoft.com> wrote in message
news:EED1D1C0-4A93-4822-AB09-1CDEBCBA6277@.microsoft.com...
> Every 3 months I need to backup that sql database and archive it off. I only want to keep 3 months
of data in the database and want to know how you can set a backup to run and then remove all the
data older than a specified date. I am guessing you will have to back it all up then run a query to
remove the data I dont want. ANy ideas if this can be done with a simple backup routine ?|||You can see an example here:
http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"c1tr1cks" <anonymous@.discussions.microsoft.com> wrote in message
news:EED1D1C0-4A93-4822-AB09-1CDEBCBA6277@.microsoft.com...
Every 3 months I need to backup that sql database and archive it off. I only
want to keep 3 months of data in the database and want to know how you can
set a backup to run and then remove all the data older than a specified
date. I am guessing you will have to back it all up then run a query to
remove the data I dont want. ANy ideas if this can be done with a simple
backup routine ?

Pull replication errors...

Hi,
the idea is to setup an offsite backup of our single sql server, have
created a publication on the MASTER server, and pulled it to the offsite
server using FTP, downloaded snapshot, replicated transactions, all appeared
to work, generated schema, but the frontend database app didnt run, loads of
various errors showing up.
decided to restore the database from disk to the backup server, to check the
app is working fine, which it now is, so all i need to do is replicated the
changes in the tables on the master server to the offsite server, only needs
to be one-way, the offsite is only for backup read only...changed the
publication to tables only, re-created snapshot and now get errors saying...
Cannot update identity column. The step failed
help please on solution or other ways to achive this.
comment the identity column update in the update proc.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Matt" <mattnj@.hotmail.com> wrote in message
news:d9tqpf$gkn$1@.news.freedom2surf.net...
> Hi,
> the idea is to setup an offsite backup of our single sql server, have
> created a publication on the MASTER server, and pulled it to the offsite
> server using FTP, downloaded snapshot, replicated transactions, all
> appeared to work, generated schema, but the frontend database app didnt
> run, loads of various errors showing up.
> decided to restore the database from disk to the backup server, to check
> the app is working fine, which it now is, so all i need to do is
> replicated the changes in the tables on the master server to the offsite
> server, only needs to be one-way, the offsite is only for backup read
> only...changed the publication to tables only, re-created snapshot and
> now get errors saying...
> Cannot update identity column. The step failed
> help please on solution or other ways to achive this.
>

Pull Merge - Schema Change Version

I have an issue with one of my subscribers getting the dreaded "The
Publisher has been restored from a backup whose schema change version is
different from the Subscriber." error on one of the publications. I have a
total of 6 publications and the error only occurs one of them. After
receiving the error I removed all the subscriptions and recreated them with
scripts that I've been using on all my subscribers. The same occur still
occurs on the same subscription. I've run out of ideas on how to solve
the issue.
Any help would be appreciated!
Thanks
Tina
Tina,
this error message is one of the things which have been solved in sp3, if
you are using merge replication with @.enabled_for_internet set to true (see
http://support.microsoft.com/default...ticle%3D319961).
HTH,
Paul Ibison
|||Hi Paul,
Unfortunately I have that parameter set to false and I'm on SP3.
Any other ideas?
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23yK$AcWLEHA.988@.TK2MSFTNGP11.phx.gbl...
> Tina,
> this error message is one of the things which have been solved in sp3, if
> you are using merge replication with @.enabled_for_internet set to true
(see
>
http://support.microsoft.com/default...ticle%3D319961).
> HTH,
> Paul Ibison
>

Pubs Transaction log

I can't backup the transaction log for the pubs database
on any systems I have. I even made some bogus transactions
to have something in the log. Is backing up the Pubs T/log
not allowed? Thanks in advance for your reply.--Denhi Den,
Its because recovery model of pubs database must be simple. change it to
bulk-logged/full and then try taking backup.
- Vishal

Pubs Transaction log

I can't backup the transaction log for the pubs database
on any systems I have. I even made some bogus transactions
to have something in the log. Is backing up the Pubs T/log
not allowed? Thanks in advance for your reply.--Denhi Den,
Its because recovery model of pubs database must be simple. change it to
bulk-logged/full and then try taking backup.
--
- Vishal