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

Identify the Backup used to Restore the Database Expand / Collapse
Author
Message
Posted Thursday, February 27, 2014 12:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 1:23 AM
Points: 117, Visits: 463
Comments posted to this topic are about the item Identify the Backup used to Restore the Database
Post #1545723
Posted Thursday, February 27, 2014 12:20 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
Interesting article.

You could also take advantage of sys.database_recovery_status




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1545727
Posted Thursday, February 27, 2014 12:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 1:23 AM
Points: 117, Visits: 463
Will definitely put this information into action the next time.
Post #1545735
Posted Thursday, February 27, 2014 7:13 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:44 PM
Points: 133, Visits: 851
Customers generally don't remember how their own backup scheme is set up. Jumping into the history files has helped me know the chain of full and log backups and how I can go about restoring them.

A more common problem for me is getting tiny backup files from customers and thinking I can restore them on my laptop. Wrong! They might have allocated 50 GB but only have 1 GB of data used.

I've also had the situation where a full 1 GB database restored but as soon as I did an update it tried to allocate 20 GB of disk according to the database settings for AutoGrowth.
Post #1545869
Posted Thursday, February 27, 2014 7:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 7:53 AM
Points: 540, Visits: 501
--this script will give you more details like who restored and backedup....

SELECT
DatabaseRestoredTo = RH.destination_database_name,
TimeOfRestore = RH.restore_date,
UserImplimentingRestore = RH.user_name,
RestoreType = CASE RH.restore_type WHEN 'D' THEN 'Full DB Restore'
WHEN 'F' THEN 'File Restore'
WHEN 'G' THEN 'Filegroup Restore'
WHEN 'I' THEN 'Differential Restore'
WHEN 'L' THEN 'Log Restore'
WHEN 'V' THEN 'Verify Only'
END,
ServerWhereBackupTaken = BS.server_name,
UserWhoBackedUpTheDatabase = BS.user_name,
BackupOfDatabase = BS.database_name,
DateOfBackup = BS.backup_start_date,
RestoredFromPath = BMF.physical_device_name
FROM
msdb.dbo.restorehistory RH
INNER JOIN
msdb.dbo.backupset BS
ON
RH.backup_set_id = BS.backup_set_id
INNER JOIN
msdb.dbo.backupmediafamily BMF
ON
BS.media_set_id = BMF.media_set_id
ORDER BY
RH.restore_history_id
Post #1545890
Posted Thursday, February 27, 2014 11:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:46 AM
Points: 1,353, Visits: 731
If the backup history tables have not yet been cleared, there is an easier way to find out if the restored backup came from a different server that doesn't involve mucking about with LSNs. This information is in the backupset table in msdb in the server_name field. Yes, this table is populated during a restore and yes, when restoring a backup from another server, this column contains the name of the server the backup was from.

http://shaunjstuart.com/archive/2010/12/msdb-backupset-table-peculiarity/

Post #1546025
Posted Thursday, February 27, 2014 11:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 1:51 PM
Points: 21,644, Visits: 15,317
shaun.stuart (2/27/2014)
If the backup history tables have not yet been cleared, there is an easier way to find out if the restored backup came from a different server that doesn't involve mucking about with LSNs. This information is in the backupset table in msdb in the server_name field. Yes, this table is populated during a restore and yes, when restoring a backup from another server, this column contains the name of the server the backup was from.

http://shaunjstuart.com/archive/2010/12/msdb-backupset-table-peculiarity/



Thanks for that additional info Shaun.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1546047
Posted Thursday, February 27, 2014 8:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 1:23 AM
Points: 117, Visits: 463
Hi Shaun,
I tried it out myself this morning to check if the restore entry goes in backupset table but could not succeed in that. I have been using backupset for quite sometime now and have not seen a restore putting an entry there. I went through you blog on this but could find a restore/backup script to replicate the senario. If you could help us with the script to push a restore entry in backupset that will be helpful.

Thanks,
Tushar Kanti
Post #1546183
Posted Friday, February 28, 2014 7:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 10:46 AM
Points: 1,353, Visits: 731
Tushar Kanti (2/27/2014)
Hi Shaun,
I tried it out myself this morning to check if the restore entry goes in backupset table but could not succeed in that. I have been using backupset for quite sometime now and have not seen a restore putting an entry there. I went through you blog on this but could find a restore/backup script to replicate the senario. If you could help us with the script to push a restore entry in backupset that will be helpful.

Thanks,
Tushar Kanti


It's very simple - just do a basic backup and restore:

On Server A:

BACKUP DATABASE MyDatabase
TO DISK = '<backup path>\MyDatabase.bak'

On Server B:

RESTORE DATABASE MyDatabase
FROM DISK='<backup path>\MyDatabase.bak'

Then, on Server B, run this:

USE msdb

SELECT server_name,machine_name,database_name,backup_start_date
FROM backupset
ORDER BY backup_finish_date DESC

You will see an entry for MyDatabase with "Server A" in both the server_name and machine_name fields.


I've just verified this behavior on SQL 2000, 2005, 2008 R2, and 2012. Note that this was done using native T-SQL backup commands. If you are using third party backup software, this behavior may be different.
Post #1546314
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse