question

Jason Crider avatar image
Jason Crider asked

Nightly Restore from SQL 2000 to SQL 2005

Let's say you have a situation where Business Objects is hammering your OLTP system and you have brought up a second box to serve as the OLAP side of things. Due to constraints, a datawarehouse won't be happening soon, so you need something on the OLAP side of things for reporting to run against.

What method would you use to get the data over to the new server? If the data needed to be refreshed every 12 hours instead of every 24 would that change the method?

sql-server-2005sql-server-2000olapoltp
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

As with many things, "It Depends". Primarily it depends on the amount of data being moved and the percentage of the SQL Server 2000 database that it represents.

If the data is relatively small or represents a small portion of the SQL Server 2000 database, then Alvin's suggestion of SSIS is probably the best way to go.

On the other hand, if the database is relatively large and the amount of data you need to move represents most of the SQL Server 2000 database, then you can write a script to take a backup on the SQL Server 2000 database, copy it, and restore it on the 2005 server. This works especially well with a tool that will compress the backup up such as Red Gate SQL Backup 6.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That's what I've ended up doing for now. Before SSIS came along I had attempted to do this through stored procs and had a little more difficulty. I've got about seven databases restoring nightly and being set to read only so I'm good to go for now. I want to make it more dynamic but I just don't have the time for that right now.
1 Like 1 ·
Alvin Ramard avatar image
Alvin Ramard answered

I would use SSIS onn the SQL 2005 box to move and manipulate the data, regardless of whether it's every 12 or 24 hours.

Do you have to copy everything? Or can you do differential updates? That would depend on the structure of the data, or the "information" in the data.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

I worked on a reasonably large scale system in which we rolled our own solution, because the back end was responsible for configuration, and the front end was responsible for transaction processing.

The solution picked up rows from designated tables at each end, and performed the relevant inserts/updates/deletes on the back end. It was a lot of work, but it was good because we had real time replication with fine control and the logging we wanted. This was before SSIS was available, so you could probably do something pretty similar using that, as long as you use a good method of tracking which rows have had changes made to them...

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.