Restore Database from Backup

Posted on 2005-03-13
Medium Priority
Last Modified: 2008-02-01
I have a database I wish to restore from a backup.  It was created on another server, therefore it has a different sa.  I have tried with no success to use the All Tasks->Restore Database.  Its also worth mentioning that the database name has a dash in it.

Any ideas?
Question by:BillPowell
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
  • 2
  • 2
  • 2
  • +1
LVL 22

Expert Comment

ID: 13529810
The hyphen in the db name shouldn't be a problem.  Neither should the fact that "sa" on the other server might be different... the "logins" are contained in MASTER, not in the user db's.

The easiest way to restore a db to a different path than from where it was backed up from is to use the "restore with move" feature...

   RESTORE DATABASE [database] FROM DISK = 'D:\Backup\XXXXXX.bak'
                 WITH MOVE '[dataname]' TO 'XXXXX_Data.mdf', MOVE '[logname]' TO 'XXXXX_Log.ldf'

See BOL for more info.


LVL 34

Expert Comment

ID: 13530617
" Neither should the fact that "sa" on the other server might be different"

The sa shouldn't be a problem.  Just remember, the users in the database you restore will NO LONGER have a login on the new server--you will have to relink them with sp_change_users_login.


Expert Comment

by:Mehul Shah
ID: 13532652
What problems you faced while restoring the db using All Tasks->Restore Database.
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

LVL 11

Author Comment

ID: 13536334
<<What problems you faced while restoring the db using All Tasks->Restore Database.>>

Heres a link to the errors Im receiving:


The first one happens when restoring.  The second one happens after I have OK'd the errors and am now trying to open one of the tables in EM.  The database does appear to exist in EM, but when I switch to QA it does not appear in the databases pick list.

Accepted Solution

Mehul Shah earned 1200 total points
ID: 13536391
When you are trying to restore the database on the second tab you need to give the physical path of the database files. As by default it will be the path from where the backup was taken.

So say you took the backup from System A where the data files were stored on c:\mssql folder. In the backup set the same path will be stored. When you try to restore the same backup on System B, the default folder will be c:\mssql and if that folder is not found the restore will fail.

The solution is to provide the correct path on second tab of the restore screen.

Hope this helps

LVL 11

Author Comment

ID: 13537493
That did it.  The paths were different.
LVL 34

Expert Comment

ID: 13539541
"That did it.  The paths were different."

That's exactly what JesterToo said above.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

800 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