Differential Backup

[franco]

SQL 7.0 SP1 on NT 4.0 EE

Thru maintenance plan I backup my DBs every night and the T-Logs every hour.
Now I have to set the option "select into/bulk copy" on one DB.
I schedule the differential backup every 3 hour with option "overwrite existing media", wich is, in my case, one file on disk.
My sequence of T-Logs is valid even if a non logged operations occurs?
If I have to restore this DB can I use the last full backup + the differential backup + all the logs in between?

example:
22.00 full db backup
08.00 t-log
09.00 t-log
10.00 t-log
10.30 diff backup
11.00 t-log
12.00 t-log
13.30 diff backup
14.00 t-log
15.00 t-log

Or my log are unusuable because the non logged operation?
Thank you very much.

[bobby]

The non logged operation will not be logged and so that part of transaction cannot be restored. Good luck!!!

------------
franco at 5/3/01 9:10:11 AM

SQL 7.0 SP1 on NT 4.0 EE

Thru maintenance plan I backup my DBs every night and the T-Logs every hour.
Now I have to set the option "select into/bulk copy" on one DB.
I schedule the differential backup every 3 hour with option "overwrite existing media", wich is, in my case, one file on disk.
My sequence of T-Logs is valid even if a non logged operations occurs?
If I have to restore this DB can I use the last full backup + the differential backup + all the logs in between?

example:
22.00 full db backup
08.00 t-log
09.00 t-log
10.00 t-log
10.30 diff backup
11.00 t-log
12.00 t-log
13.30 diff backup
14.00 t-log
15.00 t-log

Or my log are unusuable because the non logged operation?
Thank you very much.

Yes, they are unusuable.


------------
franco at 5/3/01 9:10:11 AM

SQL 7.0 SP1 on NT 4.0 EE

Thru maintenance plan I backup my DBs every night and the T-Logs every hour.
Now I have to set the option "select into/bulk copy" on one DB.
I schedule the differential backup every 3 hour with option "overwrite existing media", wich is, in my case, one file on disk.
My sequence of T-Logs is valid even if a non logged operations occurs?
If I have to restore this DB can I use the last full backup + the differential backup + all the logs in between?

example:
22.00 full db backup
08.00 t-log
09.00 t-log
10.00 t-log
10.30 diff backup
11.00 t-log
12.00 t-log
13.30 diff backup
14.00 t-log
15.00 t-log

Or my log are unusuable because the non logged operation?
Thank you very much.

[ItsMeAgain]

Just imagine this scenario...

22.00 full db backup
08.00 t-log
09.00 t-log
10.00 t-log
10.30 diff backup
--11.10 NON LOGGED OPERATION inserting row "AAA"
--11.11 LOGGED OPERATION updating row "AAA"
11.00 t-log
--DB CRASH!!!

...your restore strategy could be...
restoring the 22.00 full db backup
restoring the 10.30 diff backup
restoring the 11.00 t-log... and just imagine what happen when you try to update row "AAA" which no exist in the database because it was inserted in a non-logged operation.



------------
franco at 5/3/01 9:10:11 AM

SQL 7.0 SP1 on NT 4.0 EE

Thru maintenance plan I backup my DBs every night and the T-Logs every hour.
Now I have to set the option "select into/bulk copy" on one DB.
I schedule the differential backup every 3 hour with option "overwrite existing media", wich is, in my case, one file on disk.
My sequence of T-Logs is valid even if a non logged operations occurs?
If I have to restore this DB can I use the last full backup + the differential backup + all the logs in between?

example:
22.00 full db backup
08.00 t-log
09.00 t-log
10.00 t-log
10.30 diff backup
11.00 t-log
12.00 t-log
13.30 diff backup
14.00 t-log
15.00 t-log

Or my log are unusuable because the non logged operation?
Thank you very much.

[franco]

Very clear, thank you. Two more question:
If the non logged operation is just the population of one table that I do not need for production, say it is just a tables that it will be populated each time I run one task,and everytime re-populated, can I affoard to leave my backup strategy like it is?
I read that the backup of t-log will fail if there are non logged operations,it mean that my backup of t-log will fail if I run it during the non logged operation or it will fail anyway?
Thank you again.