2

I have been trying to restore one of our db for the past few days without any luck. I keep getting the below error. I tried the restore during peak hours, off peak hours both by accessing the backup via network and then moving the file to local server.

I have also run Restorefilelistonly and restoreheaderonly on the backup and all seems to be fine. There is no permissions issue.

Please see the error below and the screenshot from SQL Logs. I did not find much help googling this issue.

1 percent processed.
2 percent processed.
3 percent processed.
4 percent processed.
5 percent processed.
6 percent processed.
7 percent processed.
8 percent processed.
9 percent processed.
10 percent processed.
11 percent processed.
12 percent processed.
13 percent processed.
14 percent processed.
15 percent processed.
16 percent processed.
17 percent processed.
18 percent processed.
19 percent processed.
20 percent processed.
21 percent processed.
22 percent processed.
23 percent processed.
24 percent processed.
25 percent processed.
26 percent processed.
27 percent processed.
28 percent processed.
29 percent processed.
30 percent processed.
31 percent processed.
32 percent processed.
33 percent processed.
34 percent processed.
35 percent processed.
36 percent processed.
37 percent processed.
38 percent processed.
39 percent processed.
40 percent processed.
41 percent processed.
42 percent processed.
43 percent processed.
44 percent processed.
45 percent processed.
46 percent processed.
47 percent processed.
48 percent processed.
49 percent processed.
50 percent processed.
51 percent processed.
52 percent processed.
53 percent processed.
54 percent processed.
55 percent processed.
56 percent processed.
57 percent processed.
58 percent processed.
59 percent processed.
60 percent processed.
61 percent processed.
62 percent processed.
63 percent processed.
64 percent processed.
65 percent processed.
66 percent processed.
67 percent processed.
68 percent processed.
69 percent processed.
70 percent processed.
71 percent processed.
72 percent processed.
73 percent processed.
74 percent processed.
75 percent processed.
76 percent processed.
77 percent processed.
78 percent processed.
79 percent processed.
80 percent processed.
81 percent processed.
82 percent processed.
83 percent processed.
84 percent processed.
85 percent processed.
86 percent processed.
87 percent processed.
88 percent processed.
89 percent processed.
90 percent processed.
91 percent processed.
92 percent processed.
93 percent processed.
94 percent processed.
95 percent processed.
96 percent processed.
97 percent processed.
98 percent processed.
Msg 3203, Level 16, State 1, Line 2
Read on "M:\MSSQL\My_FULL_Backup_20190924_010558.bak" failed: 1101(A tape access reached a filemark.)
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

enter image description here

Restore command used:

USE [master] RESTORE DATABASE [XXXXX_Testing_20190910] FROM DISK = N'M:\MSSQL\USE1-PRD-CL4$Entreprise_AG2_Testing_FULL_20190924_010558.bak' WITH FILE = 1, MOVE N'Testing' TO N'D:\MSSQL\DATA\Testing.mdf', MOVE N'Testing_log' TO N'L:\MSSQL\LOGS\Testing_log.ldf', NOUNLOAD, REPLACE, STATS = 1, maxtransfersize=65537

Version

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) Jul 12 2019 17:43:08 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

  • Does it happen with any backup you try to restore or only with this M:\MSSQL\My_FULL_Backup_20190924_010558.bak? – Ronaldo Sep 25 '19 at 14:58
  • RESTORE VERIFYONLY FROM DISK is currently running I will update the result. – Ali Sep 25 '19 at 15:00
  • It happens with this particular database backup only all other backups from the same server works fine. – Ali Sep 25 '19 at 15:03
  • What happens when you omit the maxtransfersize or use maxtransfersize of 1MB (1048576) ? – Randi Vertongen Sep 25 '19 at 15:03
  • Sorry I haven;t tried that option yet. The database it more than 1 tb so it takes time for every attempt for restoring. – Ali Sep 25 '19 at 15:04
1

Check the following solution:

Solution 1 Check security permission for user

  1. Go to the location of the backup folder where you have stored the.BAK file
  2. Right click on the backup file and select Properties
  3. Go to Security Tab
  4. Now check the Deny permission for Authenticated users
  5. Click on Edit and remove the Deny
  6. Click OK

Solution 2 Manually delete the previous backup and enable SQL server to execute new backups to the backup device.

“BACKUP DATABASE mydatabase TO DISK= ‘C:\Mydatabase. Bak’ with format”

You can also read more solutions here: reference

| improve this answer | |
  • thanks for the detailed answer. I had indeed started the process of taking manual backup without deleting the old one and without adding "with format" (this is before I saw your answer) option and then tried restoring from that backup and it worked. Now the question when I tried to restore the database with 2 previous automated backups it did not worked but it worked with the manual backup. Does this means our automated backup process can be an issue? We are using Olla's backup scripts ola.hallengren.com/sql-server-backup.html and it has been working great so far. – Ali Sep 26 '19 at 16:53
  • We only have issue with this particular database. – Ali Sep 26 '19 at 16:54
  • What is the backup type? Did you try it using powershell? – Priyanka Sep 27 '19 at 8:56
  • Hello Folks, I just wanted to add an important point here for the benefit of others as I got the same error one more time. I just realized that I only get this issue when restoring the TDE Encrypted databases. Microsoft had a fix for this issue in their CU release. Please refer to: support.microsoft.com/en-ca/help/4529942/… – Ali Dec 10 '19 at 20:19
  • SQL Server version 2019 is having the same issue and I did not see any fix released yet by Microsoft. – Ali Dec 10 '19 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.