0

We have a SQL Server database with terabytes of files stored in filegroups. In case of a catastrophic failure we restore a backup (includes data and files) which takes hours to completely restore. We would like to find a way to speed up the restore.

It would be ideal if we could get the database up and running quickly without the files. Then we could restore the files at a later time. Is it possible to backup the database without filegroups, restore the database without filegroups, then restore the filegroups?

I have tried scripting out the database with data, executing the script, and then restoring the filegroups but this did not work.

What are some other solutions that we could implement if this will not work?

user2354863
  • 71
  • 5
  • 1
    What sql server edition you are on ?? if it is Sql Server Enterprise Edition then you need to look into [`Online Piecemeal Restores`](http://technet.microsoft.com/en-us/library/ms177425.aspx). It allows you to restore you filegroups piece by piece. as you restore you filegroup they are online and ready to be accessed by users. – M.Ali Feb 18 '14 at 23:15
  • M.Ali, we have the ability to do the piecemeal restore. The issue is we need a way to only backup/restore the database without filegroups as a first step. Do you know if this is possible? – user2354863 Mar 10 '14 at 18:03

1 Answers1

-1

It sounds like you have a small amount of critical data that is required to get the business back up and running, then a large amount of non-critical data that can be restored over a long period of time if need be.

Have you considered splitting this into 2 separate databases?

Another option might be to set up replication on the smaller tables so that you maintain a duplicate database of the critical information. You could restore THAT database in DR, then do a full restore and merge when completed.

I'm not sure if Sql Server has a way to pull individual tables from a backup. That might be an option.

Scottie
  • 11,050
  • 19
  • 68
  • 109
  • Your spot on with the first paragraph. There is a subset of our filegroup with critical files which we would like to restore as soon as possible. We could split this into a separate filegroup, but that would require us to be able to backup only part of our database (and ignore the less critical filegroup). I think it really comes down to knowing if we can backup only part of the database. – user2354863 Feb 18 '14 at 23:04
  • We have considered splitting into 2 separate databases. This would work, but we would like to use this as a last choice because it would require a custom job to move files into the less-critical database once the files are deemed less-critical. – user2354863 Feb 18 '14 at 23:06