Importing a database

Hi,

I have SQL Server 2000 running on an NT server.

I have set up another server also running SQL Server 2000 under Windows 2000.

I want to use the 2000 server for test purposes. I have a backup of the existing database on the NT server.

How do I go about importing the backup from the existing database on the NT server into my test server? Do I need to import the following databases ?

master
msdb
actual database

I have tried starting SQL in single user mode and trying to use the restore command to import the master database but this fails. What is the correct procedure for importing a database?

All you have to do is to import the new actual database... you can leave the others alone. Just turn single-user mode on and lookup how to unlink and take a database offline. When you unlink it, find the data file on disk. Copy that file to the new server. Now go to the new server, create the database and "link" the file you just copied. It's an easy way to copy databases between servers. Of course, it only works if no one is using the server!
-Dave

I usually just use DTS (Data Transformation Service) and copy objects to an empty db on the target server.

I'm pretty sure but not positive that you can just restore a SQL7 backup into SQL2000, that would be easier yet.

Yes. It's possible to restore a SQL7 backup to a SQL2000 database but not the other way around.

Please let us know what you're problem is when you say it fails. Import/Export can be finicky and the errors are usually a good hint as to why. But i couldn't agree with Doug G more, DTS packages are definately the way to go in my opinion.

Hi Everyone,

I have now used the import/export tool and it imported the db successfully (along with the master). Thanks for that.

I am now trying to connect to the "copy" database that I have just imported but I am now having further problems. In the original database I connect via ODBC settings. The original connection has an entry in the system DSN section. It is configured with a name, decsription and server with SQL server authenitication......using TCP/IP and I have set the default database to the required database. I thought that I would be able to connect to my new test server that has the "copy" database just by changing the name of the server but when I do this I get the following error.

'Data source name not found and no default driver specified'

What else do I need to configure in order to connect to my test database on my test server. What is the relevance of the name and description that is in the original ODBC settings?

well if this is sql 7 and 2000 you ported from to respectivly, i don't think you can just change the name of the ODBC. And you wouldn't want to anyways because other applications using the old ODBC might be using that name to connect to the SQL7 server and you'll be inadvertanly switching them to your test server, but in any case

if you go to system DSN and click the add button, you'll be able to configure a new DSN for your new SQL2000 server, or just delete the old one and remake it for your test server if you plan is to change all old connections to the new test DB. You should be able to select the SQL driver out of the list and continue on as is.

The name field, is what you will say in applications to connect using this ODBC. If i call the name "Black_Sabbath" in my open statements i'd say Conn.open("Black_Sabbath", "username", "password")
The description isn't used, you can type whatever you want here to help you remember what it's being used for specifically, i usually just give it the name of the Database so i know which DB this connects to, it's actually optional....

Hi Unatratnag,

The version of SQL is the same on Win NT as it is on Win 2000 i.e MS SQL Server 2000. Is SQL ver 7.0 the same as SQL ver 2000?

I have changed the server name in the original ODBC connection to the new test server name but I am still getting the error messages mentioned previously.

yes, they are different


I don't know what to tell you but to at least try my advice and completely create a new ODBC connection, or create a connection string from scratch....
I really really really think that's the problem....

Does the "test connection" pass in the ODBC manager for your DSN?

If it does, then you may have some typo or error in whatever code you're using to connect to the db.

I have tried deleting the old ODBC connection and creating the new one from scratch with the name of the test server instead of the original server.I still receive the following message:

"Data source name not found and no default server specified"

My test data connection failed but I am not concerned about that as the last ODBC connection which worked with the original server also reported a failed test!

well that's creepy, do you think it might be a WINs problem where the server thinks its still up but it's not?