Solved

Incrementally Exporting data from SQL server 2000 to SQL 2005

Posted on 2007-03-28
8
175 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
  • 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 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
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

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 …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

911 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

21 Experts available now in Live!

Get 1:1 Help Now