Solved

Restore a Database from a bak file on the network

Posted on 2004-04-30
22
1,147 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
  • 11
  • 6
  • 4
  • +1
22 Comments
 
LVL 7

Assisted Solution

by:Lori99
Lori99 earned 200 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 300 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now