SQL Clone
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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4562 Visits: 1366
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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1830 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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12744 Visits: 9222
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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4562 Visits: 1366
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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4562 Visits: 1366
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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1830 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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1830 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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4562 Visits: 1366
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
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1830 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
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39096 Visits: 9291
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