April 24, 2017 at 4:01 am
Hi,
i want to ask something regarding DB backup.Can we take backup of database backup between 2 dates and can restore it without replacing the existing data.
I think it is database partitioning. how i can do it. if someone have this script please share with.
Thnaks in advance.
April 24, 2017 at 4:10 am
I'm not sure what you actually mean, although I suspect that the answer is going to be no. Please will you describe your exact scenario, and we'll see whether we can help.
John
April 24, 2017 at 4:12 am
You mean a backup that only contains data between when it's taken and some previous point in time?
That's a differential backup, but it won't do what you want, as restoring it requires that the full backup that the diff is based on be restored first.
In short, there's no easy way to do what you want. You'd need to export the data to files, and then figure out an import process that handles various types of conflicts that you can get syncing data from two sources.
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
April 24, 2017 at 5:23 am
I am working on desktop application using .net framework and sql server as database. It will be installed on different system and places. Data will be monthly exported to office and office guy will restore the backup file. This is requirement. Right now i am taking backup of database, it is full DB backup and when restoring the backup to website it replace all the previous existing data. So i want when I will restore the backup, it will affect the old/previous data (i.e. data come from other places/system and previous restores backup.
April 24, 2017 at 5:30 am
As Gail said, there's no easy way of doing that. I would recommend that you restore your backup alongside the existing destination database and then do some ETL to get the required data into it (the destination).
John
April 24, 2017 at 5:46 am
No easy way to do that, and you will have to take into account all sorts of conflict problems when you do it.
What happens if a row was deleted in one place and updated in a different place. Which has priority?
What happens if two places have created a row with the same value for the unique constraint? Which one takes precedence?
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
April 24, 2017 at 5:48 am
backup and restore replaces the entire database(meaning all existing data) with the new backup; since you are saying replace a range of data, from what you are describing, that's not what you want.
I think you really are looking to import data from files, and insert /update that data into the appropriate tables in the database.
For example, you could have your application download a file that is published on your site import the file into a Staging or temp table,and perform a MERGE statement with the data.
a regularly scheduled SSIS package could do the same thing as well; it's just where you want the process to reside.
the details would be the most important thing to really get any help or suggestions, so the more detail you can provide, the better advice we can offer.
Lowell
April 24, 2017 at 8:37 am
saklanivijay87 - Monday, April 24, 2017 5:23 AMI am working on desktop application using .net framework and sql server as database. It will be installed on different system and places. Data will be monthly exported to office and office guy will restore the backup file. This is requirement. Right now i am taking backup of database, it is full DB backup and when restoring the backup to website it replace all the previous existing data. So i want when I will restore the backup, it will affect the old/previous data (i.e. data come from other places/system and previous restores backup.
How many tables does this affect?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2017 at 10:35 pm
2-3 tables.
April 25, 2017 at 8:05 am
saklanivijay87 - Monday, April 24, 2017 10:35 PM2-3 tables.
Then it is possible and maybe relatively "easy" to do what you want and I've done similar many times. There are two tools that could be used to do this.
Partitioned Tables
The first is "Partitioned Tables" and you could backup and restore of a month's worth of data in a separate temporary "transfer" database. To make a longer story shorter, the 2-3 tables would be partitioned by month and copies of those partitions would be save to a "transfer" database and backed up. On the receiving end, they'd do a restore of the "transfer" database, copy the "partition data" into tables in the "target" database, and then be very quickly switched into the final "target" tables. Of course, it could all be automated. With SQL Server 2008, it would require the Enterprise Edition of SQL Server.
Partitioned Tables are a bit complex compared to other solutions but may be worth it. You need to study it well before even thinking of using them because there are caveats.
Partitioned Views
"Partitioned Views" work in just about any edition of SQL Server except "CE". This may be a simpler solution because you'd just put the monthly temporal tables in 1 similarly named database that has the year and month in the form of YYYYMM (uber important to automate this in a simple fashion). Each month, you'd just send a backup for the monthly database and they'd do a restore and execute a simple piece of code to update the "Partitioned View". Partitioned views can only use (if I recall the value correct) 253 such database but 253 months is well over 20 year.
The disadvantage to some is that it requires one database per time period (month, in this case) and does clutter up the Explorer window but, if you name them start with something like a "Z", can be driven to the bottom of the database list so that people don't have to scroll through them when trying to do "normal" work.
Caveats and Benefits of Both
Both Partitioned Tables and Partitioned Views each have their own caveats and MUST be studied and well planned for before deciding to use one or the other. An important caveat for both is the fact that, except in very rare cases, partitioning will slow all code that reference the affected table(s) down a bit (sometimes relatively a lot) even when well written. Another is the fact that you probably should NOT have FKs that point at the table because the partitioning column will be added to the PK (and any other "unique" index), which would make the PK/other unique index no longer truly unique even though the unique constraint on the index could still be applied.
There are a ton of caveats to each method. For example, for Partitioned Tables, the wonderful "SWITCH" method to nearly instantly move a whole partition into the "target" table won't work unless ALL the indexes are "aligned" with the partitions, which would negate what we're trying to do to begin with. In order for Partitioned Views to be insertable, you have to insert all columns. If you have an IDENTITY column on the table, that messes things up and you need to do a work around in the form of "Instead Of" triggers or use a "Sequence" instead, Since there is no "SEQUENCE" object in 2008, that can complicate things, possibly becoming the source of many deadlocks.
There are some HUGE benefits to be had if the older months of data are static in nature. For either method, if you make it so that each month of data lives in it's own file and filegroup, you can set the older filegroups to "Read_Only" and stop backing up that which will never change. Of course, you also have to make sure you're not setting a whole bunch of free space to "Read_Only" but that monthly fire-drill is worth it. My 10 hour backups dropped to < 10 minutes because I stopped backing up the old months and only backed up the current month (and empty "next month" in place for automatic roll overs). Backup the Read_Only file groups just once (make sure they can actually be restored, of course) and then never back them up again (although you MUST keep them available). Partitioned Views using 1 database per month has some serious advantages over Parititioned Tables in that area. Of course, setting a partition to "Read_Only" also has the benefit of not needing ANY index maintenance nor having to worry about updating statistics.
So, yes... what you ask for IS possible but it's going to take some good understanding of the methods and their caveats and then it will require a conversion to the method. Once done, though, you can put it in a "set it and forget" mode and can have some huge benefits for nightly maintenance tasks, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply