Incrementally Exporting data from SQL server 2000 to SQL 2005

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.
LVL 1
LawCentralAsked:
Who is Participating?
 
imran_fastConnect With a Mentor Commented:
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
 
imran_fastCommented:
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
 
LawCentralAuthor Commented:
I think Solution 1 is the right candidate. Can you please explain me how can I automate this?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
imran_fastCommented:
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
 
LawCentralAuthor Commented:
Imran,

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

Thanks, HM
0
 
imran_fastCommented:
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
 
LawCentralAuthor Commented:
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
 
LawCentralAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.