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.
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
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
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).