{"id":1420,"date":"2013-11-08T02:29:45","date_gmt":"2013-11-07T20:29:45","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=1420"},"modified":"2016-01-07T11:31:03","modified_gmt":"2016-01-07T05:31:03","slug":"sql-server-recovery-using-differential-backup","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=1420","title":{"rendered":"SQL Server recovery using differential backup"},"content":{"rendered":"<p>One of my DBAs said while trying to restore differential backup he was getting \u00c2\u00a0:<br \/>\n<em>Fix : Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward<br \/>\n<\/em>and didn&#8217;t know what to do. \u00c2\u00a0I am not sure if anybody else have encountered this, well I certainly have done the same mistake early in my career. I thought I will blog it so next time I can direct my DBA to this post.<\/p>\n<p>Basically there are three type of recovery state for SQL Server :<\/p>\n<p>Restore with recovery \u00c2\u00a0&#8211; restore from backup and rollback uncommitted transaction. No further backups or logs can be applied. The database is available for read\/write operations after the restore is complete.<\/p>\n<p>Restore with no recovery &#8211; \u00c2\u00a0restore from backup, but don&#8217;t rollback anything. Further logs or backups can be applied. The database is left in restoring mode and no read\/write operations can be performed.<\/p>\n<p>Restore with standby \u00c2\u00a0&#8211; restore from backup, but don&#8217;t rollback any transactions, but all the undo information is kept in a file. However the database is available for read-only operations.<\/p>\n<p>In order to restore from a differential backup or restore\/apply transaction log, you need to first do a restore with no recovery of full backup, you need to keep restoring with no recovery option until you restore the last file which you will use restore with recovery.<\/p>\n<p>Here is an example of how you would restore a differential backup :<\/p>\n<p>RESTORE DATABASE \u00c2\u00a0 RAJUDB<br \/>\nFROM DISK =\u00c2\u00a0&#8216;D:\\MSSQL\\BACKUP\\FULL\\RAJUDB_full.bak&#8217;<br \/>\nWITH\u00c2\u00a0NORECOVERY;<\/p>\n<p>RESTORE DATABASE \u00c2\u00a0RAJUDB<br \/>\nFROM DISK =\u00c2\u00a0&#8216;D:\\MSSQL\\BACKUP\\DIFF\\RAJUDB_diff.bak&#8217;<br \/>\nWITH\u00c2\u00a0RECOVERY;<\/p>\n<p>&nbsp;<\/p>\n<p>I hope this helps<\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of my DBAs said while trying to restore differential backup he was getting \u00c2\u00a0: Fix : Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward and didn&#8217;t know what to do. \u00c2\u00a0I am not sure if anybody else have encountered [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[303,358,658],"tags":[871,35,870,869,306],"class_list":["post-1420","post","type-post","status-publish","format-standard","hentry","category-database","category-how-to","category-sql-server-2","tag-msg-3117","tag-restore","tag-restore-differential-backup","tag-restore-with-no-recovery","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1420","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1420"}],"version-history":[{"count":2,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1420\/revisions"}],"predecessor-version":[{"id":1422,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1420\/revisions\/1422"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1420"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1420"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1420"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}