Sql differential backup Restoration

Question

Hi Team

I have full backup on Sunday , differential backups on Monday, Tuesday, Wednesday

I want to make my DB available till Monday for testing some data

I am trying to restore my database on new server

Restored full backup ,restored differential backup took on monday

It throw s an error database cannot be restored to earlier state

How do I make my db available till Monday

Regards

Answer 1

Dont specify the STOPAT parameter.

Answer 2

Hi @Anonymous ,

1.First of all, you must first clarify the file you want to restore and the time point to restore to,you can execute code as next to check all your backup files:

restore headeronly from disk = 'x:\xxx\xxx.bak'    

2.Follow screenshot as next to restore and then click ok, you will successfully finish it.(on my side, I choose overwrite the existing database to have a test, I restore my db at 20200924 10:34:20am )
--test code:

use master  
drop database test1  
create database test1  
use test1  
go  


create table testbak (id int, cname varchar)  

--insert values 10:33  
insert into testbak values(1,'a')  
--full backup at 20200924 10:33am  
backup database test1 to disk = 'C:\testbackupfiles\testbak.bak'  
backup log test1 to disk='C:\testbackupfiles\testbak.bak'  

--insert into values 10:34  
insert into testbak values (2,'b')  

--backup at 10:34 am  
backup database test1 to disk = 'C:\testbackupfiles\testbak.bak' with differential  

--insert into values at 10:35  
insert into testbak values (3,'c')  

--backup at 10:35am  
backup database test1 to disk = 'C:\testbackupfiles\testbak.bak' with differential  

--damage the mdf files and then restore as next

--1.check

--check  
restore headeronly from disk = 'C:\testbackupfiles\testbak.bak'  

--2restore to 10:34am

3.check the resulte (on my side, it only shows the values (1,a) (2,b))

You can also use t_sql to restore it as this doc.: restore-a-sql-server-database-to-a-point-in-time-full-recovery-model

More information: revert-a-database-to-a-point-in-time

BR,
Mia

Answer 3

Hi @Anonymous ,

You can firstly read this blog or test as next workaround: sql-server-fix-error-msg-3136-level-16-state-1-this-differential-backup-cannot-be-restored-because-the-database-has-not-been-restored-to-the-correct-earlier-state

1.First of all, you must first clarify the file you want to restore and the time point to restore to,you can execute code as next to check all your backup files:

restore headeronly from disk = 'x:\xxx\xxx.bak'    

2.Follow screenshot as next to restore and then click ok, you will successfully finish it.(on my side, I choose overwrite the existing database to have a test, I restore my db at 20200924 10:34:20am )
--test code:

use master  
drop database test1  
create database test1  
use test1  
go  


create table testbak (id int, cname varchar)  

--insert values 10:33  
insert into testbak values(1,'a')  
--full backup at 20200924 10:33am  
backup database test1 to disk = 'C:\testbackupfiles\testbak.bak'  
backup log test1 to disk='C:\testbackupfiles\testbak.bak'  

--insert into values 10:34  
insert into testbak values (2,'b')  

--backup at 10:34 am  
backup database test1 to disk = 'C:\testbackupfiles\testbak.bak' with differential  

--insert into values at 10:35  
insert into testbak values (3,'c')  

--backup at 10:35am  
backup database test1 to disk = 'C:\testbackupfiles\testbak.bak' with differential  

--damage the mdf files and then restore as next

--1.check

--check  
restore headeronly from disk = 'C:\testbackupfiles\testbak.bak'  

--2restore to 10:34am

3.check the resulte (on my side, it only shows the values (1,a) (2,b))

You can also use t_sql to restore it as this doc.: https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-sql-server-database-to-a-point-in-time-full-recovery-model?view=sql-server-ver15

More information: https://solutioncenter.apexsql.com/revert-a-database-to-a-point-in-time/

BR,
Mia

Answer 4

To add a bit to my earlier reply. You restore the full backup and the Monday differential backup. Please show is the RESTORE command you are executing if that doesn't work. You can script it if you use the GUI in SSMS to restore. Also the full error messages.

Answer 5

Hi @Anonymous ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.