?
Solved

Restore a Database from a bak file on the network

Posted on 2004-04-30
22
Medium Priority
?
1,163 Views
Last Modified: 2012-08-14
I have a script to restore a database the thing is that the bak file is on the network, when I go to browse to that file SQL server says it can't find the file. It seems that it is looking for a drive.

Restore database jeffdb from disk =
 'd:\mssql\backup\jeffdb.bak'
  WITH Replace,
  MOVE 'Jeffdb_dat' TO 'd:mssql\backup\jeffdb.mdf',
  MOVE 'Jeffdb_log' TO 'd:mssql\backup\jeffdb.ldf'


Do I have to put the server name and use the $ sign to specify a drive??
0
Comment
Question by:jaze95
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
  • 4
  • +1
22 Comments
 
LVL 7

Assisted Solution

by:Lori99
Lori99 earned 600 total points
ID: 10959293
Try using UNC path to specify the location of your file.  I. E., \\SERVER\mssql\backup\jeffdb.bak.
0
 
LVL 14

Accepted Solution

by:
adwiseman earned 900 total points
ID: 10959321
Here, your restoreing from a local disk.

 'd:\mssql\backup\jeffdb.bak'
  WITH Replace,
  MOVE 'Jeffdb_dat' TO 'd:mssql\backup\jeffdb.mdf',
  MOVE 'Jeffdb_log' TO 'd:mssql\backup\jeffdb.ldf'

Here, would be an example from a network drive

 '\\<servername>\<share$>\backup\jeffdb.bak'
  WITH Replace,
  MOVE 'Jeffdb_dat' TO 'd:mssql\backup\jeffdb.mdf',
  MOVE 'Jeffdb_log' TO 'd:mssql\backup\jeffdb.ldf'
0
 
LVL 34

Expert Comment

by:arbert
ID: 10959358
Agree--use the UNC path..  When you use a drive letter, it references a drive letter on the server, not necessarily the machine the restore is running from....
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 2

Author Comment

by:jaze95
ID: 10959467
I did it before it just been a while

I remember using the UNC path with the share$

Let me test it out so I can award the points

thanks for the responses
0
 
LVL 2

Author Comment

by:jaze95
ID: 10960070
From 1 SQL server I created a backup

the name of backup is TVDB

The database that is trying to restore this file name is televantage

Here's the script that I'm trying to create a job from :

Restore database televantage_Reporting from disk =
'\\1777bbtv01\C$\Netsetup\backup\TVDB.bak'
  WITH Replace,
  MOVE 'televantage_Reporting_dat' TO 'E:mssql\data\televantage_reporting.mdf',
  MOVE 'televantage_Reporting_log' TO 'E:mssql\data\televantage_reporting.ldf'


Here is the error I got



Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000] (Error 3101)  Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10960134
Go into enterprise manager, and to manage, current actifities, and kill all connection to the database your trying to restore over, or restore to a database name that doesn't currently exist.  You can't restore over a database that has a connection to it.  Also, run this script connected to another databse, like master.
0
 
LVL 7

Expert Comment

by:Lori99
ID: 10960162
You need to be in SINGLE_USER mode to do the restore.  Here is the command.

ALTER DATABASE Northwind
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

ROLLBACK IMMEDIATE will disconnect any users connected to the database.  After your restore, set it back to MULTI_USER with:

ALTER DATABASE Northwind
SET MULTI_USER
0
 
LVL 2

Author Comment

by:jaze95
ID: 10960177
There is no connections to this database
0
 
LVL 2

Author Comment

by:jaze95
ID: 10960197
Lori99

so do something like this

ALTER DATABASE televantage_reporting
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

Restore database televantage_Reporting from disk =
'\\1777bbtv01\C$\Netsetup\backup\TVDB.bak'
  WITH Replace,
  MOVE 'televantage_Reporting_dat' TO 'E:mssql\data\televantage_reporting.mdf',
  MOVE 'televantage_Reporting_log' TO 'E:mssql\data\televantage_reporting.ldf'

ALTER DATABASE Northwind
SET MULTI_USER
0
 
LVL 7

Expert Comment

by:Lori99
ID: 10960210
Exactly right jaze95
0
 
LVL 7

Expert Comment

by:Lori99
ID: 10960229
Oops, except your last alter has Northwind instead of televantage_reporting as the database name. : )
0
 
LVL 2

Author Comment

by:jaze95
ID: 10960272
I got this

Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000] (Error 3101)  Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.
0
 
LVL 7

Expert Comment

by:Lori99
ID: 10960361
Are you running this from Query Analyzer?  Make sure you are using the Master database when you issue the restore and not in the database you are restoring to.  
0
 
LVL 2

Author Comment

by:jaze95
ID: 10960380
No I was scripting a job then running the job
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10960405
Try running your script from query analyzer.

Scripting it is going to lead to other issues, like the user the jobs run under must have access to your netword drive.
0
 
LVL 2

Author Comment

by:jaze95
ID: 10960561
I got this error

2004-04-30 10:25:00.85 kernel   BackupDiskFile::OpenMedia: Backup device '\\1777bbtv01\C$\Netsetup\backup\TVDB.bak' failed to open. Operating system error = 5(Access is denied

So I have to set my permissions on these serves to read write and execute?
0
 
LVL 7

Expert Comment

by:Lori99
ID: 10961293
Yes, whatever user is executing the restore needs access to the location of the backup file.  You could also try running this from Enterprise Manager and browse to the location of your backup file.  You can set single user from there also in the Database Properites under the Options tab.
0
 
LVL 2

Author Comment

by:jaze95
ID: 10961938
This back file is on another server, when I try restoring from EM it says it can't find the file and I get that error
0
 
LVL 2

Author Comment

by:jaze95
ID: 10962023
I can see the file, I browse to it so I don't see permissions being a problem
0
 
LVL 2

Author Comment

by:jaze95
ID: 10962625
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 10963653
So it is a permissions problem.  SQL server must also be able to see the network location.  You may be able to browse, but your server cant.

Quick test of this theory.

Exec master..xp_cmdshell 'dir \\server\share'
0
 
LVL 2

Author Comment

by:jaze95
ID: 11006155
I run the following

Exec master..xp_cmdshell '\\1777bbtv01\c$'

I get back
                                                                                                                                                               output                                                                                                                                                                        ------------------------------------------------------                                                                            
The system cannot find the path specified.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

770 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