Actually I should write this post before my last post Network
shared Backup , which I shared yesterday. I have decided I will
share this week on network database activity. Show this is my 2nd
post on the same. While I was started to backup, restore and creating a database
on network shares I faced below errors. And most of the cases issues are
related to Start-up services. Generally when we install SQL server the service
will be Local or Network in this situation it will not work until we will not
change start-up services or we will give full access to the network shared
folder.
Error:-
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device
'\\192.168.XX.XX\ITDev\suman\bak\abc.bak'. Operating system error 5(Access is
denied.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating
abnormally.
RESTORE DATABASE [world2] FROM DISK = N'\\192.168.x.xx\ITDev\suman\bak\abc.bak'
WITH FILE = 1,
MOVE N'world' TO N'\\192.168.xx.xx\ITDev\suman\bak\world.mdf',
MOVE N'world_log' TO N'\\192.168.xx.xx\ITDev\suman\bak\world_log.ldf', NOUNLOAD, STATS = 5
GO
Solution:-
Open SQL Server Services
(Run->compmgmt.msc). You will see like below
Now change the start-up
user which have full access on the file.
After changes we will
see like below…
Now try again
restore command...
RESTORE DATABASE [world2] FROM DISK = N'\\192.168.1.3\ITDev\suman\bak\abc.bak'
WITH FILE = 1,
MOVE N'world' TO N'\\192.168.1.3\ITDev\suman\bak\world.mdf',
MOVE N'world_log' TO N'\\192.168.1.3\ITDev\suman\bak\world_log.ldf', NOUNLOAD, STATS = 5
Yepppppeeeeeeeeeeeeee
6 percent processed.
11 percent processed.
15 percent processed.
22 percent processed.
26 percent processed.
30 percent processed.
35 percent processed.
41 percent processed.
46 percent processed.
50 percent processed.
55 percent processed.
61 percent processed.
66 percent processed.
70 percent processed.
75 percent processed.
81 percent processed.
86 percent processed.
90 percent processed.
97 percent processed.
100 percent processed.
Processed 360 pages for database 'world2', file
'world' on file 1.
Processed 3 pages for database 'world2', file
'world_log' on file 1.
RESTORE DATABASE successfully processed 363 pages
in 1.893 seconds (1.496 MB/sec).
Comments
Post a Comment
Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757