Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL

Posted on 2006-07-06
23
Medium Priority
?
307 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
ID: 17056205
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
ID: 17056796
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
ID: 17058173
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:georgemildred
ID: 17058244
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
ID: 17058757
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
ID: 17058799
excellent i shall try this over the weekend and report back

cheers

0
 
LVL 9

Expert Comment

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

Author Comment

by:georgemildred
ID: 17059547
your very kind cheers
0
 

Author Comment

by:georgemildred
ID: 17064686
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
ID: 17064753
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
ID: 17065046
Sorry for the late reply... but yeah, you should just have to install the client application and you should be fine.
0
 

Author Comment

by:georgemildred
ID: 17082200
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
ID: 17082378
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
ID: 17082465
hi jjoseph its actually both,
0
 

Author Comment

by:georgemildred
ID: 17082532
there are no users in the logins part
0
 
LVL 9

Expert Comment

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

Are you adding domain users or just SQL users?
0
 

Author Comment

by:georgemildred
ID: 17082676
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 1000 total points
ID: 17082842
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
ID: 17082875
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
ID: 17083074
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
ID: 17084462
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
ID: 17128828
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
ID: 17129658
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article, I am going to show you how to simulate a multi-site Lab environment on a single Hyper-V host. I use this method successfully in my own lab to simulate three fully routed global AD Sites on a Windows 10 Hyper-V host.
This program is used to assist in finding and resolving common problems with wireless connections.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

963 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