Use of third-party VSS backup plus native SQL backup
3
votes
1
answer
3213
views
I have a SQL database server, and this uses R1Soft backup to take a server backup every 24 hours at 02:00. This is a full file system backup (bare metal plus daily differential, so includes the OS, etc.).
I want to increase the backup frequency of some databases, so that in the event of failure, I can restore to a 15 minute time window, e.g.
1. Full backup at 04:00
2. Tran log backup every minutes afterwards
What I cannot find *any* clarity on is whether the R1Soft backup (done via VSS Writer ), will cause any problems with my approach (particuarly breaking the log chain). I know very little about VSS, and the more I read, the more confusing it gets.
I contacted R1Soft to clarify, and their response was:
> We are using the VSS for SQL backups. You can use both solutions as
> far as it will not run in the same time. We use VSS writer to flush
> the logs into data and after that full backup the databases.
This means nothing to me, as I don't know what they mean by *"data"*, and it doesn't clarify the log chain concern. Therefore, can anybody with VSS experience please clarify whether VSS backups "interfere" with native full/transation log backups? From my research I see conflicting messages, because the Microsoft site states:
> SQL Writer does not support... Log backups
I don't know whether I should be doing what I propose, or whether I should instead
- Ask the server hosts to amend the database backup frequency to 15 mins
- Prevent the R1Soft database backup, and handle this manually, then let it just shadow copy the backup file
Any input, even if just to highlight the questions I should be asking them, would be appreciated. The more I read, the more confused I am getting.
### Update as per answer
database_name backup_start_date backup_finish_date expiration_date backup_type backup_size MB logical_device_name physical_device_name backupset_name description is_copy_only is_snapshot checkpoint_lsn database_backup_lsn differential_base_lsn first_lsn fork_point_lsn last_lsn
--------------- ----------------------- ----------------------- ----------------- ----------- ------------------ ----------------------- --------------------------------------------------- --------------- ------------- ------------ ----------- --------------------- --------------------- ---------------------- --------------------- ---------------- ---------------------
myDb 2017-09-13 02:00:04.000 2017-09-13 02:00:05.000 NULL Full 1525.43896484375 NULL {D827E1B8-FDB7-4AE5-9264-08D4CA29536A}1 NULL NULL 1 1 12207000004436400001 12207000004429300036 NULL 12207000004436400001 NULL 12207000004436700001
myDb 2017-09-13 00:11:00.000 2017-09-13 00:11:01.000 NULL Full 1525.44726562500 NULL {9B33317C-CABA-42DD-9839-9D4599A91205}1 NULL NULL 0 1 12207000004429300036 12207000003990700036 NULL 12207000004429300036 NULL 12207000004431300001
myDb 2017-09-12 02:00:13.000 2017-09-12 02:00:14.000 NULL Full 1525.32031250000 NULL {57B7F13B-9461-48C2-8BB3-3DA651485DC6}1 NULL NULL 1 1 12207000003995300034 12207000003990700036 NULL 12207000003995300034 NULL 12207000003996900001
myDb 2017-09-12 01:11:28.000 2017-09-12 01:11:29.000 NULL Full 1525.32226562500 NULL {924FE276-544D-40F6-93A2-C2375868DB07}1 NULL NULL 0 1 12207000003990700036 12207000003659900164 NULL 12207000003990700036 NULL 12207000003992700001
myDb 2017-09-11 13:33:08.000 2017-09-11 13:33:25.000 NULL Full 1526.40234375000 NULL D:\HostedFiles\Autobackup\bak\20170911_myDb.bak NULL NULL 1 0 12207000003837600221 12207000003659900164 NULL 12207000003837600221 NULL 12207000003846900001
myDb 2017-09-11 00:07:59.000 2017-09-11 00:08:00.000 NULL Full 1525.28271484375 NULL {713D7A36-D4F2-4B2F-A0C1-B079DE03F396}1 NULL NULL 0 1 12207000003659900164 12207000003645600222 NULL 12207000003659900164 NULL 12207000003666600001
myDb 2017-09-10 02:00:17.000 2017-09-10 02:00:17.000 NULL Full 1525.25146484375 NULL {3C42FCFF-BF45-49D6-AD78-57039DB7B4DA}1 NULL NULL 1 1 12207000003657200001 12207000003645600222 NULL 12207000003657200001 NULL 12207000003657500001
myDb 2017-09-10 00:05:34.000 2017-09-10 00:05:36.000 NULL Full 1525.29394531250 NULL {AC33EA68-9B40-4CE6-A2E5-76DE908AD813}1 NULL NULL 0 1 12207000003645600222 12207000003613700036 NULL 12207000003645600222 NULL 12207000003654600001
myDb 2017-09-09 04:06:22.000 2017-09-09 04:06:23.000 NULL Full 1525.26367187500 NULL {6BCA937F-7F1C-4A43-92D8-42366D36FA17}1 NULL NULL 0 1 12207000003613700036 12189000000394000087 NULL 12207000003613700036 NULL 12207000003616500001
myDb 2017-09-09 02:00:04.000 2017-09-09 02:00:21.000 NULL Full 1525.28076171875 NULL {6CD064AC-D2DC-4F84-97A7-88C3D959FEF4}1 NULL NULL 1 1 12207000003607000144 12189000000394000087 NULL 12207000003607000144 NULL 12207000003613500001
myDb 2017-09-08 02:00:04.000 2017-09-08 02:00:07.000 NULL Full 1524.68896484375 NULL {92C322C7-AB8A-4747-A765-4D63A86BDC50}1 NULL NULL 1 1 12189000000855600001 12189000000394000087 NULL 12189000000855600001 NULL 12189000000855900001
myDb 2017-09-08 00:30:00.000 2017-09-08 00:30:17.000 NULL Full 1525.38671875000 NULL D:\HostedFiles\Autobackup\bak\20170908_myDb.bak NULL NULL 1 0 12189000000846800208 12189000000394000087 NULL 12189000000846800208 NULL 12189000000855600001
myDb 2017-09-07 07:42:05.000 2017-09-07 07:42:06.000 NULL Full 1524.51806640625 NULL {77D9CBB9-60E4-4D19-99CF-B92499E33BA7}1 NULL NULL 0 1 12189000000394000087 12188000005293700036 NULL 12189000000394000087 NULL 12189000000397700001
myDb 2017-09-07 02:00:05.000 2017-09-07 02:00:10.000 NULL Full 1524.54052734375 NULL {4D94E390-FBCD-42D5-9191-A7AE9CED4932}1 NULL NULL 1 1 12189000000384900197 12188000005293700036 NULL 12189000000384900197 NULL 12189000000393200001
(14 row(s) affected)
Asked by EvilDr
(860 rep)
Sep 13, 2017, 09:14 AM
Last activity: Sep 15, 2017, 09:35 AM
Last activity: Sep 15, 2017, 09:35 AM