SQL Server 代理数据库还原作业失败
SSA 数据库还原作业偶尔会失败并出现以下错误: 字符串数据,右截断 [SQLSTATE 01004]
此作业每天运行,并且大多数时间都会成功。当它失败时是在星期五,并且它使数据库处于恢复状态。作业内的代码与其他数据库恢复作业中使用的代码完全相同,这不是问题。
每次发生这种情况我的解决方案都是删除数据库并手动恢复。手动恢复后,我运行作业并且运行良好。从那时起,它每天都继续正常运行,直到出现故障为止。
我检查了 SQL 日志,确实看到了这个错误: “spid75、未知、BackupDiskFile::OpenMedia:备份设备 '\the_path_to_the_backups' 无法打开。操作系统错误 5(访问被拒绝。)。,,,,”。 因此,我检查以确保用户有权在文件夹级别和文件级别访问 .BAK 文件。它确实具有完全访问权限。
关于如何阻止这项工作随机失败有什么想法吗?
编辑: 这是该工作的代码。
USE DBA_Tools;
GO
EXECUTE
DBMaintenance.restoreBackupChain
@BackupFolder = '\\servername\sqlbackups2$\instancename\Database_Backups\DSOG',
@Database = 'DSOG';
USE [DSOG]
GO
EXEC sp_change_users_login 'Auto_Fix', 'reportingServicesUser'
GO
EXEC [DSOG].dbo.sp_changedbowner @loginame = N'sa', @map = false
GO
USE [master] ;
ALTER DATABASE [DSOG] SET RECOVERY SIMPLE ;
GO
An SSA database restore job fails sporadically with this error:
String data, right truncation [SQLSTATE 01004]
This job runs daily, and succeeds most of the time. When it fails it is on a Friday and it leaves the database in a restoring state. The code inside the job is the exact same code used for other database restore jobs where this is not a concern.
My solution each time this happens is to delete the database and manually restore it. After manually restoring, I run the job and it runs fine. It continues to run fine every day from that point on until it doesn't.
I checked the SQL logs and I do see this error:
"spid75,Unknown,BackupDiskFile::OpenMedia: Backup device '\the_path_to_the_backups' failed to open. Operating system error 5(Access is denied.).,,,,".
So, I checked to ensure the user has permission to access to the .BAK file at the folder level and the file level. It does have full access.
Any thoughts on how I can stop this job from failing randomly?
EDIT:
This is the code from that job.
USE DBA_Tools;
GO
EXECUTE
DBMaintenance.restoreBackupChain
@BackupFolder = '\\servername\sqlbackups2$\instancename\Database_Backups\DSOG',
@Database = 'DSOG';
USE [DSOG]
GO
EXEC sp_change_users_login 'Auto_Fix', 'reportingServicesUser'
GO
EXEC [DSOG].dbo.sp_changedbowner @loginame = N'sa', @map = false
GO
USE [master] ;
ALTER DATABASE [DSOG] SET RECOVERY SIMPLE ;
GO
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论