SQL

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
georgemildredAsked:
Who is Participating?
 
jjoseph_xConnect With a Mentor Commented:
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
 
jjoseph_xCommented:
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
 
georgemildredAuthor Commented:
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
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.

 
jjoseph_xCommented:
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
 
georgemildredAuthor Commented:
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
 
jjoseph_xCommented:
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
 
georgemildredAuthor Commented:
excellent i shall try this over the weekend and report back

cheers

0
 
jjoseph_xCommented:
If you have any problems, let me know and I'll try to help.
0
 
georgemildredAuthor Commented:
your very kind cheers
0
 
georgemildredAuthor Commented:
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
 
georgemildredAuthor Commented:
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
 
jjoseph_xCommented:
Sorry for the late reply... but yeah, you should just have to install the client application and you should be fine.
0
 
georgemildredAuthor Commented:
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
 
jjoseph_xCommented:
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
 
georgemildredAuthor Commented:
hi jjoseph its actually both,
0
 
georgemildredAuthor Commented:
there are no users in the logins part
0
 
jjoseph_xCommented:
None?  You should at least have sa and BUILTIN\Administrators.

Are you adding domain users or just SQL users?
0
 
georgemildredAuthor Commented:
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
 
jjoseph_xCommented:
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
 
georgemildredAuthor Commented:
if i go into the sysusers table and hit delete it says that the ad hoc ..... needs to enabled by the administrator

cheers
0
 
jjoseph_xCommented:
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
 
georgemildredAuthor Commented:
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
 
jjoseph_xCommented:
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
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.

All Courses

From novice to tech pro — start learning today.