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?

I would recommend making sure that your admin username and passwords are the same between the two servers. Do not import the system databases from the NT server.

I would use the Import/Export Tool to export the database from the NT server to import to the Win2K server.
========================================
Buh Bye!

Cheers,
Me

Hi Stealth,

I have now used the import/export tool and it imported the db successfully (along with 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?

I told you NOT to import the master table from the NT server, that is relevant only to each own's server. Meaning that it's not a good idea to copy system tables, including master, over to other database servers.

In terms of your ODBC settings, you need to use either the "name" of the new Win2K server or IP address. The relevance of NAME is that it is the NAME of your SQL Server and description doesn't really matter, just denotes what SQL Server it is.
========================================
Buh Bye!

Cheers,
Me

Hi Stealth,

When I looked in the original master there was data there that was relevant to the original database which is why I copied it. Should I uninstall SQL and re-install it doing a fresh import of the actual db only (without the master) or can I leave it the way it is? I tried changing the server name to the new test server name in the ODBC settings as you suggested but the error message as mentioned in my previous post still exists. It was doing this before I imported the master db. Do I need to edit the ODBC.INI file on the new test server? When I looked at it on the original DB server there were references to the original database. Do I need to edit it on the new test server?

What you need to do is the following:

1) Re-install SQL Server in your Win2K server
2) Then re-import only the database that you are testing with to the Win2K server

This is much quicker than providing instructions of how to manually fix the problem, which is related to the master system database in your Win2K server.
========================================
Buh Bye!

Cheers,
Me

Hello again Stealth,

I have now uninstalled and reinstalled SQL server 2000 on my Windows 2000 test machine. I am now about to reimport only the actual database and not any of the default databases. I have a query about the Data Transformation Services. When I ran it last time I chose the first option which was:

1. Copy tables and views from the source database

I am now beginning to wonder whether I chose the wrong option which could be the cause of my problem. Should I have chosen option 3 which was:

3. Copy objects and data between SQL server databases.

What is the difference between these two options? Thanks.

If your database objects like user-defined objects (based on same user and permissions), stored procedures, etc. are the same between the two databases in the different servers, then it's not a big deal...but for me, I have my local dev environment set-up differently than my client's dev and production environments, so any time I EXPORT or IMPORT databases, I use Option 3, with the settings shown in the attachments.

Option 1 will simply export the table structure and data from one database to another, it doesn't copy user-defined objects, stored procedures, indexes, etc. Using the 3rd option is more robust and will reduce the amount of re-configuration you have to do on DATABASE B.
========================================
Buh Bye!

Cheers,
Me

Hi,

Is it essential that no one is using the database when using the Data Transformation Services ? I was wondering if I could run it during the day when users were accessing it. If I could run it during the day would the performance hit be too much for the server? I tried to schedule the DTS to run last night when no users were on,but it didn't run. there was an error message in the application log in event viewer that simply said "Status failed.Invoked on....The job failed".

I decided to make sure that everyone was logged out of SQL before running the DTS. I have now imported the actual database again.

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!

Yes, you need to make sure that no users are logged into the SQL Server and also make sure that you have the SQLAgent running to run scheduled tasks (this service should be set to Automatic).

To avoid us from continuing to go around and around on this issue, it would greatly help if you could make screenshots of your ODBC configuration screens for this datasource you are attempting to create. I am really at a lost without visually see what all your configurations are, because the error indicates that you are not configuring the ODBC connection correctly.

So, please make screenshots of your WinNT and Win2K ODBC connections and also ODBC connections in the server where you are connecting to either of these servers.
========================================
Buh Bye!

Cheers,
Me