July 22, 2009 at 6:19 pm
full backup: every Sunday.
Diff Backups: Mon to Sat in the evenning.
I've made 3 jobs for the transactional DB:
1. for Full backup which runs every Sunday and overwrites the existing file. (file name: Adventureworks.bak )
2. Diff Backup MWF. Takes the differential backup for Mon, Wed & Friday. Supposed to overwrite every other day. (file name: Adventureworks_1.diff)
3. Diff Backup TThSa. Takes the differential backup for Tue, Thur & Sat. Supposed to overwrite the existing file every other day. (file name: Adventureworkd_2.bak)
I've made 2 jobs for the Reporting DB:
1. Restore MWF
2. Restore TThSa
(no restore on Sunday)
I can't change the backup/restore strategy. I don't like it myself for the reporting DB but can't change it, it is beyond my control.
I can restore fine on Monday using,
Step 1
Restore database adventureworks_rpt from disk = 'xxxxxxxx\Adventureworks.bak'
with norecovery, stats,
move 'data' to '.mdf',
move 'log' to '.ldf'
go
Step 2
Restore database adventureworks_rpt from disk = 'xxxxxxxx\Adventureworks_1.diff'
with recovery, stats,
move 'data' to '.mdf',
move 'log' to '.ldf'
go
it works fine on Monday but on Wednesday, when file 'Adventureworks_1.diff' is overwritten, it doesn't work & throws the error.
Hovever, if I create a new file everyday, the process works just fine.
Can a differential Backup file be overwritten?
Any suggestions to make it work automatically? tired of manual inntervention every night
thanks...
July 22, 2009 at 6:53 pm
What's the actual error message?
July 23, 2009 at 12:26 pm
Once you restore your database with recovery, you cannot restore any additional backups. You have to restore the full database with norecovery, then you can apply the appropriate differential.
What is the error that you are getting?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 23, 2009 at 3:05 pm
Thanks for you replies.
I apply the full backup with "norecovery" and apply Differential backup with "recovery". There is no requirement to apply transaction log backups.
Executed as user: TDS\clusadminspain. Cannot apply the backup on device '\\path.filename_2.diff' to database adventureworks_rpt'. [SQLSTATE 42000] (Error 3136) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
My question is:
A. Can a differential backup file be overwritten?
B. Supposing a differential backup file be over written, if I take a full backup on Sunday, but continue to overwrite the existing Differential backup file (without deleting it), would that work?
July 23, 2009 at 3:18 pm
jay (7/23/2009)
Thanks for you replies.I apply the full backup with "norecovery" and apply Differential backup with "recovery". There is no requirement to apply transaction log backups.
Executed as user: TDS\clusadminspain. Cannot apply the backup on device '\\path.filename_2.diff' to database adventureworks_rpt'. [SQLSTATE 42000] (Error 3136) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
My question is:
A. Can a differential backup file be overwritten?
B. Supposing a differential backup file be over written, if I take a full backup on Sunday, but continue to overwrite the existing Differential backup file (without deleting it), would that work?
Once you have issued a restore with recovery you cannot continue restoring any additional backups. So, the second time you issue the RESTORE BACKUP to use the next differential - it cannot be applied because you have already 'recovered' the database.
Answers to your questions:
A: Yes, any file can be overwritten - especially if you use the INIT option in your backup command.
B: No, that will not work because, again, once you 'recover' the database you can't restore any more backups. You would have to restore from the full - and then restore from the new differential.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 25, 2009 at 3:44 pm
jay (7/23/2009)
Thanks for you replies.I apply the full backup with "norecovery" and apply Differential backup with "recovery". There is no requirement to apply transaction log backups.
Executed as user: TDS\clusadminspain. Cannot apply the backup on device '\\path.filename_2.diff' to database adventureworks_rpt'. [SQLSTATE 42000] (Error 3136) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
My question is:
A. Can a differential backup file be overwritten?
B. Supposing a differential backup file be over written, if I take a full backup on Sunday, but continue to overwrite the existing Differential backup file (without deleting it), would that work?
A. Yes. Your backup command should look something like this:
backup database 'database_name' to disk = 'c:\backups\database_name_diff.bak' with differential, init
As was mentioned in the previous reply, you need the "INIT" to have it overwrite the bak file.
B. Yes. Your overall strategy of having the two separate backup files in the every other day rotation sounds like a decent plan. If you add "INIT" you should be set.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply