?
Solved

restoring from a network drive

Posted on 2005-04-18
8
Medium Priority
?
238 Views
Last Modified: 2010-03-19
I have a database backup that is 180 gb and it is too big to copy to the local machine and restore.   I have enough room for the database, but not the database and the backup.

I need to restore this database, but I get this error:

Cannot open backup device “\\milfs2\sqlbackups\MLIBSQL\MLIB_DATAbackup.bak”.  Device error or device off-line.


However, I have verified the file exists and I can even see it from inside QA when I execute:

exec master..xp_cmdshell 'dir \\milfs2\sqlbackups\MLIBSQL\MLIB_DATAbackup.bak'

I get:

Volume in drive \\milfs2\sqlbackups is sqlbackups
   Volume Serial Number is 6A01-34AC
  NULL
   Directory of \\milfs2\sqlbackups\MLIBSQL
  NULL
  04/17/2005  12:58 AM   186,521,434,624 MLIB_DATAbackup.bak
                 1 File(s) 186,521,434,624 bytes
                 0 Dir(s)  850,170,597,376 bytes free
  NULL

So I know I can see the file in QA.

What am I doing wrong?
0
Comment
Question by:sifuhall
  • 4
  • 3
8 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13807255
Does you command look like this?

RESTORE DATABASE MLIB_DATA
   FROM DISK = '\\milfs2\sqlbackups\MLIBSQL\MLIB_DATAbackup.bak'

Please post the command you are trying to execute.
0
 
LVL 1

Author Comment

by:sifuhall
ID: 13807482
Great, making a bit of progress now.  However I'm getting a different error

Using the command:
RESTORE DATABASE MLIB_DATA
   FROM DISK = '\\milfs2\sqlbackups\MLIBSQL\MLIB_DATAbackup.bak'

I get:

Server: Msg 3101, Level 16, State 1, Line 1
  Exclusive access could not be obtained because the database is in use.
  Server: Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

So I try to get exclusing access and use:
ALTER DATABASE MLIB_DATA SET SINGLE_USER WITH ROLLBACK IMMEDIATE

and that is successfull.

However when I try:

RESTORE DATABASE MLIB_DATA
   FROM DISK = '\\milfs2\sqlbackups\MLIBSQL\MLIB_DATAbackup.bak'

In the same window I get the same error:

Server: Msg 3101, Level 16, State 1, Line 1
  Exclusive access could not be obtained because the database is in use.
  Server: Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13807522
Try changing your database to master and run it from there.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 1

Author Comment

by:sifuhall
ID: 13807849
Running from the master I get:

Cannot open backup device '\\milfs2\sqlbackups\MLIBSQL\MLIB_DATAbackup.bak'. Device error or device off-line. See the SQL Server error log for more details.
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 1500 total points
ID: 13808016
That's weird.  If you go back to your database and run the command, does it still give the other error of database in use?

Try verifying in Enterprise Manager that there is really no users to the database.  If there are, then kill the process.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 13811114
I didn't think you could restore from a network drive. I always thought it had to be a local drive.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13820011
sifuhall, were you able to resolve your problem in anyway?
0
 
LVL 1

Author Comment

by:sifuhall
ID: 13894260
No, however the need to restore has been addressed and this is no longer needed.

I would love to know what the problem was, however I do not have the time to continue to troubleshoot this since there is no longer a need to have it restored.

Thanks for the help.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question