[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1171
  • Last Modified:

Restore a Database from a bak file on the network

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
jaze95
Asked:
jaze95
  • 11
  • 6
  • 4
  • +1
2 Solutions
 
Lori99Commented:
Try using UNC path to specify the location of your file.  I. E., \\SERVER\mssql\backup\jeffdb.bak.
0
 
adwisemanCommented:
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
 
arbertCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jaze95Author Commented:
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
 
jaze95Author Commented:
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
 
adwisemanCommented:
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
 
Lori99Commented:
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
 
jaze95Author Commented:
There is no connections to this database
0
 
jaze95Author Commented:
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
 
Lori99Commented:
Exactly right jaze95
0
 
Lori99Commented:
Oops, except your last alter has Northwind instead of televantage_reporting as the database name. : )
0
 
jaze95Author Commented:
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
 
Lori99Commented:
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
 
jaze95Author Commented:
No I was scripting a job then running the job
0
 
adwisemanCommented:
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
 
jaze95Author Commented:
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
 
Lori99Commented:
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
 
jaze95Author Commented:
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
 
jaze95Author Commented:
I can see the file, I browse to it so I don't see permissions being a problem
0
 
jaze95Author Commented:
0
 
adwisemanCommented:
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
 
jaze95Author Commented:
I run the following

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

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now