473,322 Members | 1,493 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

SQL Server Backups

I'm trying to create a backup set which maintains only a fixed number of
days. As such, I've got the following script:

BACKUP DATABASE [mydb] to [mydb Backup Set] WITH NOINIT, NOUNLOAD,
NAME=N'My Database Backup', NOSKIP, STATS=10, NOFORMAT,
RETAINDAYS=5
DECLARE @i INT
select @i=position from msdb.backupset where database name='mydb'
and type!='F' amd backup set id=(select max(backupset set id) from
msdb.backupset where database name='mydb')
RESTORE VERIFYONLY FROM [mydb Backup Set] WITH FILE=@i

This script was created automatically, but I added the RETAINDAYS bit. I
only want the past five days of backups available, but when I check the
list, I've got backups dating back to December 2003! The older backups
are correctly being marked as expired, but they are still on the list!
How can I purge the list to only have the last five days of backups?

Thanks in advance

Johnny
Jul 20 '05 #1
1 4679

"Loopy" <jj****@yahoo.com> wrote in message
news:f3*************************@posting.google.co m...
I'm trying to create a backup set which maintains only a fixed number of
days. As such, I've got the following script:

BACKUP DATABASE [mydb] to [mydb Backup Set] WITH NOINIT, NOUNLOAD,
NAME=N'My Database Backup', NOSKIP, STATS=10, NOFORMAT,
RETAINDAYS=5
DECLARE @i INT
select @i=position from msdb.backupset where database name='mydb'
and type!='F' amd backup set id=(select max(backupset set id) from
msdb.backupset where database name='mydb')
RESTORE VERIFYONLY FROM [mydb Backup Set] WITH FILE=@i

This script was created automatically, but I added the RETAINDAYS bit. I
only want the past five days of backups available, but when I check the
list, I've got backups dating back to December 2003! The older backups
are correctly being marked as expired, but they are still on the list!
How can I purge the list to only have the last five days of backups?

Thanks in advance

Johnny


RETAINDAYS is used to indicate that the backup media cannot be overwritten
before a certain number of days - it doesn't automatically delete old
backups or clean up data. To remove old backup information, you can use
sp_delete_backuphistory. If you want to remove old backups physically, then
one way is to set up a database maintenance plan which removes the old files
after a certain number of days.

Simon
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Sgt. Sausage | last post by:
I've got a server (SQL 2K, Win2K) where the backups have started running long. The database is a bit largish -- 150GB or so. Up until last month, the backups were taking on the order of 4 to 5...
1
by: Hai-Chu Hsu | last post by:
Hi, I want to restore my databases from the old SQL Server installation into a new installation of SQL Server. My new installation of SQL Server has different data path from the old...
1
by: David McGeorge | last post by:
Coming more from Oracle background, I want to do a point in time recovery test on SQl Server. Let's say database PUBS backed up every night and Trans log backup 3 time every 6 hours between 6 a.m....
2
by: greggarpin | last post by:
Hi All. I'm currently maintaining 4 servers - 1 for public/customers and 3 for backups, development, etc... I regularly backup the entire SQL database for our public server and restore it on...
3
by: John | last post by:
Hi All, During the past couple of years I have been maintaining the company's Access databases, in the coming weeks I will be migrating data to SQL server, I will be using the Access forms as a...
16
by: DataPro | last post by:
New to Sql Server, running SQL Server 2000. Our transaction log file backups occasionally fail as the size of the transaction log gets really huge. We'd like to schedule additional transaction...
0
by: bendecko | last post by:
Hi I'm look for a solution to this problem. I'm a web developer with many clients whose SQL databases are spread over many SQL servers all over the internet. Most of the ISPs/hosts that do...
11
by: RoB | last post by:
Hi all, I'm coming from the Informix world and I have a customer using DB2 8.2.3 for Linux on Red Hat Enterprise ES. The customer is performing filesystem backups of the containers etc every...
2
by: m19peters | last post by:
We have a script that I had to rework a little bit for 2005 that does a full backup for every database on the server... For some reason on some nights the script does not backup all databases......
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.