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 Owen

Owen

  • 130 Points 100 badge 2x thumb
I'm able to initiate a backup with the data source as the user delphix_db --

C:\>sqlcmd -S <server> -U delphix_db -P <password>
1> BACKUP DATABASE TESTDB TO DISK=N'C:\TEMP\TEST.BAK' WITH NAME=N'Test', INIT, COPY_ONLY
2> GO
Processed 320 pages for database 'TESTDB', file 'TestDB' on file 1.
Processed 1 pages for database 'TESTDB', file 'TestDB_log' on file 1.
BACKUP DATABASE successfully processed 321 pages in 0.378 seconds (6.634 MB/sec).
1> EXIT
C:\>

Should I go ahead and create a support bundle?
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 Owen

Owen

  • 130 Points 100 badge 2x thumb
Hi Neal:

Not sure how it was marked solved. I'm still having the issue. I do no have a support account. I have been using Delphix Express.

Thanks
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 Owen

Owen

  • 130 Points 100 badge 2x thumb
Hi Neal:

I just uploaded the support bundle to case number 77777.

Thanks
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 Owen

Owen

  • 130 Points 100 badge 2x thumb
Thanks Neal. We missed this requirement earlier. This helps a lot. Thanks again.
Photo of Owen

Owen

  • 130 Points 100 badge 2x thumb
Hi Neal:

We switched to using a domain user and how are getting this error

Run SnapSync for database "LUNDB".For dSource "LUNDB", failed to query source database backup.
Make sure the user "SMU\smu" has privilege to restore filelists/header out of backups on instance "MSSQLSERVER" running on host "10.80.238.205" and the instance owner "NT Service\MSSQLSERVER" has permissions to read the backup. Refer to Microsoft SQL Server documentation for the required privileges.
Changed database context to 'master'.
Msg 213, Level 16, State 7, Server SMU-238-205, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Server SMU-238-205, Line 1
RESTORE FILELIST is terminating abnormally.
Msg 50000, Level 11, State 127, Server SMU-238-205, Line 3
error_restore_filelist
die : Error: run_sqlcmd_failed
At C:\Program Files\Delphix\DelphixConnector\101b5208-7f80-9c0d-ecfe-ffe7067b8a
97-staging-39\SCRIPT\RunSqlcmdWithEncryptionKey.ps1:23 char:9
+ die "$($args[0])"
+ ~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorExcep
tion
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorExceptio
n,die

This domain user has role memberships described here --

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

What else am I missing?

Thanks
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
Photo of Owen

Owen

  • 130 Points 100 badge 2x thumb
Hi Neal:

Yes, we will install an older version for now. We don't seem to be reading the system requirements very closely.

Thanks