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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.