Real time backup database
Hi all,
I want to know is there any way to backup database which is running and updating continuously?
Here is my case:
I have a SQL Server 2005 Express DB which will be updated regularly, daily and continuously within office hour. (This DB is for production so it will be updated continuously and data will insert/update every 5 to 10 seconds.)
I want to know is "Is there any way to back up this kind of database without pausing the working process?". Because this is running continuously, I afraid when I do scheduler backup, data will lost or corrupt.
Currently, I suggest to insert into 2 DB. So, another is a backup in case the primary one got damage or corrupted or data loss.
What my users want is they don't want to increase this delay 10 or 15 seconds. this is their target time delay. So, they can't delay any more. And so currently, I'm out of idea.
Last time they got an experience of database crush so for that whole day, there is no data in DB. And so they request me to do backup of Live running database instead of backup after office hour.
How you guys think???
Any idea or have such kind of backup experience before???
Thanks.
scsfdev
Re: Real time backup database
SQL Server has pretty advanced backup functionality. First make sure the database's Recovery Model is set to "Full".
Then you can perform Transacation Log backups, say every hour. Only the modifications made in the previous hour are written to the back up media. This is very fast.
At night do a Full or Incremental (aka Differential) backup.
Using the above or a mix of the different backup types allows you to fully restore your database to the point of the crash. It would be very rare to have any data loss at all.
Quote:
Currently, I suggest to insert into 2 DB. So, another is a backup in case the primary one got damage or corrupted or data loss.
Reseach "Stand-By Server" in the help file. Basically, you backup the Primary server as described above and immediately restore the backups onto the Stand-By server. If the Primary Server fails, the Stand-By server can be placed "on-line".
Stand-By servers can also be utilized by applications that only require read access to data and don't care if the data is an hour or so behind the data in the Primary server.
Re: Real time backup database
Quote:
Originally Posted by
brucevde
SQL Server has pretty advanced backup functionality. First make sure the database's Recovery Model is set to "Full".
Then you can perform Transacation Log backups, say every hour. Only the modifications made in the previous hour are written to the back up media. This is very fast.
At night do a Full or Incremental (aka Differential) backup.
Using the above or a mix of the different backup types allows you to fully restore your database to the point of the crash. It would be very rare to have any data loss at all.
Reseach "Stand-By Server" in the help file. Basically, you backup the Primary server as described above and immediately restore the backups onto the Stand-By server. If the Primary Server fails, the Stand-By server can be placed "on-line".
Stand-By servers can also be utilized by applications that only require read access to data and don't care if the data is an hour or so behind the data in the Primary server.
Thanks brucevde.:thumb:
I will research further.
I'm not that good enough in SQL Server in-deep function. :)
Thanks again.
