Quickest way to automate restore?

[bessebo]

We are running SQLServer 2000 and I need to set up an environment that will allow users to report off of a restored database (from the previous day's full backup). I am interested in the best method in which to do this as I want this to be automated so that when users get into work in the morning they can run reports against this non-production database.
I'm sure that there are many of you out there who do this and I am wondering how you do this easily. Oh, by the way, I want to restore the SQL2000 backup to a server running SQL2005. Any help would be appreciated.

Regards,
Bessebo

 

[ptheriault]

I would just set up snap shot replication or transactional replication if you want to report on real time data. How large is your db. It would be a pain to have to copy a large db to another server. But here are the steps.

Using SQL Agent
1 create a job to backup your database. step 2 of that job would be to zip the db.bak file if it is large.
Then add step 3 to copy or xcopy the .zip or .bak file to the other server.

Then on the 2005 server.
Create a job with the following steps.
1. kill any active user sessions
2. unzip .bak file
3. restore db

This isn't that hard to set up and I've done this in the past but the best and easiest method is some kind of replication.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bessebo]

There are actually two databases. One is 58GB and the other is 7GB. No to be disrespectful but I understand the process involved but I was more interested in automating it. I also think that there must be some type of purchased application/utility that will facilitate this process. I really have no interest in replication as I am concerned over performance hits for every record transacted in our production ERP database to be duplicated on another server in our network. I do like the backup and restore functionality to another server but I was hoping that there may be packages out there that would make this process easier and quicker.

Regards,
Bessebo

 

[ptheriault]

The steps I described using the sql agent would automate this for you. The SQL Agent would perform all the tasks outlined from a scheduled job. The only thing you would have to do is set up the job.

Look into snapshot replication in BOL.

Snap shot replication runs on a schedule you set up. So if you only want it to run once a day at night then you would schedule it for that.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bessebo]

Good point. So I assume that we can take a snapshot from SQL2000 that will automatically update a SQL2005 database? I am wondering how long this would take?

Regards,
Bessebo

 

[ptheriault]

So I assume that we can take a snapshot from SQL2000 that will automatically update a SQL2005 database?

yes you can.

I am wondering how long this would take?

That's a good question. That is going to depend on your network, switches and pipe between the servers.

- Paul
- If at first you don't succeed, find out if the loser gets anything.