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 at 14:58
  • RESTORE VERIFYONLY FROM DISK is currently running I will update the result. – Ali Sep 25 at 15:00
  • It happens with this particular database backup only all other backups from the same server works fine. – Ali Sep 25 at 15:03
  • What happens when you omit the maxtransfersize or use maxtransfersize of 1MB (1048576) ? – Randi Vertongen Sep 25 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 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

  • 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 at 16:53
  • We only have issue with this particular database. – Ali Sep 26 at 16:54
  • What is the backup type? Did you try it using powershell? – Priyanka Sep 27 at 8:56

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.