[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Restore a Database from a bak file on the network

Posted on 2004-04-30
22
Medium Priority
?
1,169 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

656 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