How to reset the list of backup for a restore process?

Question

How to reset the list of backup for a restore process?

tilew-948340

Hall of Fame

Points: 3431
More actions
August 13, 2012 at 11:48 am

#279667

I did many tests of backups and restores on a DB
Many of the backups are bad (have bad data) and I want to remove them from the possible "backup" you can restore.
A complete reset of the list would do the job, but how do you reset this list?

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What list are you talking about?

If the list that the SSMS restore dialog brings up, that's just the backup history in MSDB. You can use SQL Agent with the backup history cleanup job to regularly clean that up.

That said, that list is just for convenience, you can use the device option to restore any backup file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 2

GilaMonster (8/13/2012)
What list are you talking about?
If the list that the SSMS restore dialog brings up, that's just the backup history in MSDB. You can use SQL Agent with the backup history cleanup job to regularly clean that up.
That said, that list is just for convenience, you can use the device option to restore any backup file.

SQL agent... what is that? if it is SQL server agent, it was not even started on the machine. I just started it, but I see nothing about reset history... how dow I do that?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

http://msdn.microsoft.com/en-us/library/ms177182.aspx

There is a maintenance plan option for backup history cleanup.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 4

SQLRNNR (8/13/2012)
http://msdn.microsoft.com/en-us/library/ms177182.aspx
There is a maintenance plan option for backup history cleanup.

I can't do it...

From MSDN "Create a Maintenance Plan (Maintenance Plan Design Surface)", I found this:

To create a maintenance plan

1.In Object Explorer, click the plus sign to expand the server where you want to create a maintenance plan.

2.Click the plus sign to expand the Management folder.

I can't find the "Management folder", but surely it seems to be applicable for SQL 2012 (I have SQL server 2008 R2). Is there something similar for 2008 R2?

BTW, I reset the "database" list by doing a backup with the option "Back up to new media set and erase all existing backup sets", and the list from "Tasks\Restore\Database" (in Management Studio) is only one line (wich is what I am looking for but for the "restore files and filegroups" wich is not reset even if I do a "file and filegroups" backup...?)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

SQL 2008's the same, those instructions aren't 2012 specific

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

tilew-948340 (8/13/2012)
BTW, I reset the "database" list by doing a backup with the option "Back up to new media set and erase all existing backup sets",

So the list you were talking about is not the one that comes from msdb, it's from making multiple backups to the same file. The way to not get that list is to always do your backups to new files or specify the WITH INIT option (which is that the "backup to new media set" option does). Otherwise you get multiple backups in the same file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 7

GilaMonster (8/13/2012)
tilew-948340 (8/13/2012)
BTW, I reset the "database" list by doing a backup with the option "Back up to new media set and erase all existing backup sets",
So the list you were talking about is not the one that comes from msdb, it's from making multiple backups to the same file. The way to not get that list is to always do your backups to new files or specify the WITH INIT option (which is that the "backup to new media set" option does). Otherwise you get multiple backups in the same file.

Not really. The list is not read from the file because I delete the file.

The situation here is: DB is not on network, but it is on many laptops with a copy of the DB. Many users make changes at the same time on each laptop. They do backups from the interface to a file just in case an error happened (like the time that all data from a table has been deleted!). Then I have to "synchronized" those copies (still no network!) to a "master" (ya, its a nightmare, but there are rules to make sure I keep the good data) and then I delete the copy of the DB and the backup files on those laptops. Usualy, it is always the last backup I have to restore, so it is not a big deal (old backup can't be restore as the file does not exist anymore), but the scroll down from the list is beginning to be long and with all the tests I made, it is even longer!

So If there is a way to reset the list, that would be a good clean.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

tilew-948340 (8/13/2012)
GilaMonster (8/13/2012)
tilew-948340 (8/13/2012)
BTW, I reset the "database" list by doing a backup with the option "Back up to new media set and erase all existing backup sets",
So the list you were talking about is not the one that comes from msdb, it's from making multiple backups to the same file. The way to not get that list is to always do your backups to new files or specify the WITH INIT option (which is that the "backup to new media set" option does). Otherwise you get multiple backups in the same file.
Not really. The list is not read from the file because I delete the file.

If taking a backup with the option "Back up to new media set and erase all existing backup sets" cleared the list you are talking about, then that list was the list of backup sets within a single file, because the only thing that option does is erase any existing backups within the file as if you were backing up to a new file.

If you are talking about some other list (that backing up with the option "Back up to new media set and erase all existing backup sets" does not clear) then please be clear exactly what list you are talking about and where it is.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 9

GilaMonster (8/13/2012)

If taking a backup with the option "Back up to new media set and erase all existing backup sets" cleared the list you are talking about, then that list was the list of backup sets within a single file, because the only thing that option does is erase any existing backups within the file as if you were backing up to a new file.

My error: the "(right click on DB)\Tasks\Restore\Database" always shows one row, making an erase process or not.

If you are talking about some other list (that backing up with the option "Back up to new media set and erase all existing backup sets" does not clear) then please be clear exactly what list you are talking about and where it is.

From management studio, if I do a "(right click on DB)\Tasks\Restore\Files and FileGroups" restore (File and fileGroup let me choose a specific backup in time, losing some data if any, but that is what I want), all the backups I have done on the database (even the backup done from a deleted file) can be seen in the list.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Restore files and filegroup doesn't sound like something you want to do. That's for piecemeal restore and partial restores, not simple database restores.

Screenshot please? Attach it to your post.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 11

tilew-948340 (8/13/2012)
I did many tests of backups and restores on a DB
Many of the backups are bad (have bad data) and I want to remove them from the possible "backup" you can restore.
A complete reset of the list would do the job, but how do you reset this list?

use this to delete backup\restore history for a specific database

exec sp_delete_database_backuphistory @database_name = 'yourdbname'

Or this will delete history for all databases older than the date specified

exec sp_delete_backuphistory @oldest_date = 'oldest_date'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Perry Whittle SSC Guru Points: 234013 More actions · Answer 12

SQLRNNR (8/13/2012)
http://msdn.microsoft.com/en-us/library/ms177182.aspx
There is a maintenance plan option for backup history cleanup.

That cleans up the actual physical backup or text files and essentially just executes

xp_delete_file

It does not clear the actual history.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 13

GilaMonster (8/13/2012)
Restore files and filegroup doesn't sound like something you want to do. That's for piecemeal restore and partial restores, not simple database restores.
Screenshot please? Attach it to your post.

I can't do screenshot. Remember: it is not on network! but I can show you a sample of what I am talking about if you want to see what it looks like. Is it what you want?

Usualy, all backups are "full" backup, rarely partials. I restore going back in time, backups after backups from the most recent to the last time the DB was OK or until the last sync between the laptops. If the error was made before a sync... well, it is too bad. Can't restore it...it took us two days and a half to redo the DB with appropriate data last time... which is not bad considering it could have been two months...

But... when you say "not SIMPLE database restore" you mean "not easy to do" or you reference to the type of DB (simple vs full)?

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 14

Perry Whittle (8/13/2012)
use this to delete backup\restore history for a specific database
use msdb;
go
exec sp_delete_database_backuphistory @database_name = 'yourdbname'

Seems to do the job!!!!

Thank you very very much!!!