Sql-server – Restoring database with less data than the backup file

backuprestoresql serversql-server-2012

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.

Best Answer

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.

Related Solutions

Sql-server – Errors restoring a differential backup

Here's what I have understood from your question :

Your Full backup taken yesterday failed to restore on your TestingDB. Hence, you took a latest full backup and that got restored successfully. After this you are trying to restore a differential backup from yesterday.

This will not work. Here's the reason it will not work. Everytime a backup happens let it be FULL\DIFF or LOG. it generates an LSN, this LSN is a chain and unique number which matches with each other and are compared at the time of restore.

If you have taken latest FULL backup then now you should take the latest diff backup and try to restore it on TestingDB and you should not get any errors you received earlier. Also, while restoring you can check the LSN of Full and Diff backup files by using RESTOREHEADERONLY command which reads the headers of your .bak file and gives you the information about First and Previous LSN.

I hope this helps in solve your problem.

Sql-server – Restoring a replicated SQL Server database in a non-replicated testing enviroment

You have to just restore the database using

restore database <db_name>
from disk = 'Disk path\db_name.bak'
with recovery, replace -- only if there is already database that you need to overwrite
,stats =10,
move 'logical_data' to 'physical_path.mdf',
move 'logical_log' to 'physical_path.ldf'

once the database is restored, you can remove the replication bit

sp_removedbreplication 'database_name'

Best Answer

Related Solutions

Sql-server – Errors restoring a differential backup

Sql-server – Restoring a replicated SQL Server database in a non-replicated testing enviroment

Related Question