Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server table backups location Expand / Collapse
Author
Message
Posted Thursday, April 29, 2010 6:44 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:08 AM
Points: 744, Visits: 1,056
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





Post #912763
Posted Thursday, April 29, 2010 6:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:56 AM
Points: 1,002, Visits: 884
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!

Post #912768
Posted Thursday, April 29, 2010 6:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 1:06 PM
Points: 2,827, Visits: 8,479
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.



Post #912777
Posted Thursday, April 29, 2010 6:59 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:08 AM
Points: 744, Visits: 1,056
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.
Post #912786
Posted Thursday, April 29, 2010 7:00 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:08 AM
Points: 744, Visits: 1,056
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
Post #912788
Posted Thursday, April 29, 2010 7:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:56 AM
Points: 1,002, Visits: 884
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!

Post #912792
Posted Thursday, April 29, 2010 7:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:56 AM
Points: 1,002, Visits: 884
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!

Post #912794
Posted Thursday, April 29, 2010 7:14 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:08 AM
Points: 744, Visits: 1,056
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?
Post #912808
Posted Thursday, April 29, 2010 7:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:56 AM
Points: 1,002, Visits: 884
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!

Post #912817
Posted Thursday, April 29, 2010 8:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 10:45 AM
Points: 5,572, Visits: 6,356
I don't think the Copy-Only Backups get stored in the tables, though. Do they?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #912946
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse