snippetsqlMinor
how to restore differential back daily basis on sql server DB
Viewed 0 times
basissqlbackdifferentialhowserverrestoredaily
Problem
Im working with SQL Server DB, what i need is that i have a DB1 and clients site communicate with this DB,Reporting module of the application is also using the same DB (i-e That shows different reports in web pages) client want to point the reporting instance to another DB i-e DB2.
What we need to do is: create a new DB2 i-e copy of DB1 and on daily basis we will take the differential backup of DB1 and will restore it to DB2.
For this i tried to take the differential backup of DB1 using this query,
and to restore it to DB2 i use
it runs perfect for the 1st time but next time if gives me error:
Please also advise if i can use Transaction Log backup if that is more batter then differential back up.
Can any one help me to solve this issue.
Thanks in advance.
What we need to do is: create a new DB2 i-e copy of DB1 and on daily basis we will take the differential backup of DB1 and will restore it to DB2.
For this i tried to take the differential backup of DB1 using this query,
BACKUP DATABASE [DB1] TO DISK = N'D:\DB_location\DB1-Diff.bak'
WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'DB1-Differential Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GOand to restore it to DB2 i use
RESTORE DATABASE [DB2] FROM DISK = N'D:\DB_location\DB1-Diff.bak'
WITH FILE = 1, norecovery, NOUNLOAD, STATS = 10
GOit runs perfect for the 1st time but next time if gives me error:
Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.Please also advise if i can use Transaction Log backup if that is more batter then differential back up.
Can any one help me to solve this issue.
Thanks in advance.
Solution
This error happens when Full back up is not restored before attempting to restore differential backup or full backup is restored with WITH RECOVERY option. Make sure database is not in operational conditional when differential backup is attempted to be restored.
Please have a look on the below blog.
http://blog.sqlauthority.com/2010/03/21/sql-server-fix-error-3117-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-to-rollforward/
Please have a look on the below blog.
http://blog.sqlauthority.com/2010/03/21/sql-server-fix-error-3117-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-to-rollforward/
Context
StackExchange Database Administrators Q#72144, answer score: 3
Revisions (0)
No revisions yet.