September 1, 2010 at 9:37 pm
I have a full back and a differential backup taken right after full backup. When i use these backups to create another db i get error saying backup is not restored to complete. Whats the best way to validate and make sure that the correct differential backup is used. I did look up the lsn of full and differential backup but couldn't tell if it was in order or not. How do i do that?
September 1, 2010 at 11:50 pm
Check using RESTORE HEADERONLY & RESTORE LABEL ONLY...
Posting the exact error will help you
September 1, 2010 at 11:56 pm
there is no error, what values should we compare to see if the backups are in order or not. For obvious reasons i cant post the results. thanks
September 2, 2010 at 5:44 am
iqtedar (9/1/2010)
I have a full back and a differential backup taken right after full backup. When i use these backups to create another db i get error saying backup is not restored to complete. Whats the best way to validate and make sure that the correct differential backup is used. I did look up the lsn of full and differential backup but couldn't tell if it was in order or not. How do i do that?
Following SP will help you to find the order of taken backup
USE MASTER
GO
IF OBJECT_ID('USP_DBA_RECENTBACKUPS') IS NOT NULL
DROP PROC USP_DBA_RECENTBACKUPS
GO
--EXEC USP_DBA_RECENTBACKUPS 'DOMAIN_dba','R'
CREATE PROC USP_DBA_RECENTBACKUPS
@P_DBNAME VARCHAR(200)= NULL,
@PLAN CHAR(1)= NULL
AS
BEGIN
DECLARE @DB_NAME VARCHAR(200)
SET @DB_NAME=@P_DBNAME
IF @DB_NAME IS NULL
BEGIN
WITH
BACKUP_RECENT AS
(
SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,
BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,
SERVER_NAME,DATABASE_NAME,[TYPE]
FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B
ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)
) BACKUPS
GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME
),
BACKUP_ALL AS
(
SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,
PHYSICAL_DEVICE_NAME
FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B
ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)
)
SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=
CASE WHEN [TYPE]='D' THEN 'FULL'
WHEN [TYPE]='I' THEN 'DIFFERENTIAL'
WHEN [TYPE]='L' THEN 'LOG'
WHEN [TYPE]='F' THEN 'FILE / FILEGROUP'
WHEN [TYPE]='G' THEN 'DIFFERENTIAL FILE'
WHEN [TYPE]='P' THEN 'PARTIAL'
WHEN [TYPE]='Q' THEN 'DIFFERENTIAL PARTIAL'
END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL
WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID
ORDER BY SERVER_NAME,DATABASE_NAME,BACKUP_DATE
END
ELSE
BEGIN
IF @PLAN = 'R'
BEGIN
WITH
BACKUP_RECENT AS
(
SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,
BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,
SERVER_NAME,DATABASE_NAME,[TYPE]
FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B
ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)
) BACKUPS
GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME
),
BACKUP_ALL AS
(
SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,
PHYSICAL_DEVICE_NAME
FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B
ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)
)
SELECT * FROM(
SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=
CASE WHEN [TYPE]='D' THEN 'FULL'
WHEN [TYPE]='I' THEN 'DIFFERENTIAL'
WHEN [TYPE]='L' THEN 'LOG'
WHEN [TYPE]='F' THEN 'FILE / FILEGROUP'
WHEN [TYPE]='G' THEN 'DIFFERENTIAL FILE'
WHEN [TYPE]='P' THEN 'PARTIAL'
WHEN [TYPE]='Q' THEN 'DIFFERENTIAL PARTIAL'
END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL
WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID AND DATABASE_NAME=@P_DBNAME AND [TYPE] IN ('D','I')
UNION
SELECT
SERVER_NAME,DATABASE_NAME,BAKUPTYPE='LOG',
BACKUP_FINISH_DATE BACKUP_DATE,PHYSICAL_DEVICE_NAME [LOCATION]
FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B
ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)
WHERE [TYPE]='L'
AND BACKUP_FINISH_DATE>
(
SELECT TOP 1 BACKUP_FINISH_DATE FROM MSDB.DBO.BACKUPSET WHERE [TYPE] IN ('D','I')
AND DATABASE_NAME= @DB_NAME ORDER BY BACKUP_FINISH_DATE DESC
)
AND DATABASE_NAME= @DB_NAME ) AS RESTORE_PLAN
ORDER BY [RECENT BACKUP]
END
ELSE
BEGIN
WITH
BACKUP_RECENT AS
(
SELECT MAX(BACKUP_DATE) BACKUP_DATE,MAX(ID) ID,[TYPE],SERVER_NAME,DATABASE_NAME
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,
BACKUP_FINISH_DATE BACKUP_DATE, PHYSICAL_DEVICE_NAME,A.MEDIA_SET_ID,
SERVER_NAME,DATABASE_NAME,[TYPE]
FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B
ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)
) BACKUPS
GROUP BY [TYPE],SERVER_NAME,DATABASE_NAME
),
BACKUP_ALL AS
(
SELECT ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,
PHYSICAL_DEVICE_NAME
FROM MSDB.DBO.BACKUPSET A JOIN MSDB.DBO.BACKUPMEDIAFAMILY B
ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)
)
SELECT SERVER_NAME [SERVER],DATABASE_NAME [DATABASE],BAKUPTYPE=
CASE WHEN [TYPE]='D' THEN 'FULL'
WHEN [TYPE]='I' THEN 'DIFFERENTIAL'
WHEN [TYPE]='L' THEN 'LOG'
WHEN [TYPE]='F' THEN 'FILE / FILEGROUP'
WHEN [TYPE]='G' THEN 'DIFFERENTIAL FILE'
WHEN [TYPE]='P' THEN 'PARTIAL'
WHEN [TYPE]='Q' THEN 'DIFFERENTIAL PARTIAL'
END,BACKUP_DATE [RECENT BACKUP], PHYSICAL_DEVICE_NAME [LOCATION] FROM BACKUP_RECENT,BACKUP_ALL
WHERE BACKUP_RECENT.ID=BACKUP_ALL.ID AND DATABASE_NAME=@P_DBNAME
ORDER BY BACKUP_DATE
END
END
END
How to use the script
The procedure uses msdb to get the relevent backups you need to look at.
The Procedure can be executed in 3 ways.
1. EXEC USP_DBA_RECENTBACKUPS: This will detail all recent backups of all types (Full, differential and log ) for each database. Eg. If you take only full backups you will find only one record for each server.
2. EXEC USP_DBA_RECENTBACKUPS '<DBNAME>': The same result as above. But here you are ineterested in a single database. It will list all recent backups of all types for the given databse.
3. EXEC USP_DBA_RECENTBACKUPS '<DBNAME>','R': If you are having a simple backup plan of Full, differential and log backups, you can use this syntax to see what you need to restore the database. It will list latest full backup, latest differntial backup, and all the log backups therafter.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 2, 2010 at 6:29 am
Just curious, why would you take a differential immediately after a full? There can be little or no changes between the two.
Basically, if you have a differential that you mean to apply, you need to make sure that you leave the database in a recovering state after the full restore is run, so that you can then run the differential and recover.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2010 at 9:50 am
ok,..seems like i didn't phrase the question right. There is full backup taken on every sunday and daily differential backups and log backups every two hours. Now my question is i have the copy of full backup and differential backup, how can i make sure that diff backup is in sequence with full backup. Basically i am asking what values do i need to match in restore header only to make sure diff backup can be restored after full backup. Btw these are light speed backups
September 3, 2010 at 10:26 am
Any of the diffs after the full will work. I'd think that typically you wanted the latest diff made after the full.
Isn't the backup finish time in the headeronly result?
September 4, 2010 at 6:28 pm
CheckpointLSN of full backup = DatabaseBackupLSN of differential backup ( http://www.sqlbackuprestore.com/logsequencenumbers.htm)
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
September 5, 2010 at 8:59 pm
yup i figured that out by few tests..thanks
September 7, 2010 at 9:31 am
Personally, I wouldn't write scripts that depend on anything other than the backup files themselves; using msdb to figure out which files and which order fails completely if msdb isn't there, or isn't the correct msdb.
Restoring to a different machine for any reason (testing, development, production caught fire and was doused with salt water) also indicates you'll be using a msdb that doesn't have information on the backups you want to restore.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply