Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server table backups location


SQL Server table backups location

Author
Message
river1
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
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
GregoryF
GregoryF
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 885
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
homebrew01
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2976 Visits: 9071
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
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
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
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
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
GregoryF
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 885
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
GregoryF
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 885
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
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
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
GregoryF
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1014 Visits: 885
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
Brandie Tarvin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7737 Visits: 8714
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/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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search