Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Incrementally Exporting data from SQL server 2000 to SQL 2005

Posted on 2007-03-28
8
Medium Priority
?
183 Views
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
2. Views
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.
0
Comment
Question by:LawCentral
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 28

Expert Comment

by:imran_fast
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.
0
 
LVL 1

Author Comment

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

Expert Comment

by:imran_fast
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
C:\Backup2005\Backup.bak
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 1

Author Comment

by:LawCentral
ID: 18813222
Imran,

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

Thanks, HM
0
 
LVL 28

Expert Comment

by:imran_fast
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.
0
 
LVL 1

Author Comment

by:LawCentral
ID: 18834331
Imran,

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.
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 1000 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
RESTORE DATABASE DatabaseName
   FROM Disk = 'C:\backupfilelocation'
GO

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.
0
 
LVL 1

Author Comment

by:LawCentral
ID: 19028895
Imran,

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.

Thanks,
Hemal
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

730 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