[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

importing MSSQL database into new installation

Posted on 1998-05-29
10
Medium Priority
?
364 Views
Last Modified: 2008-02-01
I had to reinstall SQL Server due to a HD crash.
I have a backup of my database and log on a different drive. Unfortunately I didn't back up the Master database
Is there a way to import my old database into the new installation?

Help appreciated

Ion
0
Comment
Question by:iamari
[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
10 Comments
 
LVL 9

Expert Comment

by:cymbolic
ID: 1091376
I'ts nice to have a current master database, but you shouldn't need one to restore your database.  In case of media failure, It's recommended that you drop your existing database and recreate the database and device files before restoring anyway. Without a current master, if you recreate your database and devices using enterprise manager, then you should be able to restore to the recreated database.  Just make sure the database and devices you create are as big or bigger than the one you dumped.

It's always good practice to document your database and device settings as you create them, just so you know the specifics when the hard drive hits the fan!   But it's worth a try to recreate from memory or overshoot just to get your data back.
0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1091377
It's a good practice to backup the master database every time a database is created, modified (size change) or devices are added, etc.  Even so, we keep a separate text file that documents each device and states what database is allocated to it, etc, in a text file for potential use, just in case.
0
 
LVL 2

Author Comment

by:iamari
ID: 1091378
I've recreated the devices and database (without defining tables). How do I restore now the old one?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 2

Expert Comment

by:threshold
ID: 1091379
To rebuild same database on other host/database, there is two ways:

1. Build a new empty database as target,
    use Enterprise Manager,
    and right-click the source database ,
    then transfer it to target.

2. Build a new empty database as target, it has the same size as source database
    backup the source database into disk backup device,
    and find the .dat file in ...Mssql/Backup/
    copy the .dat file to target host
    then restore the target database by the file.
    finally, change the size of target database , if you need.
   

0
 
LVL 2

Author Comment

by:iamari
ID: 1091380
I'm afraid I still don't understand.
In Enterprise Manager, I can't see the source database, since it no longer belongs to a server. So when I go to Tools/Database/Object_Transfer it prompts me for a server name for the source. But there is no server name, just two files named MyData.DAT and MyLog.DAT
If I could simply import the two files into the new devices, I would be all set. But I couldn't find a way to do this

Regards,

Ion
0
 
LVL 2

Author Comment

by:iamari
ID: 1091381
I've recreated the two devices and the database. Then I've stopped the MSSql service and copied the old files over the newly created ones. Now I see the database but is grayed out and marked "suspect". When I try to open it says "The database is marked inaccessible and cannot be edited.

Any more help appreciated

Ion
0
 
LVL 2

Author Comment

by:iamari
ID: 1091382
Let's start over. I had a crash that deleted everything except the database files, MyData.DAT and MyLog.DAT
I've reinstalled SQL Server and I'm trying to recreate my database using the two files I have left. It looks like there is no direct way to import my old files into the new installation.

I've created two new devices, same name and size as the original ones. I've created a new database, with Data and Log on the two new devices. I've created a backup device for the new database and use it to backup Data and Log. After this, I delete the new database in order to use the backup device to restore it. (If I don't do this, the database will be marked as "suspect" and prevent me from restoring)

Now I close SQL Server, close SQL Services and manually copy my old files over the new ones.
I reopen the services and reopen the Enterprise Manager. The way I figure it, if I create now a new database For Load, I should be able to use the old file for restore. But what happens, even though I recreate it for Load (using the same Data and Log devices) it REWRITES the two (old) files with new, useless information. So when I use the backup device, I get nothing but a generic database.

I'm beginning to think it's imposible to do this type of restore, but this would be insane!

Please help!

Ion
0
 
LVL 2

Accepted Solution

by:
Ezhil earned 750 total points
ID: 1091383
What threshold says is correct , but follow the steps
1. Creata DB device and LOG device with same size as the old one.
2. Create a DB .
3. Create logins , users , groups if any.
5. Stop SQL Server.
4. Now copy the old DB & LOG files into the new one.
5. STOP & START Your SQL Server
0
 
LVL 2

Author Comment

by:iamari
ID: 1091384
Well, that's exactly whta I did the first time. But when I do this, the database is marked as "suspect" and cannot be edited.
Any other ideas?
0
 
LVL 2

Author Comment

by:iamari
ID: 1091385
I found a bit of code that gets rid of the "suspect" mark. It goes like:
use master
go
sp_configure "allow updates", 1
reconfigure with override
go
update sysdatabases
set status = status - 256
where name = "MyDatabase"
and status & 256 = 256
go
sp_configure "allow updates", 0
reconfigure
go

I get rid of "suspect" but instead, I get "recovering", so the database is still inaccessible. Perhaps this IS insane!

Ion
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
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 the fundamental information of how to create a table.
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.

650 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