November 17, 2008 at 7:43 am
Here is roughly my backup schedule for my DB server
Full backup 2am
Log Backup 8am to 8pm every 2 hours
Differential Backup at 3pm
To test the backups and have db continuity I restore the full backups to a warm server at 8am each morning
At 4pm I want to apply the Differential backups to the warm server but have been receiving this message
/******/
Date11/12/2008 4:33:19 PM
LogJob History (RESTORE - Step 1 User DBs (Diff))
Step ID52
Server
Job NameRESTORE - Step 1 User DBs (Diff)
Step NameRestore Diff DBSallc
Duration00:00:00
Sql Severity16
Sql Message ID3013
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: [Domain]\[Service Account]. This differential backup cannot be restored because the database has not been restored to the correct earlier state. [SQLSTATE 42000] (Error 3136) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
/*****/
Now if I recall I would get this message only if there was a full backup sometime AFTER 2am and I am trying to apply the older full backup then the diff backup. But the only backup we have on our Databases is a tape backup via Backup Exec at 12:30AM...an hour and a half BEFORE the SQL 2am Full backup.
At a loss on this...here is my script I am running as well..
ALTER DATABASE DBSallc SET single_user WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBSallc from disk =
'E:\SQL Backup\Data\DBSallc.bak' WITH REPLACE,
MOVE 'DBSallc_data' TO 'D:\Data\DBSallc.mdf',
MOVE 'DBSallc_log' TO 'E:\DataLog\DBSallc.ldf',
NORECOVERY
RESTORE DATABASE DBSallc from disk =
'E:\SQL Backup\Diff\DBSallc.bak' WITH REPLACE,
MOVE 'DBSallc_data' TO 'D:\Data\DBSallc.mdf',
MOVE 'DBSallc_log' TO 'E:\DataLog\DBSallc.ldf',
RECOVERY
November 17, 2008 at 9:30 am
Can you post the code for your backups?
November 17, 2008 at 9:53 am
ALTER DATABASE DBSallc SET single_user WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBSallc from disk =
'E:\SQL Backup\Data\DBSallc.bak' WITH REPLACE,
MOVE 'DBSallc_data' TO 'D:\Data\DBSallc.mdf',
MOVE 'DBSallc_log' TO 'E:\DataLog\DBSallc.ldf',
NORECOVERY
RESTORE DATABASE DBSallc from disk =
'E:\SQL Backup\Diff\DBSallc.bak' WITH REPLACE,
MOVE 'DBSallc_data' TO 'D:\Data\DBSallc.mdf',
MOVE 'DBSallc_log' TO 'E:\DataLog\DBSallc.ldf',
RECOVERY
ALTER DATABASE DBSallc SET multi_user WITH ROLLBACK IMMEDIATE
November 17, 2008 at 9:55 am
jsheldon (11/17/2008)
ALTER DATABASE DBSallc SET single_user WITH ROLLBACK IMMEDIATERESTORE DATABASE DBSallc from disk =
'E:\SQL Backup\Data\DBSallc.bak' WITH REPLACE,
MOVE 'DBSallc_data' TO 'D:\Data\DBSallc.mdf',
MOVE 'DBSallc_log' TO 'E:\DataLog\DBSallc.ldf',
NORECOVERY
RESTORE DATABASE DBSallc from disk =
'E:\SQL Backup\Diff\DBSallc.bak' WITH REPLACE,
MOVE 'DBSallc_data' TO 'D:\Data\DBSallc.mdf',
MOVE 'DBSallc_log' TO 'E:\DataLog\DBSallc.ldf',
RECOVERY
ALTER DATABASE DBSallc SET multi_user WITH ROLLBACK IMMEDIATE
Thank you for reposting the restore code. Actually, i want to see the code for the BACKUP.
November 17, 2008 at 9:56 am
Try removing "WITH REPLACE" from the differential restore statement. I think you only need that on the full restore.
Greg
November 17, 2008 at 9:58 am
Sorry here is the backup code
DECLARE
@SQL nvarchar(300),
@date varchar(20)
SET @date = REPLACE(CONVERT(varchar,GETDATE(),101),'/','')
SET @SQL = N'
BACKUP DATABASE DBSallc
TO DISK = ''\\[servername]\SQLBACKUP$\[servername]\Data\DBSallc' + @date + '.bak''
WITH FORMAT
RESTORE VERIFYONLY FROM DISK =N''\\[servername]\SQLBACKUP$\[servername]\Data\DBSallc' + @date + '.bak'''
EXECUTE sp_executesql @SQL
November 17, 2008 at 9:59 am
Greg Charles (11/17/2008)
Try removing "WITH REPLACE" from the differential restore statement. I think you only need that on the full restore.
Doesn't matter. If you use SSMS to script a restore using Full, Differential, and T_Log backups, you will see that SSMS puts a REPLACE on the Differential restore as well as the restore of the full backup file.
November 17, 2008 at 10:04 am
More information to assist
SQL Server 2005 SP2 Standard Edition
Databases are set to 80 compatibility (as per the vendor)
November 17, 2008 at 10:05 am
I notice that the restore code you posted doesn't have date stamps in the files name, but your backup code does.
November 17, 2008 at 10:12 am
I have a script that strips the datetime off the file before restoration. This works fine on the full backup database restore.
Here it is for differential
/* Unzip SmartStream Production Differential databases to warm server */
/* Unzip .BAK backup files loop through each user database */
DECLARE @db_name varchar(20)
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@fetch_status = 0
BEGIN
DECLARE @doscommand1 varchar(1000)
SET @doscommand1 = 'c:\progra~1\winzip\wzunzip "E:\SQL Backup\Diff\ss-diff-'+@db_name+convert(varchar(10),getdate(),112)+'.zip" "E:\SQL Backup\Diff\"'
EXEC master.dbo.xp_cmdshell @doscommand1
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
/* Trim the backup date off of the .BAK backup files */
DECLARE @doscommand2 varchar(1000)
SET @doscommand2 = 'e:\SQLBAT~1\unzip_diff.bat'
EXEC master.dbo.xp_cmdshell @doscommand2
/* Contents of the unzip_diff.bat file.
REM DOS command to strip the backup time off of the .bak file.
REM For example DBSpuen10232008.bak becomes DBSpuen.bak
REM Because of the DOS SETLOCAL ENABLEDELAYEDEXPANSION DOS command it is
REM unsuccessful in xp_cmdshell.
SETLOCAL ENABLEDELAYEDEXPANSION
SET FOLDER=E:\SQLBAC~1\Diff
FOR /F "Tokens=*" %%a in ('dir /a-d /b "%FOLDER%\*.bak"') DO (
Set Filename=%%~na
Set Filename=!Filename:~0,-8!
ECHO Renaming "%FOLDER%\%%a" to "%FOLDER%\!Filename!%%~xa"
REN "%FOLDER%\%%a" "!Filename!%%~xa"
) */
November 17, 2008 at 11:23 am
Can you do the restores manually using the original backup files to see if they work?
November 17, 2008 at 2:01 pm
Ok if I use the raw backup files without trimming the file names I STILL can an error...I am banging my head against the wall on this....
Here is my script I used
ALTER DATABASE DBSwsam SET single_user WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBSwsam from disk =
'E:\SQL Backup\Data\DBSwsam11172008.bak' WITH REPLACE,
MOVE 'DBSwsam_data' TO 'D:\Data\DBSwsam.mdf',
MOVE 'DBSwsam_log' TO 'E:\DataLog\DBSwsam.ldf',
NORECOVERY
RESTORE DATABASE DBSwsam from disk =
'E:\SQL Backup\Diff\DBSwsam11172008.bak' WITH REPLACE,
MOVE 'DBSwsam_data' TO 'D:\Data\DBSwsam.mdf',
MOVE 'DBSwsam_log' TO 'E:\DataLog\DBSwsam.ldf',
RECOVERY
ALTER DATABASE DBSwsam SET multi_user WITH ROLLBACK IMMEDIATE
And here is the error...
Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I will recheck the entire network to make sure no other backup is being done which would invalidate the full backup...
(I tried it with removing the REPLACE and still the same error.)
November 17, 2008 at 2:10 pm
Run this query and see what you find:
select * from msdb.dbo.backupset where database_name = 'DBSwsam' -- use the appropriate DBName here
November 17, 2008 at 2:28 pm
Ok I see the list of backup and see my backup, so I guess what I am looking at is to ensure the full backup I am calling is the last row on this list?
Also going from memory I can probably do a RESTORE .... FROM MEDIA_SET_ID or something like that = 'xx'
if so how would I determine the media set?
November 17, 2008 at 2:42 pm
Look at the Type. Full backups are D differentials are I and T-Logs are L. You want to ensure that your are restoring the lastest full backup. Do a RESTORE HEADERONLY on the backup files you are trying to restore from and compare them to the data in msdb.dbo.backupset.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply