June 29, 2021 at 8:26 am
Hi,
Production database : I have a database Backup size is 300 GB from production server.
Total size of hard disk is 1 TR in Production.
Test environment: I want to restore a database in test, not all the tables from backup mdf. , i need only 50 GB data, from the database backup.
Total size of hard disk is 400 GB in test.
I dint need all data from prod data 300 GB, just want to collect 50 GB tables from prod data to test. I didn't need all tables from backup fil and reduce to atlest 50gb in order to adjust size of test machine hard-disk.
Full backup: every day only
Is there any suggestion, how to achieve that.
June 29, 2021 at 8:33 am
There is no easy way. As far as I know, it is not possible to select which objects should be restored from a SQL backup.
You can, of course, restore the entire DB and then drop the objects you don't need. Alternately, some sort of regular ETL process could be implemented.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 29, 2021 at 8:43 am
I would BCP the data out and then BCP it in to the test database. Another option is to use SSIS to import the selected tables. There is also an option (which I don't recommend, but it exists and you should decide about it) which is to try and use piecemeal restore, but I have to admit that I've never tried it and that your database has to be designed in order to use it (for example the tables that you want to restore have to be on their own file separated from the tables that you don't want to restore).
Adi
June 29, 2021 at 9:24 am
Thanks for answer back, #Phil #Parkin
If restore the entire DB, so there is no space in Test Environment - limitation disk space.
Total size of hard disk is 400 GB in test.
Database Backup size is 300 GB from production server.
I only need 50 GB data from backup file. is there any alternative ?
June 29, 2021 at 9:37 am
is there any alternative ?
Alternatives are described by Adi Cohn. An SSIS solution sounds plausible.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 29, 2021 at 10:57 am
Backups are basically all or nothing (although, if you structure your system and have lots of read only data, you can use partial backups and a partial restore). Backups are simply not the mechanism for moving a piece of a database or a subset of data. To do that, you are going to have to explore the different mechanisms of export/import. No real choice there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2021 at 3:07 pm
If you have space on prod, restore it to second DB on prod server, remove unneeded tables/content, then back that up and restore to dev.
July 12, 2021 at 3:33 pm
I think some 3rd party backup tools have Object level restore options. Otherwise, several good options already mentioned above.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply