September 9, 2010 at 5:47 am
Hi,
What is the best way to check a particular database to confirm whether it is in full or simple recovery?
A related question, if we have confirmed that a given database is in full recovery, where do we look to see how often transaction logs are backed up?
Any insights are much appreciated.
Steve
September 9, 2010 at 6:01 am
SELECT [name] AS [DatabaseName]
,CONVERT(SYSNAME, Databasepropertyex(N''+ [name] + '', 'Recovery')) AS [RecoveryModel]
FROM MASTER.dbo.sysdatabases
ORDER BY [name]
GilaMonster (9/9/2010)
sysdatabases is deprecated, should not be used for new development and will be removed in a future version of SQL Server.
Thanks Gail 🙂
September 9, 2010 at 6:05 am
sysdatabases is deprecated, should not be used for new development and will be removed in a future version of SQL Server.
SELECT name, recovery_model_desc from sys.databases
As for log backups, they're in one of the MSDB tables (can't recall offhand which), recorded in the error log and visible in the job history of the log backup job.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2010 at 6:20 am
Thank you both.
What I understand from Gail's reply is, with respect to the log backups then, since we use DPM here, the best place to look would be in the job history of the log backup job on DPM.
Much appreciated,
Steve
September 9, 2010 at 6:35 am
Last I heard (may be a version or two ago though), DPM doesn't do log backups.
Check in the SQL error log. If it is doing a log backup, SQL will log it. Should be also in the msdb system tables (the backup tables)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2010 at 7:59 am
I spoke with someone who (hopefully) knows this here and I'm told that our version of DPM (2007) does do the log backups.
He also says that a differential/incremental backup is the same as a log backup. I can see how that could be the case but, is that true? Is it just a different term for the same thing?
I'll thank you in advance for help I may get, rather than posting back after the fact with a thank you.
Steve
September 9, 2010 at 8:10 am
SwayneBell (9/9/2010)
I spoke with someone who (hopefully) knows this here and I'm told that our version of DPM (2007) does do the log backups.
Double-check by reading the SQL error logs. Also, test and make sure that you can restore to a point-in-time via DPM.
http://www.brentozar.com/archive/2008/08/microsoft-dpm-2007-review/
N.B. A file-backup of the transaction log file (.ldf) is NOT the same as a transaction log backup.
He also says that a differential/incremental backup is the same as a log backup. I can see how that could be the case but, is that true? Is it just a different term for the same thing?
No, not at all.
A differential backup backs up the changes made since the last full backup. It is only a backupof the data at the exact time of backup and it cannot be used for point in time recovery. If you say have a differential backup running at 3pm and another at 4pm, you can restore the DB to 3pm or 4pm, but not 15h45.
Also SQL's backups are differential, not incremental. There is a difference
A log backup contains the log records since the last log backup (not since the last full or diff). It can be used for point-in-time recovery. You can chose to stop a log restore at any point within the time interval that the log backup covers.
ONLY log backups truncate the transaction log and mark the space within as reusable if you are running in full or bulk-logged recovery. Neither full nor differential backups do this. If you have a DB in full recovery and have no log backups, the log will grow until it fills the drive.
Oh, I may be a little biased against DPM, because a couple of weeks ago I spent too much time rebuilding system databases and reinstaling SQL becasue a restore of master and msdb from DPM left me with corrupt and unusable database files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2010 at 8:25 am
GilaMonster (9/9/2010)
SwayneBell (9/9/2010)
I spoke with someone who (hopefully) knows this here and I'm told that our version of DPM (2007) does do the log backups.Double-check by reading the SQL error logs. Also, test and make sure that you can restore to a point-in-time via DPM.
http://www.brentozar.com/archive/2008/08/microsoft-dpm-2007-review/
N.B. A file-backup of the transaction log file (.ldf) is NOT the same as a transaction log backup.
He also says that a differential/incremental backup is the same as a log backup. I can see how that could be the case but, is that true? Is it just a different term for the same thing?
No, not at all.
A differential backup backs up the changes made since the last full backup. It is only a backupof the data at the exact time of backup and it cannot be used for point in time recovery. If you say have a differential backup running at 3pm and another at 4pm, you can restore the DB to 3pm or 4pm, but not 15h45.
Also SQL's backups are differential, not incremental. There is a difference
A log backup contains the log records since the last log backup (not since the last full or diff). It can be used for point-in-time recovery. You can chose to stop a log restore at any point within the time interval that the log backup covers.
ONLY log backups truncate the transaction log and mark the space within as reusable if you are running in full or bulk-logged recovery. Neither full nor differential backups do this. If you have a DB in full recovery and have no log backups, the log will grow until it fills the drive.
Oh, I may be a little biased against DPM, because a couple of weeks ago I spent too much time rebuilding system databases and reinstaling SQL becasue a restore of master and msdb from DPM left me with corrupt and unusable database files.
Houston, we have a problem. The person that told me this is responsible for our backups.
I'll follow up - much appreciated Gail.
September 9, 2010 at 8:28 am
SwayneBell (9/9/2010)
Houston, we have a problem. The person that told me this is responsible for our backups.I'll follow up - much appreciated Gail.
Ooops... 😀
Hey, look on the bright side. You can clear this up now, not when you're trying to recover from a disaster. :hehe:
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2010 at 8:32 am
p.s. I'm not saying DPM is useless. i don't have enough experience with it to say that. Just check and make sure that you can restore what you need to restore and that the backups you expect to have really do exist.
This is not something you want to be figuring out when the DB is down and you're trying to recover.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2010 at 8:34 am
That's true Gail (although I'm starting to feel a headache coming on).
You may have helped us immeasureably.
September 9, 2010 at 8:54 am
A related question, if we have confirmed that a given database is in full recovery, where do we look to see how often transaction logs are backed up?Steve
if you got your answer then please ignore.
If not then you can use below query :-
select top 10 * from msdb.dbo.restorehistory
where destination_database_name = 'yourdbname' order by restore_date desc
----------
Ashish
September 9, 2010 at 9:15 am
The following query should give you the last 3 backups for each database and include the columns: database_name, recovery_model, backup_type, size_gb, backup_start_date, backup_finish_date, and duration_minutes.
select * from
(
select
database_name,
recovery_model,
case type
when 'd' then 'database'
when 'i' then 'differential database'
when 'l' then 'log'
when 'f' then 'file or filegroup'
when 'g' then 'differential file'
when 'p' then 'partial'
when 'q' then 'differential partial'
else '?'
end backup_type,
convert( numeric(6,2), backup_size /1024 / 1024 / 1024) as size_gb,
backup_start_date, backup_finish_date,
datediff( minute, backup_start_date, backup_finish_date) duration_minutes,
rank() over (partition by database_name, type order by backup_start_date desc) as backup_seq
from msdb..backupset
) x
where backup_seq <= 3
order by database_name, backup_seq;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 9, 2010 at 9:20 am
Eric Russell 13013 (9/9/2010)
The following query should give you the last 3 backups for each database and include the columns: database_name, recovery_model, backup_type, size_gb, backup_start_date, backup_finish_date, and duration_minutes.
select * from
(
select
database_name,
recovery_model,
case type
when 'd' then 'database'
when 'i' then 'differential database'
when 'l' then 'log'
when 'f' then 'file or filegroup'
when 'g' then 'differential file'
when 'p' then 'partial'
when 'q' then 'differential partial'
else '?'
end backup_type,
convert( numeric(6,2), backup_size /1024 / 1024 / 1024) as size_gb,
backup_start_date, backup_finish_date,
datediff( minute, backup_start_date, backup_finish_date) duration_minutes,
rank() over (partition by database_name, type order by backup_start_date desc) as backup_seq
from msdb..backupset
) x
where backup_seq <= 3
order by database_name, backup_seq;
Interesting, and thank you Eric.
I ran this and I see only 'database' and 'differential database' backups, no log backups even on databases in full recovery.
Does anyone have Tequila handy?
September 9, 2010 at 9:23 am
I can get you some Marula if you want.
Query sys.databases and take a look at the log_reuse_wait_desc for the databases in full recovery. Run DBCC SQLPERF(LogSpace) and see how full your transaction logs are.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply