Avatar of Vadim Rapp
Vadim Rapp
Flag for United States of America asked on

Restore only meaningful part of the database - possible?

We have a database on SQL Server 2012 (soon to be upgraded to 2019), 52GB. Every now and then I have to restore the past state of it to the "sandbox" database for various research, to find out what was the data at that moment several days ago, or to try something, and so on. It takes 10-15 minutes. I know that only a relatively small fraction of the tables are "of interest", i.e. hold the data that actually matters for the task at hand. So I'm wondering, is there a way to restore only that "meaningful" part of the database, and thus to do it much faster?
Microsoft SQL Server

Avatar of undefined
Last Comment
Priyanka

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Kyle Abrahams

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jim Horn

SQL Server database restores are an all or nothing deal-i-o.

Adding on to Kyle's excellent workarounds above, by definition SQL Server does not know what's meaningful and what's not, so I'd start flushing out requirements exactly what 'relatively small fraction of the tables are "of interest" ' means, and design a solution that accommodates these needs.  Make people define it and go from there. 
Vadim Rapp

ASKER
>Is it possible to keep the "sandbox" in sync?

No, the goal is always to restore it to the certain point in time in the past, to see what was the data back then.
Vadim Rapp

ASKER
>SQL Server does not know what's meaningful and what's not,
 
But I do. What if I place "important" tables in one filegroup, and then restore only that filegroup? is it possible?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Kyle Abrahams

You can move different tables into different file groups.  Note that you would have to recreate any clustered indexes.

Pretty indepth guide here on moving the tables
    https://www.mssqltips.com/sqlservertip/5832/move-sql-server-tables-to-different-filegroups/

and on backing up the file groups here:
   https://www.mssqltips.com/sqlservertutorial/17/sql-server-filegroup-backups/
Vadim Rapp

ASKER
So if I do, can I then restore only one file group and work on this database?
Priyanka

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.