Failed to take a copy-only full backup for the dSource

  • 1
  • 1
  • Problem
  • Updated 2 years ago
  • Solved
I'm trying to run a SnapSync of a SQL Server data source. I have chosen the full-copy backup method for the snapshot. I am receiving the following error "Failed to take a copy-only full backup for the dSource XXX; Make sure that the source database is up and there is no other database backup running against it. Also make sure that the source database user "delphix_db" has privileges to take backups.". I've verified that the source is up and no other backups are running. I've also double checked the privileges of the database user delphix_db according to the requirements listed here -- https://docs.delphix.com/display/DOCS50/Requirements+for+SQL+Server+Source+Hosts+and+Databases

What else should I do?
Photo of Owen

Owen

  • 130 Points 100 badge 2x thumb

Posted 2 years ago

  • 1
  • 1
Photo of Neal Stack

Neal Stack, Employee

  • 2,902 Points 2k badge 2x thumb
Official Response
Hello,

If you login to the SQL Server data source as the "delphix_db" user, are you able to initiate a backup using syntax similar to the following?

BACKUP DATABASE [XXX] TO DISK=N'\\10.201.65.80\42378ae2-a1b9-d705-62ae-fe4485b7bc74-staging-127\dlpxCopyOnlyFullBackup.dlpxbak' WITH NAME=N'Delphix Copy-only Full Backup by XXX (9257f804-fcee-4e6e-acf8-0b9015770a9c)', INIT, COPY_ONLY

You will need to change the path to a local disk because Delphix temporarily shares this path from the staging host.

If this works OK but still fails when doing it from Delphix, we would likely need to see a support bundle (https://support.delphix.com/hc/en-us/articles/201655216-Best-Practice-Uploading-Support-Logs ).

Thanks,
  Neal
Photo of Neal Stack

Neal Stack, Employee

  • 2,902 Points 2k badge 2x thumb
Official Response
Hi Owen,

I just got back from holiday and I'm not sure if you still need help on this or not as it looks like the issue was marked solved.

If you do, I would need to see a support bundle. Do you have a Delphix support account?
Photo of Neal Stack

Neal Stack, Employee

  • 2,902 Points 2k badge 2x thumb
Official Response
Hello,

You should be able to go here (https://upload.delphix.com/) and upload the file. Use case number 77777 and let me know when the file is uploaded. Detailed instructions for generating a support bundle and uploading it are here (https://support.delphix.com/hc/en-us/articles/201655216-Best-Practice-Uploading-Support-Logs).

Thanks,
  Neal
Photo of Neal Stack

Neal Stack, Employee

  • 2,902 Points 2k badge 2x thumb
Official Response
Hi Owen,

Thanks for providing the support bundle. I can see that the source SQL Server instance is owned by "NT Service\MSSQLSERVER". There is a known bug for this.

Normally, Delphix creates a temporary SMB share from the toolkit directory of the staging host and only shares it to the owner of the source SQL Server instance for as long as it takes to write the backup. When it is running under this account type, the permissions don't get set properly and we get this error in the debug logs when SQL Server tries to write to the directory:

[2016-11-22 08:21:35,733][DEBUG][host.WindowsHostUtils#dumpStdoutAndStderr:100][Worker-667|JOB-254|DB_SYNC(MSSQL_DB_CONTAINER-25)][ACTION-783] stdout from C:\Program Files\Delphix\DelphixConnector\101b5208-7f80-9c0d-ecfe-ffe7067b8a97-staging-25\SCRIPT\CopyOnlyFullBackup.wrap.ps1Changed database context to 'master'.
BACKUP DATABASE [TestDB] TO DISK=N'\\smu-238-205.acme.com\101b5208-7f80-9c0d-ecfe-ffe7067b8a97-staging-25\dlpxCopyOnlyFullBackup.dlpxbak'
WITH NAME=N'Delphix Copy-only Full Backup by bigfish88 (4005d8a6-282b-4f15-af56-2b5ae99ff616)', INIT, COPY_ONLY
Msg 3201, Level 16, State 1, Server SMU-238-205, Line 1
Cannot open backup device '\\smu-238-205.acme.com\101b5208-7f80-9c0d-ecfe-ffe7067b8a97-staging-25\dlpxCopyOnlyFullBackup.dlpxbak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Server SMU-238-205, Line 1
BACKUP DATABASE is terminating abnormally.
Msg 50000, Level 11, State 127, Server SMU-238-205, Line 3
error_backup_database

We may support this in the future but for now, the SQL Server instance on the source host should run as either domain users or local service accounts. Delphix does not support running SQL Server instances as local user accounts or Managed Service Accounts (MSA).

https://docs.delphix.com/display/DOCS/Requirements+for+SQL+Server+Source+Hosts+and+Databases

I hope this helps,

Neal
Photo of Neal Stack

Neal Stack, Employee

  • 2,902 Points 2k badge 2x thumb
Official Response
Hello,

I apologize for not catching this before. I notice that in the support bundle you uploaded previously, the version of the SQL Server instance shows "13.0.1601.5" which equates to SQL Server 2016. We don't support that version yet. Support for this version of SQL Server is coming in Delphix 5.1.4.0 which is scheduled in approximately 2 weeks (mid December).

The key message here is:
Column name or number of supplied values does not match table definition.
The SQL Server 2016 "RESTORE ... HEADERONLY" command returns an additional column that Delphix isn't expecting.

Are you able to test with an older version of SQL Server?

Thanks,
  Neal