---
title: Efficient, partial, point-in-time database restores
description: Efficient, partial, point-in-time database restores
image: https://blog.coeo.com/hubfs/Imported_Blog_Media/dboverview_thumb-2.jpg
---

[![](https://www.coeo.com/wp-content/themes/coeo/images/logo.svg)](https://blog.coeo.com/)

+44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com) | [Client portal login](https://my.coeo.com)

# Efficient, partial, point-in-time database restores

# The Coeo Blog

![Gavin Payne](https://blog.coeo.com/hubfs/Profile%20photos/GavinCircle.jpg)

This article is about a situation that many of us could describe the theoretical approach to solving, but then struggle to understand why SQL Server wasn’t following that theoretical approach when you tried it for real.

Earlier this week, I had a client ask about the best way to perform:

- a partial database restore, 1 of 1300 filegroups;
- to a specific point in time;
- using a differential backup, and therefore;
- without restoring each transaction log backup taken since the full backup.
-  
  
  The last point might sound un-necessary because you’re restoring a differential backup, but the restore script originally being used meant SQL Server still wanted every transaction log since the full backup restored.  This article explains the background to the situation, the successful restore commands, and identifies what was causing every transaction log to need to be restored.
  
  For this article, let’s imagine we have a database of the following configuration:[![DBoverview](https://blog.coeo.com/hs-fs/hubfs/Imported_Blog_Media/dboverview_thumb-2.jpg?width=569&height=143&name=dboverview_thumb-2.jpg "DBoverview")](https://blog.coeo.com/hubfs/Imported_Blog_Media/dboverview-4.jpg)
  
  And, let’s imagine it has the following backup schedule:[![Backupoverview](https://blog.coeo.com/hs-fs/hubfs/Imported_Blog_Media/backupoverview_thumb-2.jpg?width=569&height=296&name=backupoverview_thumb-2.jpg "Backupoverview")](https://blog.coeo.com/hubfs/Imported_Blog_Media/backupoverview-2.jpg)
  
  Then, let’s assume we want to restore fgTwo to the point in time labelled above in order to recover data from a table it stores.  Performing a partial database restore which will be required also requires the primary filegroup to be restored so SQL Server will automatically restore it making the future database look like the following:
  
  [![DBoverview2](https://blog.coeo.com/hs-fs/hubfs/Imported_Blog_Media/dboverview2_thumb-2.jpg?width=351&height=111&name=dboverview2_thumb-2.jpg "DBoverview2")](https://blog.coeo.com/hubfs/Imported_Blog_Media/dboverview2-2.jpg)
  
  You’d expect the path to restore fgTwo to look like the path on the left of the diagram below, but for some reason the client was being forced to perform the restore steps on the right, despite their T-SQL restore commands appearing to follow the syntax in Books Online.
  
  [![Restoreoverview](https://blog.coeo.com/hs-fs/hubfs/Imported_Blog_Media/restoreoverview_thumb-2.jpg?width=442&height=250&name=restoreoverview_thumb-2.jpg "Restoreoverview")](https://blog.coeo.com/hubfs/Imported_Blog_Media/restoreoverview-2.jpg)
  
  To show how to restore the filegroup using the path on the left, and highlight what was causing the path on the right to be required, I’ll use a series of T-SQL restore commands.
  
  This first command restores the parts of the database we’re interested in from the full backup file, labelled “A” in our timeline, and the two most important parameters shown in green are what turns a complete database restore into a partial filegroup restore.
  
  restore  
   database partial2  
  **filegroup=’primary’, filegroup=’fgTwo’**  
  from disk = ‘c:\\parttest\\partial1\_FULL\_A.bak’  
   with norecovery, **partial**, replace,  
   move ‘primary’ to ‘c:\\parttest\\priamry.mdf’,  
   move ‘primary\_log’ to ‘c:\\parttest\\primary\_log.ldf’,  
   move ‘fTwo’ to ‘c:\\parttest\\fTwo.ndf’
  
  The next command restores from the differential backup, labelled “B” in our timeline, and means we don’t need to restore transaction logs A1 and A2.  In the client’s real-world scenario they actually had 120+ transaction logs between the full and differential backups, hence the desire not to have to restore each of them.
  
  It was this command that was the cause of them having to restore every transaction log since the full backup, the crossed out parameters in purple were what was used and causing it to happen.  ***Telling SQL Server again which filegroups to restore was forcing it to need to restore the entire log chain since the full backup.***
  
  restore  
   database partial2  
  **filegroup=’fgTwo’**  
   from disk = ‘c:\\parttest\\partial1\_DIFF\_B.bak’  
   with norecovery,  
   move ‘primary’ to ‘c:\\parttest\\priamry.mdf’,  
   move ‘primary\_log’ to ‘c:\\parttest\\primary\_log.ldf’,  
   move ‘fTwo’ to ‘c:\\parttest\\fTwo.ndf’
  
   
  
  Finally, with that step performed, you can then restore the subsequent transaction logs from after the differential backup with a relevant STOPAT parameter:
  
  restore  
   log partial2  
   from disk = ‘c:\\parttest\\partial1\_Log\_B1.trn’  
   with norecovery, stopat=’2012-05-29 15:13:12.750′
  
   
  
  restore  
   log partial2  
   from disk = ‘c:\\parttest\\partial1\_Log\_B2.trn’  
   with norecovery, stopat=’2012-05-29 15:13:12.750′
  
  restore database partial2 with recovery
  
  At this point, the database is recovered with just the primary and fgTwo filegroups online, you can look in sys.master\_files to see the state of all of the database’s data files and which are currently online.
  
  In summary, this article showed how to perform a partial database restore, and how you can easily have to restore more than you were expecting to due to a simple “over-clarification” in a restore command.
  
  I have a complete demo script of a more thorough test available for download from [here](http://gavinpayneuk.files.wordpress.com/2012/06/partialdemo.pdf).
  
  ![](https://blog.coeo.com/hs-fs/hubfs/Imported_Blog_Media/b-76.gif?width=1&height=1&name=b-76.gif)

### Subscribe to Email Updates

## Related posts

---

### [Troubleshooting Ola Hallengren’s Maintenance Solution](https://blog.coeo.com/troubleshooting-ola-hallengrens-maintenance-solution)

### [Domain-Independent Windows Failover Cluster for SQL Server AlwaysOn Availability Group](https://blog.coeo.com/domain-independent-windows-failover-cluster-for-sql-server-alwayson-availability-group)

### [SQLBits 2022 session - Field Testing Ola Hallengren’s Maintenance Solution](https://blog.coeo.com/sqlbits-2022-session-field-testing-ola-hallengrens-maintenance-solution)

### [Windows Active Directory Detached Cluster with SQL Server Basic Availability Group](https://blog.coeo.com/advanced-sql-server-windows-cluster-options)

![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png)

+44 (0)20 3051 3595 | info@coeo.com

## Contact Us

By clicking submit below, you consent to allow Coeo to store and process the personal information submitted above to provide you the content requested.

You may unsubscribe from these communications at any time. For more information on how to unsubscribe and our commitment to your privacy, please review our **[Privacy Policy](https://www.coeo.com/privacy/)**.

## Upcoming Events

[See all events](https://www.coeo.com/events/)

#### NOW Building, Thames Valley Park Drive, Reading, RG6 1RB

[![](https://www.coeo.com/wp-content/themes/coeo/images/social-glass.png)](https://www.glassdoor.co.uk/Overview/Working-at-Coeo-EI_IE959052.11,15.htm)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-in.png)](https://www.linkedin.com/company/coeo-ltd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-twitter.png)](https://twitter.com/CoeoLtd)[![](https://www.coeo.com/wp-content/themes/coeo/images/social-fb.png)](https://www.facebook.com/coeoltd/)

![](https://www.coeo.com/wp-content/themes/coeo/images//menu-icon.png)

![](https://www.coeo.com/wp-content/themes/coeo/images//menu-close.png)

![](https://www.coeo.com/wp-content/uploads/2016/12/logo-invert.png)

+44 (0)20 3051 3595 | [info@coeo.com](mailto:info@coeo.com)

- [Solutions](https://www.coeo.com/solutions/)
- [Next Steps](https://www.coeo.com/next-steps/)
- [Dedicated Support](https://www.coeo.com/dedicated-support/)
- [Case studies](https://www.coeo.com/case-studies/)
- [Technologies](https://www.coeo.com/solutions/technologies/)

- [Industries](https://www.coeo.com/industries/)
- [Finance](https://www.coeo.com/industries/finance/)
- [Retail](https://www.coeo.com/industries/retail/)
- [Technology](https://www.coeo.com/industries/technology/)

- [The Team](https://www.coeo.com/people/)
- [Join Us](https://www.coeo.com/careers/)
- [Graduate Programme](https://www.coeo.com/graduate-programme/)

- [About Coeo](https://www.coeo.com/about-coeo/)
- [The Coeo Blog](https://www.coeo.com/blog/)
- [Contact us](https://www.coeo.com/contact-us/)
- [Privacy Notice](https://www.coeo.com/privacy/)
- [Cookie Policy](https://www.coeo.com/privacy#Cookie_Policy)

- [Events](https://www.coeo.com/events)

Sign up to our newsletter ![go arrow](https://www.coeo.com/wp-content/themes/coeo/images/newsletter-go.png)

 Back to top