There has been some confusion around backup with SharePoint agents over SQL native backups. In short you should use one and only one. Pick a solution and test it, then test it again, and test once more for luck. SharePoint agent and SQL native backups both have their place in recovery but can create problems when used together.

SQL is a transactional databases, SharePoint is a transactional system; both need to be reviewed when you create your backup solution. While this post will not cover different recovery options I will highlight a problem with point in time recovery.

The issue is when you backup with the SharePoint native backup (PowerShell Backup-SPFarm cmdlet or Central Administration) it updates the LSN in SQL.

First let’s start by checking the LSN in SQL:

use msdb;

select database_name,first_lsn,last_lsn,checkpoint_lsn from dbo.backupset where database_name =
‘SP_Portal_PortalContent’;

Returns:

Next, let’s open a SharePoint PowerShell (As Administrator) and kick off a backup

Now that the SharePoint Backup has completed, let’s check the SQL LSN again.

You can see now that the number of backups has increased. SharePoint has used SQL Native Backup to back up the database.

So what happens now when we do a transaction log back and point in time restore?

Let kick of a transaction log backup;

To do this I just created a standard SQL maintenance plan to run a transaction log backup on the database in question.

Let’s check the LSN again and you will see another line has been added which is our transaction log file;

So the next question is how to restore?

If you open SQL manager and kick off a restore, SQL will default to the last Full Backup and any Transaction logs from the last full back up till now.

As you can see the last Full Backup is the SharePoint Native Backup we run from power shell. An SQL restore now needs the SharePoint Backup files to recover.

If we attempt to restore, after a few days, the SharePoint Backup location may have changed. The SQL will report a missing device.

While the file is maybe safe somewhere on the file system, if the SQL server cannot find the file the below error will happen.

So now we need to find the backup files. The files will be located in the SharePoint backup location. To find this run the following query;

SELECT dbo.backupset.database_name, dbo.backupmediafamily.physical_device_name

FROM dbo.backupset

INNER
JOIN dbo.backupmediafamily

ON dbo.backupset.backup_set_id=dbo.backupmediafamily.media_set_id where dbo.backupset.database_name =
‘SP_Portal_PortalContent’

Now we can see the location of the backup file.

If we need to restore, we would need the SharePoint Backup file and the SQL transaction log file.