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,
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
and 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
it 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.