Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Restoring MS-SQL DBs from .bak files

Posted on 2001-07-30
7
Medium Priority
?
454 Views
Last Modified: 2012-06-27
In the event of disaster, How does one recover previous system with backup files (.bak) on a new server and a fresh install of SQL Server?
0
Comment
Question by:masonrivlin
[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
7 Comments
 

Accepted Solution

by:
allenstoner earned 400 total points
ID: 6335397
 If you using Enterprise Manager you can right-click the databases tag and under 'All Tasks' is a 'Restore Database'.  In the 'Restore as database' field at the top you can put in a new database name or overwrite an existing database.  For what you're asking you'll want to put in a new name.
  You'll want to restore from a device.  Under 'Select Device' browse to the location of the .bak file and select it as the device.
  If the actual SQL directories are different you may need to change them under the 'Options' tab.
  If you want to use t-sql check out the bol for 'RESTORE DATABASE' which will give you some direction as well as the syntax.

Allen
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 6336743
The procedure might change slightly if you have older version of SQL Server, especially for the TSQL statement of RESTORE DATABASE...

furhtermore, you might check out the following stored procedure (SQL7+): sp_change_users_login. It will help you to clean up "orphaned" users and create the necessary logins.

CHeers
0
 

Author Comment

by:masonrivlin
ID: 6341436
I appreciate your comments so far.  This would work for user dbs.  What about the master database?  I try to restore over it, find the .bak file as a device, and am told that the backup's sort order and the current install's sort order do NOT match and it cannot proceed.  Also, part of the error is that the binary sort order id of the backup (42) is not the same as the current (52).
Now what I want to do is force it over the existing master db.  After that, I am certain that the rest is a simple matter indeed.

With that in mind, How do I restore the master db when the backup from which I am working was saved in a NON binary sort order?
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6343499
You have to rebuild the master database, using a tool that you should find on the SQL Server cd: rebuildm.exe
You can also reinstall the SQL Server, specifying those sort order.
0
 

Expert Comment

by:CleanupPing
ID: 9281934
masonrivlin:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9623425
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Award points to angelIII and allenstoner

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Anthony
EE Cleanup Volunteer
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

722 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