Restoring differential backup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Restoring differential backup

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-05-26 : 16:45:31

Hello Guys,

I have two sql server 2005, one for real server and another for testing server.

I created maintenance plans for full database backup, its runs every saturday.
And also i created another maintenance plans for differential database backup. Its runs everyday.

Now, i tried to restore thoes differential backup on testing server,but getting error. I can not restore on my testing server.
Anybody know why?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-05-26 : 17:05:53

Maybe you could post the errors that you're getting when trying to restore?

--
Gail Shaw
SQL Server MVP

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-05-26 : 17:17:36

I got this err:
Restore failed for server "server_name"(Microsoft.SqlServer.Smo)

Additional information:
System.Data.SqlClient.SqlError: The log or differential backup can not be restored because no files are ready to rollforward. (Microsoft.SqlServer.Smo)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-05-27 : 03:08:50

That means that the DB you were restoring to had already been recovered. This is probably because the full backup was restored WITH RECOVERY. To restore a diff backup (or log backups), the DB must either be in the STANDBY or RESTORING state.

Restore the full backup WITH NORECOVERY, then restore the latest differential WITH NORECOVERY, then restore any log backups, in sequence, all with NORECOVERY. Once you're happy that all the necessary backups are restored, bring the database online. RESTORE DATABASE <Database Name> WITH RECOVERY.

Once the database has been recovered, no further backups can be restored.

--
Gail Shaw
SQL Server MVP

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-05-27 : 09:33:57

Thanks GilaMonster,

I am explaning what i did,
First i restored the full backup database on my testing server, then i tried to restore differential database backup, so i got above error...

On my case how to restore full backup then differential backup on testing (different server) sql server?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-05-27 : 10:37:28

I already said how to restore full and then differential

quote:
Restore the full backup WITH NORECOVERY, then restore the latest differential WITH NORECOVERY, then restore any log backups, in sequence, all with NORECOVERY. Once you're happy that all the necessary backups are restored, bring the database online. RESTORE DATABASE <Database Name> WITH RECOVERY.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources