[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

moving databases

Posted on 2005-04-19
23
Medium Priority
?
199 Views
Last Modified: 2010-03-19
Good morning Expert,

Old computer crashed. Setting up new computer. Need to get databases from old computer to new computer. In Enterprise Manager when I try to set up a new database I can see, in the Tree, SQL Server Groups which are other SQL Servers on the LAN.  I can set up a new SQL Sever Group but after I do I cannnot set up databases. The only options available are
New SQl Server Registration
New SQL Server Group
Rename SQL Server Group

How do I add databases to the SQL Server Groups?
How can I get the databases on my old server to the new server?
Is this type of info documented anywhere? I find help on the
Microsdoft Management Console but nothing on SQL Server.

Thanks.

Allen in Dallas
0
Comment
Question by:Allen Pitts
  • 9
  • 8
  • 2
  • +3
23 Comments
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 13818115
I'm not sure to what extent you mean by the old computer crashed, but if you can STOP the SQL Services on the old computer and copy the .MDF and .LDF files to the new server, you can then "attach" them to the new server.  Very easy move.  Though you may have to create/edit some users...
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13818397
Setting up databases is more along the lines of "creating"

If you have backups, you can use RESTORE DATABASE, RESTORE LOG

leew's comments are correct if you have no backups but can still get the files off the old server.
0
 

Author Comment

by:Allen Pitts
ID: 13818445
The old computer is still running. I can find the mdf and ldf files on the old computer at
C:\Program Files\Microsoft SQL Server\MSSQL\Data

No such dir in new computer. Created dir and copied files into new dir. But I don't see the dataabases in the Enterprise Manager tree.
More importantly. I can see a SQl Server Group called SQL Server Group which contains other SQl Servers on the LAN. I created a SQL Server Group called Allen. Cannot create databases or Users on Allen. The user icon is greyed out and the if I rigth click on the server group I get
New SQl Server Registration
New SQL Server Group
Rename SQL Server Group

How do I create a server group on the local
computer?

Thanks.

Allen in Dallas

0
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.

 
LVL 5

Expert Comment

by:obahat
ID: 13818451
1. First create the SQL server group. If you already have one - you're fine. The group is only used for convenient display purposes of servers.
2. Assuming that you have set up the server already on your new machine - under the group - use the add server to add (map) the server to the group.
3. Once done - drill down into the server itself, rightclick the server, and press --> new database.
Then create the new database.

Once done, you can either DTS the content of the old database to the new one, or use Backup (of the old DB) and restore (to the new DB).

Hope this helps.

Omri.
0
 
LVL 5

Expert Comment

by:obahat
ID: 13818455
To your last question: When you register the new server - you can either add it to an existing group, or create a new group for it.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13818598
Hey Allen....you guys are still short/missing a DBA right?
0
 

Author Comment

by:Allen Pitts
ID: 13819143
Hello,
Yes, Kevin we are still missing the DBA, no budget for new hire.

I managed to add a seerver to the server group I created.
When I expand the server beneath the server group it says
(Connection failed, check SQL Server Registration Properties.)
When I click on the Users icon I get message
The SQL Server \\RGeorge2 is not known to be running. Are you sure you wish to connect?
When I click yaes it A connection could not be established.
So apparently the server is not running. I can't seem to find any place to make it start runnining. Any ideas?

Thanks

Allen in Dallas


Thanks.

Allen in Dallas



0
 

Author Comment

by:Allen Pitts
ID: 13819174
Hello

There is an option marked Start when I right click on the server name.
When I click Start I get
An error 1060 - (The specified service does not exist as an installed service) ocurred while performing this service operation on the MSSQLServer service.

Any ideas?

Thanks

Allen in Dallas
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13819187
Allen...the reason I ask is that I was going to offer up an hour or so of no-cost, no obligation consulting. :-)

To get the services running, first make sure they are not.  Go to the physical SQL Server you are trying to register/administer and look to see if the MS SQL Server Service is running.  If it is, you have a connection issue.  If not, start it and then try to connect again.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13819199
Sounds like you are trying to register a machine that does not have SQL Server installed, or the install is hosed
0
 

Author Comment

by:Allen Pitts
ID: 13819336
Hey Kevin,

I'm pretty sure that the SQL Server is installed because I can see other servers.
In ther services tray, lower right hand on the old computer there is an icon for SQl server which alows me to turn SQL Server on and off. There is no such icon on the new computer. How do I getr it to appear?

Thanks

Allen in Dallas
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13819365
Go to the SQL Server Program group and look for Service Control manager  (do this on the new computer)

If you are sitting at Workstation 'A', trying to register Server 'B', the fact that you can see 'C', 'D' and 'E' has no bearing.  You need to verify your install on B, then try to connect to it from other machines.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13819782



you must move all databases files (must to know structure of DB)
and use the article as instruction

http://www.databasejournal.com/features/mssql/article.php/2224361

BTW: do you have SQL server  DBs backups? If 'yes' - restore them on new server
0
 

Author Comment

by:Allen Pitts
ID: 13819902
Hello,
Where is the SQL Server Program Group?
I don't see it in Enterprise Manager or any where on the harddrive

Thanks.

Allen in Dallas
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13820291
Start>>Programs>>Microsoft SQL Server

I really think youdon't ahve SQL Server installed on the box you are looking at....
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13820430
looks like you just installed sql server client tools
But be sure - check running services - such as MSSQLSERVER service (if you have it you will find where is it located)
Start->Run-> type    ->  services.msc /s - see if there is the MSSQLserver service
0
 
LVL 8

Expert Comment

by:Julianva
ID: 13822103
Once you have SQL server running
then
you need to

you can copy your DB files pubs.mdf' /ldf files
to you folder then create a db with the same name

then run this script

Example

EXEC sp_attach_db @dbname = N'pubs',
    @filename1 = N'c:\mssql7\data\pubs.mdf',
    @filename2 = N'c:\mssql7\data\pubs_log.ldf'







0
 

Author Comment

by:Allen Pitts
ID: 13826575
Hello,
I did the Start->Run-> type    ->  services.msc /s
Can find no services called SQL or MSSQL or anything like that. I believe I have misnamed this question. I do want to add databases but first ineed to add a server
to the SQL Server Group.

On my old computer, called JDOSS2, in Enterprise Manager it looks like

Tree
Console Root
Sql Server Group
Local (Windows NT)
DalData (Windows NT)
DalSQL03 (Windows NT)

On my new computer, called APITTSSQL, in Enterprise Manager it looks like

Tree
Console Root
Sql Server Group
DalData (Windows NT)
DalSQL03 (Windows NT)
JDOSS2(Windows NT)

I could DTS the databases from my old computer to the new computer. I am trying
to add an entity (I guess a server) that will have the functionality of the Local (Windows NT) item. I can add Server Groups to Microsoft SQL Servers and I can add a database
to the existing servers but I can find no provision for adding the server. The old computer has the SQL Server icon in the srvices tray and the new one does. Also
I ran the mscservices /s on the old computer and there is
MSSQLSERVER
MSSQLServerADHelper
MSSQLServerOLAPService
So  I think I need to get the SQL Server service running. How do I do that?

Thanks.

Allen


   













 
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13826865
If you don't see it in the services list of the new machine, it has not been installed properly.  I promise.  You probably on ly have the client tools installed.

0
 

Author Comment

by:Allen Pitts
ID: 13827446
Kevin,

The OS on the machine is Windows 2000. I uninstalled an dreinstalled SQL Server. When you install SQL Server it says SQl Server not allowed on that operating system. I can't seem to tell the difference between the old system setp and the new system set up they are bith Windows 2000.

Allen
0
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 1000 total points
ID: 13827760
Win2K Server or Pro?  SQL Standard, Enterprise, Personal or developer?
0
 

Author Comment

by:Allen Pitts
ID: 13827895
Kevin,

Figured it out!! Was loading Standard edition on a Windows 2000 machine. Uninstalled Standard reinstalled Personsal Edition. Works like other machine.

Thanks.

Do you ever visit any of the SQL User groups?

Allen
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13827945
I will be at the North Texas SSUG tomorrrow night, Microsoft campus in Las Colinas....haven't missed in almost a year :-)
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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

834 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