2

I'm not really experienced with DBA and SQL, so I need some recommendations from you.

I have a production database backup file, however it is very large, so when I'm executing restore, for example, making a copy of data in testing environment - it takes ages to restore.

I read this: Is a partial restore of data from backup possible?

It is similar to what I want to do, but not exactly equivalent.

To be clear, I need an answer to the following question: is there any possibility to restore database with fewer records than in the backup file?

For instance, if the backup file table contains 1 million records, I just want to restore only 1k records.

  • A couple of things to check to help the restore go faster. On the destination, is "Instant File Initialization" enabled (check the SQL Server errorlog for that phrase). Also, is the log file (.ldf) very large compared to the data file(s) (.mdf, .ndf). This is all about how fast SQL can write the pages. Tony's answer is a valid one :) – Kevin3NF Jan 11 at 13:54
5

No, it is not possible to only restore n number of rows from a given table. The restore process does not even know about the tables, let alone how many rows are in each table, as it works on the database page level.

To accomplish your goal of only getting n number of rows from production to non-production without doing a full restore you'll probably want to set up an ETL process.

2

is there any possibility to restore database with fewer records then it is in backup file

This is possible only when your db is organized in special mode: it should use partitioned tables and different partitions should reside in different filegroups.

In this case you can use Piecemeal Restores and restore only Primary and some other filegroup.

If for example, your tables are partitioned by date and every month resides on its own FG, you can restore only PRIMARY and FG201901 and you'll get only the data regarding 2019-01. Other files will not be created at all even if restored database will still aware of other filegroups that will remain offline, but your tables will be queryable unless you try to access data regarding prior periods of time.

This way you can achieve "only some rows from certain tables" after restore.

Alternatively you can use no partitioning but place your tables on different FG. This way you can restore only a subset of original tables.

  • This is correct, but OP should be note that this is a fairly advanced DBA operation, requiring substantial structural changes to the production database. So not a simple fix, by any means. – BradC Jan 11 at 15:21
  • I tried to answer a general question, how it's possible to restore less rows that the backup contains. It certainely should not be done in OP's case – sepupic Jan 11 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.