Solved

SQL

Posted on 2006-07-06
23
285 Views
Last Modified: 2010-03-19
Hi Experts,

I hope somebody may be able to help me with this one, heres the scenario surrently i have 2 apps running on different SQL 7 servers. As a method recovey i want to place these two apps on a backup server away from the main LAN. So basically i wish to know if it is possible for me to copy these two SQL based apps and put them onto a single server, and if so how would i do it?

I suppose really what i want to do is to have the two apps on a standalone server somewhere so in the event that i need to be up and running quickly i could switch on my backup server, restore the app data and hey presto away we go.

Anybody any ideas, as you may have guessed i am not that familar with SQL


Cheers
0
Comment
Question by:georgemildred
  • 12
  • 11
23 Comments
 
LVL 9

Expert Comment

by:jjoseph_x
Comment Utility
That depends on the applications.

They'll likely either use an OLEDB or ODBC connection.  If it's OLEDB the application would use a connection string in ADO to connect to the database, so the application would prompt you for the servername/ipaddress and the username/password during the installation.

If it's an ODBC connection, you either need to create a system or machine DSN or, again, the application will prompt for the servername/ipaddress and username/password.

0
 

Author Comment

by:georgemildred
Comment Utility
so is it a simple matter of copying the app data, and running its setup programs?

what about the SQL setup how do i transfer it?

Cheers
0
 
LVL 9

Expert Comment

by:jjoseph_x
Comment Utility
Do you also want to run SQL server on the new machine?  Then you'd just have to install SQL Server (and the necessary service packs), backup the existing databases (you can do that from the SQL Enterprise Manager... just right-click on the database, select All Tasks->Backup) then restore the database to the new server.

You'll also have to make sure that you setup the same security settings (users, roles)... but you can check your existing SQL Server and re-create those on the new one.

0
 

Author Comment

by:georgemildred
Comment Utility
The new machine will indeed need to run SQL and will have a different server name, will this cause the backup and restore you mentioned any problems? the version of SQL is 7 by the way

Cheers
0
 
LVL 9

Expert Comment

by:jjoseph_x
Comment Utility
Nope, the backup/restore doesn't care what the server name is (people often have to move data from SQL Server to another and this is usually how its done).
0
 

Author Comment

by:georgemildred
Comment Utility
excellent i shall try this over the weekend and report back

cheers

0
 
LVL 9

Expert Comment

by:jjoseph_x
Comment Utility
If you have any problems, let me know and I'll try to help.
0
 

Author Comment

by:georgemildred
Comment Utility
your very kind cheers
0
 

Author Comment

by:georgemildred
Comment Utility
ok, i did the backup as you kindly suggested, it completed fine, i then attempted to restore the backup onto the new sql server, it comes up with an error message "The file whatever.mdf cannot be used by RESTORE consider using the Eith MOVE option to identify a valid location for the file.

ODBC SQLSTATE 42000

Any ideas?

0
 

Author Comment

by:georgemildred
Comment Utility
hold the phone, its restored ok, happy days your a star cheers, i was getting file locations wrong.

should i now just be able to point the client program to the new sql server?

0
 
LVL 9

Expert Comment

by:jjoseph_x
Comment Utility
Sorry for the late reply... but yeah, you should just have to install the client application and you should be fine.
0
Free camera licenses with purchase of My Cloud NAS

Milestone Arcus software is compatible with thousands of industry-leading cameras for added flexibility. Upon installation on your My Cloud NAS, you will receive two (2) camera licenses already enabled in the software. And for a limited time, get additional camera licenses FREE.

 

Author Comment

by:georgemildred
Comment Utility
Hi,

I hjave managed to successfully restore the database ok, but it has not restored any of the user and when i goto try and add a database user within SQL it tells me that the user already exits inside the database.

any ideas
0
 
LVL 9

Expert Comment

by:jjoseph_x
Comment Utility
Do you mean the logins under the Security tree item or do you mean that users for that particular database?

If you mean the latter, you just need to go to logins and give the users there permissions for the required databases.
0
 

Author Comment

by:georgemildred
Comment Utility
hi jjoseph its actually both,
0
 

Author Comment

by:georgemildred
Comment Utility
there are no users in the logins part
0
 
LVL 9

Expert Comment

by:jjoseph_x
Comment Utility
None?  You should at least have sa and BUILTIN\Administrators.

Are you adding domain users or just SQL users?
0
 

Author Comment

by:georgemildred
Comment Utility
sorry yes there is sa and admins alright, i am adding users at the moment

cheers


if i try and add a user it keeps saying user exits?

very strange
0
 
LVL 9

Accepted Solution

by:
jjoseph_x earned 250 total points
Comment Utility
Are these SQL Server users or Domain Users?

If they're SQL Server users, this might help:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp
0
 
LVL 9

Expert Comment

by:jjoseph_x
Comment Utility
Actually, it might be even easier to go to the sysusers table in that database and just delete the problematic users and create them again.
0
 

Author Comment

by:georgemildred
Comment Utility
if i go into the sysusers table and hit delete it says that the ad hoc ..... needs to enabled by the administrator

cheers
0
 
LVL 9

Expert Comment

by:jjoseph_x
Comment Utility
Hmmm.

Maybe a simpler solution would be to use:

DROP USER.

In the SQL Query Analyzer try this:

USE myDatabase
DROP USER ProblematicUser;
GO

0
 

Author Comment

by:georgemildred
Comment Utility
cheers i will try this today, it just seems to be that it backups everything ok and the restore seems to work ok but there its just not restoring the users
0
 
LVL 9

Expert Comment

by:jjoseph_x
Comment Utility
If that doesn't work, then using sp_change_users_login with the autofix option should definately solve the problem by re-mapping database users to SQL Server logins.
0

Featured Post

New My Cloud Pro Series - organize everything!

With space to keep virtually everything, the My Cloud Pro Series offers your team the network storage to edit, save and share production files from anywhere with an internet connection. Compatible with both Mac and PC, you're able to protect your content regardless of OS.

Join & Write a Comment

This is an article about my experiences with remote access to my clients (so that I may serve them) and eventually to my home office system via Radmin Remote Control. I have been using remote access for over 10 years and have been improving my metho…
Data center, now-a-days, is referred as the home of all the advanced technologies. In-fact, most of the businesses are now establishing their entire organizational structure around the IT capabilities.
Viewers will learn how to connect to a wireless network using the network security key. They will also learn how to access the IP address and DNS server for connections that must be done manually. After setting up a router, find the network security…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now