Solved

Restore a Database from a bak file on the network

Posted on 2004-04-30
22
1,150 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

896 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

11 Experts available now in Live!

Get 1:1 Help Now