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)

9
  • 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
  • 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
  • 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 <X64> (Build 14393: ) (Hypervisor) – Ali Sep 25 '19 at 15:06
  • I'm restoring from Prod to QA: Prod is Enterprise (same version) while QA is Develper Edition. – Ali Sep 25 '19 at 15:07

Your Answer

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

Browse other questions tagged or ask your own question.