October 28, 2009 at 3:26 am
Hi Folks,
Is it possible to backup the database between the dates? As we can take differential backup, is it possible to backup the database between two particular dates?
We have a huge data. The data entry operation team produces thousands of records per week. We already have delivered the application and first full backup of 100000 records to client. After that we have delivered 10k of new records to client as first differential backup. We need to deliver the newly entered data on bi-weekly basis. Here after first differential backup, second differential backup contains the data difference since last full backup(with redundant data of first differential backup).
How is it possible to backup only newly entered data in every two weeks and restore that data on client site?
-Vicky
October 28, 2009 at 10:03 am
I'm not sure using differential backups is the best way to keep your client database up to date. It sounds like you want to restore a full backup then take a differential backup every two weeks and apply it to the client database. But the client database won't be usable in between unless you restore with recovery and that would force you to restore a full backup every two weeks, also.
Have you considered using replication or database mirroring to keep the client database in sync? Transactional replication and database mirroring will sync the client database closer to real time while snapshot replication will do that less frequently.
Greg
October 28, 2009 at 12:12 pm
Vicky-854895 (10/28/2009)
How is it possible to backup only newly entered data in every two weeks and restore that data on client site?
Backup/Restore was not designed as a replication tool.
You can easily handle the business requirement via application logic; identify the new rows, put them in a staging table then send the info the way fits you better.
By the way, if you put your staging tables in a separate database you can go back to use backup/restore as your delivery tool 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 28, 2009 at 12:43 pm
Try Identifying the new records with a Datetime Column you have or by maintaining a record in another table let us say Maintenance Table.
Then use SSIS Packages looks at the Maintenance table and then goes to the Original Table and filters out old records based on your conditions.
You got it???
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
October 28, 2009 at 2:43 pm
Hi.
You don't say what the client expects to use the database for but as you are restoring diffs, I am assuming they are accessing this as read only.
If your client is some distance away with a poor network, you could consider log shipping using daily TLog backups. These could be transported over the internet or by courier and the client database updated as and when using restore log ... with norecovery. You can make the database available as read only using restore database ... with standby=<undo file>....
If they are a little closer and/or better connected, you could consider replication, especially via http as this may make firewall changes easier or mirroring.
If you can give a bit more detail about the task, we may be better placed to advise you.
October 28, 2009 at 3:22 pm
You should also be more specific as to what you are trying to ship to your client.
Content of a single table vs content of multiple tables, a filegroup, a full database, ...
SQLserver supports multiple replication scenarios. Books online has good info on that for the different options and usage scenarios. http://msdn.microsoft.com/en-us/library/ms151198%28SQL.90%29.aspx and http://msdn.microsoft.com/en-us/library/ms151182%28SQL.90%29.aspx
SSIS/BCP export - compress - send over - decompress - load using SSIS/BCP is also an option, considered you can handle DRI asynchrone or load in the correct partent/child order.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 30, 2009 at 10:27 am
not sure if you are using... but if using 2008 then you can implement CDC and get only changed data transfer.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply