Incrementally Exporting data from SQL server 2000 to SQL 2005

Posted on 2007-03-28
Last Modified: 2013-11-30
I need to export data from SQL server 2000 to SQL server 2005. These server are running on different graphical locations. The exdate needs to be done on a daily basis. The data to be exported will contain following objects:
1. Tables
3. Stored Procs.
4. Defaults
5. UDFs

Following technologies didnt work for me:
1. Replcation- This didnt work as it locks the table on SQL server 2000. This database requires a lot of DML procedures running. In addition new tables and objects are constantly added. Replication would not accomodate any of this.
2. Log Shipping: I understand that log shipping is not possible from SQL server 2000 to SQL server 2005. This was then confimed on the blog of SQL Server team.
3. DTS: I cannot run a DTS procedure from SQL server 2000 to SQL Server 2005. The error I get is "unable connect to this server you must use sql server management studio or sql server management objects". I installed SSMO on SQL server 2000 but it didnt succeed at all.

I welcome your suggestions and probably solutions to my problem.
Question by:LawCentral
  • 4
  • 4
LVL 28

Expert Comment

ID: 18806447
make a schedule with these steps

1. backup database on server 2000 .
2. copy the backup file on sql 2005.
3. Restore it on sql 2005.

Solution 2
Use merge replication schedule it in off peak time. it will lock only for the first time when it generates the initial snapshot after that it will apply changes but new tables and view will not be moved automatically.

Author Comment

ID: 18806466
I think Solution 1 is the right candidate. Can you please explain me how can I automate this?
LVL 28

Expert Comment

ID: 18806739
step 1

Backup database on Sql Server 2000
 i.e. on sql 2000 take backup of the database at off peak time for example 12:00 AM using sql server 2000 jobs. Place the backup on specific drive "C:\SQLBACKup2000\Backup.bak"

Step 2.
Copy the backup file on sql 2005.
make window schedule at 1:00 AM to move or copy the file from sql 2000 server C:\Backup2000\Backup.bak to Sql 2005 Server on location C:\Backup2005\Backup.bak

3. Restore it on sql 2005.
Make Sql server 2005  schedule for restore the file on sql server 2005 from location

Author Comment

ID: 18813222

Could you please elaborate how to perform step 2 and 3? Could you please supply some sample scripts?

Thanks, HM
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

LVL 28

Expert Comment

ID: 18828111
step 2
Create a batch file which will copy the backup file created on your database server to the sql 2005 server.

Step 3
On the Sql 2005 server create an empty database.

after that right click on it all task restroe and give the path of this backup and then check box schedule it.

Author Comment

ID: 18834331

With all due respect you are pointing "What" to do here. I know "what" needs to be done. However, I am more interested in "HOW". Can you please elaborate on how?

I am currently writing an app which will run as a Windows service and FTP any new backup files created. This I believe will take care of the step 2.

However, Step 3 needs to be automated and I will appreciate if I had some script to do so.
LVL 28

Accepted Solution

imran_fast earned 500 total points
ID: 18842024
For Step three

You need to copy the backup on the Destination Server which is step 2. Once you have the backup file on the location then you have to create a job in sql server for restore.

Open Enterprise Manager on sql server expand
Management --> Sql Agent --> Jobs
there create a new job in steps click new and add this cammand
   FROM Disk = 'C:\backupfilelocation'

Make schedule from schedule tab. Make sure that sql server agent is running and this job will run at the scheduled time to restore yourdatabse.

Hope this is clear.

Author Comment

ID: 19028895

I will award you the point for your help. However I ended up upgrading the SQL 2000 db to SQL 2005 and thereafter it was a breeze.


Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MSSQL 2014 Query Synthax 8 38
SQL Date Retrival 7 30
Update in Sql 7 8
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now