March 10, 2011 at 8:08 am
Is it possible to backup and restore only few partitions of a database. Say if i partition my data based on date and each month will have a seperate database file in the dataabse, how can i backup only last 4 partitions and restore them.
March 10, 2011 at 8:13 am
Here is the real scenario, Let me know the best possible ways to do.
I do insert data into a dev database (which is a exact copy of production) every month and backup the dev db and restore on production server as a new copy and then drop the existing production.
here is my thoughts..
1. backup only latest months data and restore on production to save time for full back up and full restore.
2. backup differential data (hope it backs up only the datai loaded recently) and restore differential bkp to the current production server.
March 10, 2011 at 8:19 am
Neither is going to work directly. To restore a diff, the database has to have been restore with norecovery and the diff has to be based on the full that was used for the restore. File/filegroup backups have to be made to the same database they were taken from, and there will be log backup requirements.
Suggestion: Restore the dev database (with just the new data), restore to prod and use SSIS.
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
March 10, 2011 at 8:23 am
what do you mean by restore the dev database with just new data.
Dev dataabse is never restored unless there is a problem, which means we keep appending data to Dev database every month and do a full backup and restore on prodcution then change names on the production and drop the old one. This is what we do so that production db has minimal downtime for monthly load.
March 10, 2011 at 8:34 am
i must admit, data moving from dev to prod scares me. That's been a complete no-no in just about every large environment I've worked in. Too much risk of development/test data getting into production
I meant put the new data into a separate database, back that up, restore to prod then use SSIS to get it across. Even if the databases are structurally identical, SQL does not consider them the same and won'l allow piecemeal backup from one, restore to the other.
How long does the monthly load take? What's the window available for 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
March 10, 2011 at 9:40 am
we have about 20 databases to load which are of identical structure and takes for more than a week to finish.
Like you said if i am loading only 1 months worth of data into a seperate database, how do i append that to the existing production server with a very minimal downtime.
March 10, 2011 at 10:30 am
If you have enterprise edition you can have a partitioned table. Very fast to insert into insert into an empty partition, then you can keep partitioned or merge the partition.
A week for a month end process? Nothing optimisable there?
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
March 10, 2011 at 10:42 am
yes all of the database are partioned on date which means each month will have one file. Could you help me more on how i can backup just partiion and merge the same on production database.
Yes, it takes more than week because the data is in terabytes.
March 10, 2011 at 10:57 am
You cannot backup and restore partitions. You can do filegroups, and if that contains just your partition, I believe you can restore that filegroup, then switch in the partition.
The other alternative would be to bcp out/in the data from dev/to production, though I am scared of that, just like Gail.
Typically don't do this from development since someone might inadvertently change something in development. Mostly I would move data through a staging environment on the production server, not using a development instance. Is there a reason you don't want to insert this data into production directly?
March 10, 2011 at 11:05 am
Tara-1044200 (3/10/2011)
yes all of the database are partioned on date which means each month will have one file. Could you help me more on how i can backup just partiion and merge the same on production database.
As I've already said, you cannot backup a file from one DB and restore to a different DB.
Backup the dev database, primary and the filegroup the new data is in (although I would strongly recommend that you not use the dev environment for month end, use another production server)
Restore that as a new DB on the production server.
Create a new partition in the table that the data is going into.
Use bcp or SSIS to transfer the data to the production DB
Optional: merge the partition.
I've worked on TB DBs, they're challenging, but not necessarily that slow. Sure there's no optimisation that can be done there?
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply