November 8, 2016 at 5:07 am
Morning All,
I have a database, with a filegroup that is 2TB of documents (blob data).
I frequently have restore this database elsewhere, for various reasons that are mostly sub-optimal, but unfortunately for the moment there is no getting around that. I am somewhat stuck with an 'architect' who is stuck in his ways. I digress.
Out of this database I really only need 300GB of data.
Is there a way to backup the file groups excluding the document one, and replace the missing documents one with an empty/trimmed/static version I keep for this purpose?
So ultimately, I want to restore the filegroups I want, and a replacement one, and bring it all online... When I write it like that, it sounds a really rather silly ask and I think I might know the answer 😉 But I am asking any way in case there is some magic trick I Am missing.
Cheers All,
Alex
November 8, 2016 at 5:16 am
alex.sqldba (11/8/2016)
Is there a way to backup the file groups excluding the document one, and replace the missing documents one with an empty/trimmed/static version I keep for this purpose?
No.
You can restore all the filegroups other than the documents one, but you can't mix and match filegroups in restores, it MUST match the database its been restored to and it must either be taken at the same time as the backup used for the rest of the database, or be followed by restores of log backups to bring all the filegroups to the same point in time.
What you're going to have to do is:
Backup DB
Restore somewhere with space
Truncate the massive tables
Shrink the files in that filegroup
Backup the, now smaller, DB for restore elsewhere.
It's not hard to automate that process to say run every week somewhere. That lets you test your backups and have a small copy ready for anyone who needs it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2016 at 7:41 am
Hi Gail
Thanks. Yeah that process you describe is more or less how it currently is done. One of the downsides are that its not particularly fast, the location with storage is pretty slow and in another datacentre, so its a slow copy/restore followed by sub-optimal delete and truncate and shrink.
Never mind. They will just have to put their hands in their pockets if they want it to work quicker.
Cheers though!
Alex
November 8, 2016 at 7:46 am
I just had a thought actually! Before I'll get around to testing it I wonder what you guys think:
What if I were to create a snapshot of the database, lets call it SS_MyDatabase, within that SS can I truncate the table that I do not want to copy, and take a backup of that SS.
I can test this but its going to be another 40 hours until I'll have a test DB to do this on.
Cheers All,
Alex
November 8, 2016 at 8:35 am
I don't think that it will work. You can't delete data from a snapshot. The snapshot database is a read only database.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 8, 2016 at 9:08 am
Damn. I did not realise that about Snapshots!
November 8, 2016 at 9:29 am
I think some third party backup solutions, like Redgate, allow you to restore specific objects (tables etc) from a backup.
If you can afford the license cost, it might be worth investigating if one of the third party products allows you to restore all except.
November 8, 2016 at 9:40 am
You can also have a look at piecemeal backup and restore that lets you backup or restore specific files and/or file groups. I have to admit that I've never done it on a real system, but look it up and read about it. Maybe it is what you are looking for.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 8, 2016 at 12:36 pm
alex.sqldba (11/8/2016)
What if I were to create a snapshot of the database, lets call it SS_MyDatabase, within that SS can I truncate the table that I do not want to copy, and take a backup of that SS.
Snapshots are read only and can't be backed up.
They're not full databases, just collections of the 'original version' of pages changed in the source database that gives them the appearance of being a point in time copy of the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2016 at 6:14 am
alex.sqldba (11/8/2016)
Morning All,I have a database, with a filegroup that is 2TB of documents (blob data).
I frequently have restore this database elsewhere, for various reasons that are mostly sub-optimal, but unfortunately for the moment there is no getting around that. I am somewhat stuck with an 'architect' who is stuck in his ways. I digress.
Out of this database I really only need 300GB of data.
Is there a way to backup the file groups excluding the document one, and replace the missing documents one with an empty/trimmed/static version I keep for this purpose?
So ultimately, I want to restore the filegroups I want, and a replacement one, and bring it all online... When I write it like that, it sounds a really rather silly ask and I think I might know the answer 😉 But I am asking any way in case there is some magic trick I Am missing.
Cheers All,
Alex
what you're asking here would require a re architecture of your current database if its not already split into filegroups. You seem to be referring to piecemeal restores too which are an enterprise feature, if all of this it to satisfy the requirement that you only want 1 table of 300GB data then purchase Litespeed or sql backup which can provide object level restores or restore the database and drop unwanted objects.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply