Thursday, June 15, 2023

SQL Server Restore Error - Access is Denied

 USE [master]
RESTORE DATABASE [SS_TESTDB_2023] FROM  DISK = N'E:\SQLServer_DB\SS_PRODDB_29MAY2023.bck' WITH  FILE = 1,  
MOVE N'SS_PRODDB' TO N'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023.mdf',  
MOVE N'SS_PRODDB_log' TO N'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023_log.ldf',  NOUNLOAD,  STATS = 5
GO
SQL Server logfile location 
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log 
2023-06-15 12:43:00.92 spid60      Error: 3633, Severity: 16, State: 1.
2023-06-15 12:43:00.92 spid60      The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023.mdf' at 'container.cpp'(2693).
2023-06-15 12:43:00.92 spid60      Error: 3633, Severity: 16, State: 1.
2023-06-15 12:43:00.92 spid60      The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023_log.ldf' at 'container.cpp'(2693).


solution
grant read,write permission on folder for the which is logged in
best practice is to login through Administrator and restore so that we could avoid the error

USE [master]
RESTORE DATABASE [SS_TESTDB_2023] FROM  DISK = N'E:\SQLServer_DB\SS_PRODDB_29MAY2023.bck' WITH  FILE = 1,  
MOVE N'SS_PRODDB' TO N'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023.mdf',  
MOVE N'SS_PRODDB_log' TO N'E:\SQLServer_DB\SS_DATAFILE\SS_TESTDB_2023_log.ldf',  NOUNLOAD,  STATS = 5
GO

5 percent processed.
10 percent processed.
15 percent processed.
20 percent processed.
25 percent processed.
30 percent processed.
35 percent processed.
40 percent processed.
45 percent processed.
50 percent processed.
55 percent processed.
60 percent processed.
65 percent processed.
70 percent processed.
75 percent processed.
80 percent processed.
85 percent processed.
90 percent processed.
95 percent processed.
100 percent processed.
Processed 3869 pages for database 'SS_TESTDB_2023', file 'SS_PRODDB' on file 1.
Processed 2 pages for database 'SS_TESTDB_2023', file 'SS_PRODDB_log' on file 1.
RESTORE DATABASE successfully processed 3869 pages in 280.499 seconds (66.223 MB/sec).



No comments:

Post a Comment