• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

Restoring MS-SQL DBs from .bak files

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
masonrivlin
Asked:
masonrivlin
2 Solutions
 
allenstonerCommented:
 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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
masonrivlinAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
CleanupPingCommented:
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
 
Anthony PerkinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now