May 12, 2010 at 9:35 pm
Dear All:
We are running an SQL server 2005 with a small database. I am trying to change the backup plan to minimize data loss to an acceptable point.
we are using simple recovery mode. so I created two maintenance plans:
1. Full daily backup
2. Hourly differential backup
the frequency above is just for testing purposes. then I tried to restore the backups as follows:
1. restored the full backup successfully with none recovery
2. attempted to restore the most recent differential backup, when I attempt to ado that I get the following error message:
not sure where I am going wrong. I am doing backups and restores using SQL management studio.
your help is much appreciated.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'MYSERVER'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
May 13, 2010 at 1:09 am
script out the backup and post here.
second what Msg Id, Severity, Error Message you got in error message
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 13, 2010 at 1:30 am
What version\service pack are you using?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 13, 2010 at 2:35 am
Looks like the diff doesn't belong to that full backup. Maybe there was a full backup somewhere in between.
Which of the backups are you trying to restore, in which order?
p.s. Typically to get minimal data loss and point-in-time restore capability, you'd use full backups, maybe diffs and log backups and have the DB in full recovery model.
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
May 13, 2010 at 5:50 pm
here is the cript of the backup jobs:
Full Backup script
BACKUP DATABASE [tass] TO DISK = N'F:\Test\tass_backup_201005140844.bak' WITH NOFORMAT, NOINIT, NAME = N'tass_backup_20100514084448', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'tass' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'tass' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''tass'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'F:\Test\tass_backup_201005140844.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
Diff:
BACKUP DATABASE [tass] TO DISK = N'F:\Test\tass_backup_201005140845.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'tass_backup_20100514084517', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'tass' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'tass' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''tass'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'F:\Test\tass_backup_201005140845.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
May 13, 2010 at 5:52 pm
Full Backup script
BACKUP DATABASE [tass] TO DISK = N'F:\Test\tass_backup_201005140844.bak' WITH NOFORMAT, NOINIT, NAME = N'tass_backup_20100514084448', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'tass' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'tass' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''tass'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'F:\Test\tass_backup_201005140844.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
Diff:
BACKUP DATABASE [tass] TO DISK = N'F:\Test\tass_backup_201005140845.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'tass_backup_20100514084517', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'tass' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'tass' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''tass'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'F:\Test\tass_backup_201005140845.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
May 13, 2010 at 5:55 pm
oops looks like I posted the same thing twice, sorry about thta:-)
May 14, 2010 at 2:15 am
And you're certain there were no other backups made?
What's the restore script that you used?
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
May 14, 2010 at 7:55 am
You could run something like this to look at the backup history and verify the files used for the restore match your backups.
SELECT sysdb.name, bkup.description, bkup.backup_finish_date,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',
ceiling(bkup.backup_size /1048576) as 'Size Meg' ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',
server_name, user_name, sysdb.crdate
,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'
,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
where backup_finish_date > DATEADD(DAY, -30, (getdate())) -- Last 30 days
AND sysdb.name = 'MyDatabase'
ORDER BY sysdb.name, bkup.backup_finish_date desc
May 14, 2010 at 3:40 pm
use the following to ascertain backup sets that have been initaited against the database. Check the output to see if there are any unexpected\manual backups outside of the regime you have in place
use MSDB
select bs.database_name, bs.recovery_model, bmf.physical_device_name, bs.server_name,
bs.backup_start_date, bs.backup_finish_date, bs.backup_set_id, bs.first_lsn,
bs.last_lsn, bs.checkpoint_lsn, bs.database_backup_lsn, bs.type,
cast(convert(Numeric(10,2), (bs.backup_size / 1048576)) as varchar(15)) + ' MB' as Backup_Size,
bs.differential_base_lsn, bf.filegroup_name, bf.file_number, bf.backed_up_page_count, bf.file_type,
cast(convert(Numeric(10,2), (bf.file_size / 1048576)) as varchar(15)) + ' MB' as DBFileSize,
cast(convert(Numeric(10,2), (bf.backup_size / 1048576)) as varchar(15)) + ' MB' as BackupSize
from dbo.backupset bs
inner join dbo.backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
inner join dbo.backupfile bf on bs.backup_set_id = bf.backup_set_id
where bs.database_name = 'yourdb'
order by bs.backup_start_date, bs.backup_finish_date
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 17, 2010 at 7:05 pm
Thanks Guys. i think the problem was another maintenance plan running and doing full backups at a different time, so that makes the differential backup part of a different set.
is there a good resource in plain English 🙂 about SQL backups, restores and how they work?
regards
May 24, 2010 at 6:22 pm
Perry Whittle (5/14/2010)
use the following to ascertain backup sets that have been initaited against the database. Check the output to see if there are any unexpected\manual backups outside of the regime you have in place
use MSDB
select bs.database_name, bs.recovery_model, bmf.physical_device_name, bs.server_name,
bs.backup_start_date, bs.backup_finish_date, bs.backup_set_id, bs.first_lsn,
bs.last_lsn, bs.checkpoint_lsn, bs.database_backup_lsn, bs.type,
cast(convert(Numeric(10,2), (bs.backup_size / 1048576)) as varchar(15)) + ' MB' as Backup_Size,
bs.differential_base_lsn, bf.filegroup_name, bf.file_number, bf.backed_up_page_count, bf.file_type,
cast(convert(Numeric(10,2), (bf.file_size / 1048576)) as varchar(15)) + ' MB' as DBFileSize,
cast(convert(Numeric(10,2), (bf.backup_size / 1048576)) as varchar(15)) + ' MB' as BackupSize
from dbo.backupset bs
inner join dbo.backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
inner join dbo.backupfile bf on bs.backup_set_id = bf.backup_set_id
where bs.database_name = 'yourdb'
order by bs.backup_start_date, bs.backup_finish_date
Hi thanks for the help:
when I ran the above script, I got a whole heap of information some I understand but some I dont. it seems there are other backup jobs happenning in the background, but not sure why they running or the location of the backuped up files. the maintenance plans for these jobs do not exist. please refer to sample of query result:
part one
servFULLF:\TassBackup\TransactionLog\tass_backup_201005241000.trnSQLServer
servFULLF:\TassBackup\TransactionLog\tass_backup_201005241000.trnSQLServer
servFULLF:\TassBackup\TransactionLog\tass_backup_201005241200.trnSQLServer
servFULLF:\TassBackup\TransactionLog\tass_backup_201005241200.trnSQLServer
servFULLF:\TassBackup\TransactionLog\tass_backup_201005241400.trnSQLServer
servFULLF:\TassBackup\TransactionLog\tass_backup_201005241400.trnSQLServer
servFULLF:\TassBackup\TransactionLog\tass_backup_201005241600.trnSQLServer
servFULLF:\TassBackup\TransactionLog\tass_backup_201005241600.trnSQLServer
servFULL{CF7B9AF2-6610-4412-A8CD-D01B04A895EE}3SQLSERVER
servFULL{CF7B9AF2-6610-4412-A8CD-D01B04A895EE}3SQLSERVER
servFULL{13688B9A-585F-41FF-A598-B9FDF4483108}3SQLSERVER
servFULL{13688B9A-585F-41FF-A598-B9FDF4483108}3SQLSERVER
servFULLF:\TassBackup\Differential\tass_backup_201005250700.bakSQLSERVER
servFULLF:\TassBackup\Differential\tass_backup_201005250700.bakSQLSERVER
Part 2
24/05/2010 10:0024/05/2010 10:001716LD242.00 MB0.00 MB
24/05/2010 10:0024/05/2010 10:001716LL1310.25 MB2.11 MB
24/05/2010 12:0024/05/2010 12:001717LD242.00 MB0.00 MB
24/05/2010 12:0024/05/2010 12:001717LL1310.25 MB0.40 MB
24/05/2010 14:0024/05/2010 14:001718LD242.00 MB0.00 MB
24/05/2010 14:0024/05/2010 14:001718LL1310.25 MB0.59 MB
24/05/2010 16:0024/05/2010 16:001719LD242.00 MB0.00 MB
24/05/2010 16:0024/05/2010 16:001719LL1310.25 MB0.25 MB
24/05/2010 22:0024/05/2010 22:001723DD242.00 MB241.50 MB
24/05/2010 22:0024/05/2010 22:001723DL1310.25 MB0.00 MB
24/05/2010 23:0024/05/2010 23:001725DD242.00 MB241.50 MB
24/05/2010 23:0024/05/2010 23:001725DL1310.25 MB0.00 MB
25/05/2010 7:0025/05/2010 7:001728ID242.00 MB1.69 MB
25/05/2010 7:0025/05/2010 7:001728IL1310.25 MB0.01 MB
May 25, 2010 at 10:03 am
Maybe the network admin has setup BackupExec jobs backing up databases directly to tape.
You could add [font="Courier New"]bs.user_name [/font]to your query and see what it shows for those entries.
May 25, 2010 at 10:47 am
saghbash (5/24/2010)
tassFULL{CF7B9AF2-6610-4412-A8CD-D01B04A895EE}3ISVS-SQL\TASStassFULL{CF7B9AF2-6610-4412-A8CD-D01B04A895EE}3ISVS-SQL\TASS
tassFULL{13688B9A-585F-41FF-A598-B9FDF4483108}3ISVS-SQL\TASS
tassFULL{13688B9A-585F-41FF-A598-B9FDF4483108}3ISVS-SQL\TASS
Hi check the sys.backup_devices table for these and get the physical filenames.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 26, 2010 at 7:43 pm
Hi:
We do use Symantec Backup Exec, however we don't have an agent for SQL backup. The system admin is only backing up backup files created by SQL on backup tape. Yesterday I tried something different.
I did a manual full backup in the morning and one differential in the afternoon. the restore worked without any problems.
this morning I tried to restore using the full backup i created manualy, with the differential that is created by a maintenance plan this morning but received this message:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'MYSERVER'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
It is weird, because there are no backups in between a part from the log backup.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply