Automating Restoring a Sql 2000 database to Sql 2008

Question

Automating Restoring a Sql 2000 database to Sql 2008

extremenovice

SSCrazy

Points: 2118
More actions
July 14, 2010 at 4:49 am

#226823

I am trying to automate a restore of a sql 2000 database on server A to a sql 2008 instance on server B. I have tried copy database wizard so that it could be scheduled on a daily basis, but the errors i have received lead me to believe that its not viable due to incompatibility reasons.
Firstly, is this possible, as everything i have read has stated it is not. I have restored the database successfully manually. I just need to have a schedule restore take place daily.
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

sticking with an actual RESTORE command is the best solution i think. you need to time things correctly, because you need to coordinate the following steps:

1. a scheduled backup on the SQL 2000 machine puts a copy in a specific folder each day.

2. copy the backup fron that server's filesystem to the SQL2008 machine.

3. a scheduled restore on the sql 2008 machine restores the database.

4. repeat for each database that needs to be copied over nightly/daily.

what have you tried so far, or at least what is already in place? is there a regualarly scheduled backup of the current 2000 database?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 2

Also, it's worth noting, there are pretty vast differences between 2000 and 2008. You need to be sure that your 2000 database is compatible with 2008. If you have not run it through the SQL Server 2008 Upgrade Advisor, I'd strongly suggest doing that as a first step.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

If you decide to do the restore make sure you build in a step to update all statistics on the database. Even better would be to rebuild all the indexes.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

extremenovice SSCrazy Points: 2118 More actions · Answer 4

Hi All, thanks for your replies, they are much appreciated.

There is a backup schedule on the 2000 server, and it is here i want to setup a job to copy the backup over to the other 2008 server into a folder. Then i want to create a job on the 2008 server to restore that backup file on a daily or wkly basis. The frequency has not been decided on yet.

I would then implement the update stats, integrity checks and so forth in the plan on the 2008 server once the above task is successful.

My main aim is if you could help me understand the copy backup & restore method and how i can actually achieve the method of copying it over to the server 2008 to then carry out the restore?

Thanks in advance.

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 5

There are multiple ways you could do that. Personally, I'd go with using PowerShell from the 2008 server. You can issue file commands to copy the file and then issue SQL commands to run the restore database operation. However, learning PowerShell and SQL restore at the same time might be a bit daunting, so another option is to use a simple command line interface, use normal DOS commands to copy the file and then use sqlcmd, a command utility for running scripts, to run the restore operation. You can set the restore operation as a stored procedure and have sqlcmd call that proc, or you can just use a script. As far as the basics on restoring database, this article [/url]should help, although it is targeted at 2005, the basics are the same.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 6

If the backup file is in a location on the network available to both servers, you could do what we do for month end jobs -- add a step to the backup job on SQL 2000 that connects to the SQL 2008 box via a linked server (you'd need to make that) and fires off the restore job on the remote server (i.e. msdb..sp_start_job). That way you are guaranteed to have the restore start after the backup is complete and you maximize the time you have to do things in the restore job like reindexing, checkdb, etc.

extremenovice SSCrazy Points: 2118 More actions · Answer 7

Hi, apologies for my late response to your additional advice.

Grant, I think you're right about learning about Powershell and sql restore being a daunting task. I really want to try and keep this as simple as i can to be honest, so i will try and give the latter option a go and see how that goes along with Jeff's suggestion. I am a little more familiar with these two options than i am with Powershell. Thanks for the article, it is very thorough and greatly informative.