May 10, 2010 at 12:32 am
Hi All,
I have n number of client databases which is using SQL server compact edition 3.5 SP1 (CE).And the central server SQL server 2008 Enterprise edition. When ever network connection enabled i will transfer data from the CE to server, and delete the data from the CE database using MSMQ. Can anybody suggest what backup and recovery strategy I should use in this situation? So that the data lose is very less in the server.
Thanks,
Roshan
May 10, 2010 at 3:14 am
Do u have any fixed time for transfering data from CE to server?
Is it possible to take backup from CE before deleting the data?
May 10, 2010 at 3:29 am
Hi Ratheesh
There is no fixed time frame for transferring data from CE to server, Whenever network connection available it will transfer data. Once the acknowledgment received from the server i will delete the data from the CE.
Once the data received in the server means that is server responsibility to keep the data safe. so I am more concerned about on SQL server enterprise data
Thanks
Roshan
May 10, 2010 at 6:37 am
I'd say a basic backup and recovery plan should cover you well. Schedule a full backup on a regular basis. Most of our db's are less than 100gb, so we do a full backup nightly. As they grow you may need to do full backups once a week or something and then do differentials. After the full backup you just need good log backups, with a defined level of data loss, say, 15 minutes. Then you do the log backup every 15 minutes. Recoverability can be up to the second or, no older than 15 minutes in this fashion.
If you really want to cover your bases, you can add in mirroring.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2010 at 5:05 am
Hi,
For backup and recovery stratergy,
Consider following
1) how heavily are your db updated--> helps to decide log file size
2) size of db
3)how much db size increase on daily --->basis helps to decide log file size
if db are not heavily updated u can schedule log backups every 4 hours not to mention log files are sized accordingly
daily diiferntial backup at night and full backup once or twice in week ,if db around 100 -200 gb,will do the work
May 11, 2010 at 5:33 am
ankur.hooda (5/11/2010)
Hi,For backup and recovery stratergy,
Consider following
1) how heavily are your db updated--> helps to decide log file size
2) size of db
3)how much db size increase on daily --->basis helps to decide log file size
if db are not heavily updated u can schedule log backups every 4 hours not to mention log files are sized accordingly
daily diiferntial backup at night and full backup once or twice in week ,if db around 100 -200 gb,will do the work
Log backups are not just a matter of how heavy the use of the log is. They are also reflective of just how much data the business is willing to lose.
Assume for a moment that the database gets corrupted and it happens in such a way that you can't do a tail backup on the log. With a four hour cycle of log backups, you could lose 3 hours 59 minutes of data. I would confirm with the business that is a level of risk they're willing to take on. Five, ten or fifteen minutes is much more standard for risk. Other business will want to risk nothing, but then you need to add in other forms of protection besides log backups.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2010 at 6:00 pm
Thanks everyone
Thanks for your valuable suggestions
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply