September 3, 2014 at 1:11 am
Hi,
My intention is to make a copy of a database with some data excluded. One way to do this:
- Make a copy-only backup of the source database
- Restore the backup as a new database
- Delete undesired data from the new database
In my case, the source database has two file groups, primary and secondary. The secondary has just one table with huge amount of data and this file group appears as one physical file. Is there any way to make a partial backup, which does not include this file group or table, and it would still be possible to restore the backup to a new database? My point is to avoid copying huge amount of data if it won't be needed.
Any help is appreciated.
-Timo
September 3, 2014 at 8:11 am
Yes you can do filegroup backup:
BACKUP DATABASE <your_db>
FILEGROUP = 'primary'
TO DISK = '...'
Alternatively, you can set secondary to read-only, and run this:
BACKUP DATABASE <your_db>
READ_WRITE_FILEGROUP
TO DISK = '...'
September 3, 2014 at 9:07 am
Thanks for the answer. Have you tried if these work in practice?
Restoring a backup with the primary filegroup only leaves the data file of the secondary filegroup RECOVERY PENDING. I suspect there is no easy way to TOTALLY remove this RECOVERY PENDING file and the corresponding filegroup and recreate them. I can remove the physical file but the metadata just remains and indicates DEFUNCT.
The copied database needs to be fully functional and allow filling the secondary filegroup whose content was not copied. It seems that the only solution is to copy on a more fine-grained level.
-Timo
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply