| Author |
Topic |
|
jbunian
Starting Member
3 Posts |
 Posted - 2007-10-11 : 11:33:47
|
| Can I do backup/differential backup while updating database with 100s of new records?Will it cause any problem if I take FULL backup while using BULK COPY?Thanks,Sastri |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-11 : 11:44:08
|
| you can take a backup yes. but the records won't neceserily be there since the transaction log won't be flushed to db._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
jbunian
Starting Member
3 Posts |
Posted - 2007-10-11 : 11:49:44
|
| Thanks for your reply.I am using Simple recovery Mode. The feed to our database is not updating Transaction logs.My plan is to take FULL backup once in a day and DIFFERENTIAL backup every two hours. Our server is updated with data throughout 24 hrs. I am wondering is it safe to take these backup while the FEED is updating database with 1000s of records in a minute?Need somemore details.Thanks. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-11 : 11:56:43
|
| > The feed to our database is not updating Transaction logs.of course it does.yes you can take a backup but why not wait until it completes?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 15:36:13
|
| "you can take a backup yes. but the records won't neceserily be there since the transaction log won't be flushed to db."In FULL Recovery Model (and since SQL 7 I think) a FULL Backup is as-of the FINISH time, rather than the START time. So I presume that a FULL backup appends any "committed "transaction, from the TLog, after the normal "copy every sector to .BAK file" is completed??Kristen |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-11 : 16:00:04
|
yes.it's a good question though... what happens if a backup wants to finish while bulk insert is still in progress.my guess is that in simple and bulk logged mode it won't wait but in full it might.something to test  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
jbunian
Starting Member
3 Posts |
Posted - 2007-10-12 : 04:53:33
|
| The FEED is a continuous process through out the weekdays as it is a market data. I have chance only on weekends to take Full Backup presently. As I mentioned the third party is using some components to write data into our database which is not at all updating TLogs. Otherwise I would have backup Tlogs.Will it allow to take FULL/ differential backups while FEED is on?I can’t take chance to do this testing unless I know the right answer. We will not get the data if the server is down which is not affordable.Any source to get the right answer? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-12 : 05:08:58
|
| TLogs are always updated.yes you can take full backup while the feed is on.when the backup finishes you'll have the latest data that the feed put into the db and was succesfully commited to the db_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 05:21:22
|
| "As I mentioned the third party is using some components to write data into our database which is not at all updating TLogs"That's not possible, TLogs are always updated. In SIMPLE Recovery Model that data is not then retained so the Log file space can be reused (smaller log files)With FULL Recovery Model that data is retained until the next TLog backup. And allows roll-forwards - so you can, for example, res tor the last full backup and all TLog backups since; you can even use a "STOP-AFTER" command in the Restore to specify a point in time - i.e. all TLog data after that point is ignored during the restore.Now you may not want to do this type of restore on your Production server often (after some accident / disaster) but there may be circumstances where you do want to on a Test server. Diagnosing a problem that happened at some point-in-time; fraud; rescue accidentally deleted data to put back into the Product database, that type of thing.So for a mission critical system I would have thought TLog backups were essential - unless you can recreate the data by some other means [after a disaster]See also:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=How%20Backup%20works,What%20type%20of%20backup%20should%20I%20use,Automating%20Backups,BackupKristen |
|
|
|
|
Backup database