Backup and restore in 2000

Question

Backup and restore in 2000

Vidya-389933

SSC-Addicted

Points: 439
More actions
February 26, 2009 at 5:52 am

#135118

Hi,
Is there a way to restore only the data and not the structure of the database (table structure, stored procedures, etc)?
My situation is that we are moving the database to a new machine because the old one is on it's last legs. There are many stored procedures, DTS packages, etc which have hardcoded the name of the old server. Ideally, I would like to copy over a backup of the old database to the new machine, restore it and then make changes to make the server name dynamic, etc. Then test it and make sure everything runs fine. Then, during the production turn over weekend, just copy over the data in the tables/views. This way, the changes made in the stored procedures, packages, etc will not be overwritten.
Is this even possible?
Thanks,
VP

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

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

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

You can restore to your new server, make all your changes. Before you copy over the production using the Import / Export wizard or SSIS you can truncate all the tables (on the new server) so that you would have empty tables.

Does that help?

David

@SQLTentmaker

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

tosscrosby SSCoach Points: 18045 More actions · Answer 2

Vidya (2/26/2009)
Hi,
Is there a way to restore only the data and not the structure of the database (table structure, stored procedures, etc)?
My situation is that we are moving the database to a new machine because the old one is on it's last legs. There are many stored procedures, DTS packages, etc which have hardcoded the name of the old server. Ideally, I would like to copy over a backup of the old database to the new machine, restore it and then make changes to make the server name dynamic, etc. Then test it and make sure everything runs fine. Then, during the production turn over weekend, just copy over the data in the tables/views. This way, the changes made in the stored procedures, packages, etc will not be overwritten.
Is this even possible?
Thanks,
VP

What I've done many times in the past, IF you're retiring/re-purposing the old server is to rename the new server to the original server's name and also change the IP address to the original server's IP, once of course you've taken the old machine off the wire. SO much easier. And if you have client or application machines where there are ODBC drivers involved, you don't have to change a thing. Works likes a charm.

-- You can't be late until you show up.

Vidya-389933 SSC-Addicted Points: 439 More actions · Answer 3

Unfortunately, renaming the new server to be the same as old won't work. Both will be online for a while until all testing is done. We'll only have a weekend to cut over production. Also, there are new naming conventions that need to be followed for naming the servers. We did think of that solution, but unfortunately had to drop it.

Also, when I have this opportunity, I'd like to make the code more portable by not having hard coded names in the system.

Thanks for the suggestion though 🙂

Vidya-389933 SSC-Addicted Points: 439 More actions · Answer 4

David,

The way I'm doing this is by copying over the database backup of the old server on to the new machine and then restoring it.

Are you saying I should do it that way the first time, make changes as required to the code, then use some other way - Import/Export or SSIS - to just copy over the data and not the structure?

I hadn't thought of doing it that way. Thanks. I'll mention it to our DBA and see what he'll say. It's been very hard to get any response out of our DBA which is why I had to look for answers outside the company 🙁

Thanks,

Vidya

tosscrosby SSCoach Points: 18045 More actions · Answer 5

Vidya (2/26/2009)
Unfortunately, renaming the new server to be the same as old won't work. Both will be online for a while until all testing is done.
Also, when I have this opportunity, I'd like to make the code more portable by not having hard coded names in the system.
Thanks for the suggestion though 🙂

I have brought up the new server as ServerB, did my migration and testing, then during the migration to production shutdown ServerA, renamed and re-IP'd ServerB and we were off and running. Always worked for me. However, I did not have a lot of hard-coded information within my procsor DTS. Also, I do agree, the way to go is to definitely get rid of that bit of hardcoding, wherever possible. What I would suggest if you cannot do the rename option is to get everything coded the way you want, test it, script both the procs and DTS packages either to a file or into another database, restore prouction in it's entirety and run the saved scripts on the restored database. This way, what has already been tested is put in place, with hopefully no surprises on Monday morning. Good luck.

-- You can't be late until you show up.

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

Make a DNS entry to route all traffic from your old server to your new server. Then if you miss changing anything, it will still work. You can fix connections after migrating without panicking at go-live.