SQL Server table backups location

Question

SQL Server table backups location

river1

SSC-Insane

Points: 24309
More actions
April 29, 2010 at 6:44 am

#139913

Hi,
I need to know some information about backups.
1) in SQL Server 2005 where is the table that as the information about all the backups that where made to databases?
2) If i make a backup of one database in query window , like:
backup database test to disk ='c:\example.BAK'
will this backup be stored in that table (point 1)?
3) All the backups made to system databases (master, model, etc...) are stored in that table too?
4) Suppose that i have a database named test1 and i make two backups from query window, then i restore one of the backups that i made. The first Test1 database will be replaced by the backup of the test1 backup information. will the two backups made to the first test1 database will still be in the table of backups? or because the database was restored the backups associated with it will be deleted?
Thank you

Viewing 14 posts - 1 through 13 (of 13 total)

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

GregoryF SSCertifiable Points: 6296 More actions · Answer 1

river1 (4/29/2010)
Hi,
I need to know some information about backups.
1) in SQL Server 2005 where is the table that as the information about all the backups that where made to databases?
2) If i make a backup of one database in query window , like:
backup database test to disk ='c:\example.BAK'
will this backup be stored in that table (point 1)?
3) All the backups made to system databases (master, model, etc...) are stored in that table too?
4) Suppose that i have a database named test1 and i make two backups from query window, then i restore one of the backups that i made. The first Test1 database will be replaced by the backup of the test1 backup information. will the two backups made to the first test1 database will still be in the table of backups? or because the database was restored the backups associated with it will be deleted?
Thank you

1) msdb..backupset (msdb..backupfile also has lot's of backup info)

2) yes, table above

3) yes, all backups

4) don't follow you

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

4) The backup related tables in MSDB store information about the backups and restores that have taken place .... the history of activity. Not the actual backup data.

None of the history information gets deleted unless you run jobs to delete it.

river1 SSC-Insane Points: 24309 More actions · Answer 3

point 4:

So , if i have a database named test1 and make three backups and then restore one of the backups, those three backups will still be in that table, because they where made in that SQL Server 2005 instance.

You can only delete the historyc if you restore the msdb will and older backup or if you directly delete records from this table, correct?

Thank you.

river1 SSC-Insane Points: 24309 More actions · Answer 4

Point 1:

"msdb..backupset (msdb..backupfile also has lot's of backup info)"

Didn't understood... what's the correct table to query and see the backups?

Thnak you

GregoryF SSCertifiable Points: 6296 More actions · Answer 5

river1 (4/29/2010)
Point 1:
"msdb..backupset (msdb..backupfile also has lot's of backup info)"
Didn't understood... what's the correct table to query and see the backups?
Thnak you

Both tables contain information on backups taken, take a look for yourself. They are related.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

GregoryF SSCertifiable Points: 6296 More actions · Answer 6

river1 (4/29/2010)
point 4:
So , if i have a database named test1 and make three backups and then restore one of the backups, those three backups will still be in that table, because they where made in that SQL Server 2005 instance.
You can only delete the historyc if you restore the msdb will and older backup or if you directly delete records from this table, correct?
Thank you.

The tables contain all information until purged. When you drop a db through the GUI, you are always asked if you want to delete backup/restore history. These are some of the tables it deletes from

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

river1 SSC-Insane Points: 24309 More actions · Answer 7

Point 1:

It seems that this table backupset only contains information of the backups that where made to backup devices. At the company we make backups to file. when i make a backup like:

backup database sgt to disk ='c:\example.BAK' the name column of the table appears as NULL , why?

GregoryF SSCertifiable Points: 6296 More actions · Answer 8

river1 (4/29/2010)
Point 1:
It seems that this table backupset only contains information of the backups that where made to backup devices. At the company we make backups to file. when i make a backup like:
backup database sgt to disk ='c:\example.BAK' the name column of the table appears as NULL , why?

I had also suggested that you look at msdb..backupfile

The name appears as Null because you did not give the backup a name, a complete backup statement looks like (note the inclusion of name and description, these are not always necessary, but may be helpful in some situations)

backup database [Test] to

disk = 'E:\TmpBkp\Test\Test__Full.bak'

with format, init, MaxTransferSize = 4194304, BufferCount = 64,

name = 'Test Full backup.', description = 'Test DB Full Backup 20100428'

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 9

I don't think the Copy-Only Backups get stored in the tables, though. Do they?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

GregoryF SSCertifiable Points: 6296 More actions · Answer 10

Brandie Tarvin (4/29/2010)
I don't think the Copy-Only Backups get stored in the tables, though. Do they?

Yes, all backup operations are in msdb..backupset and msdb..backupfile

Just to be sure before I posted this, I did a copy_only and it appeared in both tables

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

AlexSQLForums SSChampion Points: 14277 More actions · Answer 11

Did anyone figure out where backup locations are stored?

it's not in msdb..backupfile or msdb..backupset.

I'm looking for "D:\DBBACKUPS\test123_080520101200.BAK" after i execute:

backup database test123 to disk ='D:\DBBACKUPS\test123_080520101200.BAK'

Alex S

AlexSQLForums SSChampion Points: 14277 More actions · Answer 12

backup locations are located in:

msdb..backupmediafamily

Alex S

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 13

Copy-only backups are recorded in the is_copy_only column of the backupset table.

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

Manu