SQLTeam.com | Weblogs | Forums

How to copy complete SQL database, users etc to new machine without sa password and connect databases?

sql2008r2

#1

Hi,

I am a total newbie with very little knowledge. Our work has halted due to hardware issues in our old windowsXP PC. We had paid close to $2000 for our ERP software but the company isn't providing tech support anymore!

I want to replicate SQL 2008 R2 installation and settings etc to another windows PC. I do not have the sa password, but the remember password tick box is checked so I'm able to login in to SMSS and see our attached database file. I unable to find any database access settings in our ERP software settings, to which I have access to.

I can't risk changing sa password and then detach database, as I do not know if that needs to be updated in our ERP software too. Once I change sa password in SQL, I'm afraid that I won't be able to revert to existing password, as I don't know it.

The dataconfig file for ERP reads like this -
[Database]
ServerName=SERVER-DELL
ServerIP=SERVER-DELL
ServerPort=
InstanceName=DEFAULT
SAPassword=

On our new PC, I have installed SQL 2008 R2, created default instance (database attached to MSSQLSERVER on old PC). Took our database file offline from old PC, detached and attached to MSSQLSERVER instance on new PC. (changed sa password to blank via SMSS)
But still my ERP throws and error - unable to access database path - master. Check login credentials.

I can do as instructed, but afraid of messing the existing setup in the old PC.
Please help, I'm on the edge here. All forms of help are highly appreciated.


#2

If you need to do that again (e.g. because the old server is still in use, and the data being udpated, and you will need a fresh copy of the data) I recommend that you use Backup (on the Source/Old server) and Restore (on the Target/New server). When you restore be careful to make sure that you force the Data (MDF) and TLog (LDF) files to be in "suitable" folders / drives.

Make sure you have an automated, regular, backup scheduled

There may be setting that need to be made the same - e.g. Collation ... so there may be unknowns in that regard. One option might be to make a fresh install of SQL and then deliberately copy the old MASTER database files over the top of the new one. You'll need identical version / service pack levels for that to be safe. It would like bring with it Config Settings which might be important.

But of course your ERP might have stuff in Files, Registry and back-side-of-envelope :frowning:

Create yourself a new user, whilst logged on as SA, and set it to SysAdmin. Then try to log on with that UserID / Password. If that works OK that will give you a route to get into SQL (I suggest that you then use the new UserID instead of the SA one).

I would hope that the ERP software is not trying to connect as SA ... so you would also need to migrate the Logons / Roles etc. that it needs. In part they are in the database, but they need re-synchronising with the new SQL installation. That might be giving you trouble connecting

Dunno if that is the logon synchronization issue, or something else, but it is worrying me that the error message specifically mentions "master"