moving an SQL Server 2k sp2 dBase to sp3... 1

[groovin]

hi, i have an sql server 2k sp2 running on NT4 with databases I'd like to move to an sql server 2k sp3a machine running win2k3. yes, shame on us for not staying current with the service packs, but we have our reasons and our server is completely cut off from the world. anyways, i was wondering what the best method to copy this database would be? we would prefer to leave the old NT server running the database until we finish testing this new machine. i know sql has its own backup/restore apps and we are running brightstor to back up the database... or i can just stop the sql service and copy the sql data directly over to the new server...

also, will there be any problems moving a db from a sql server 2k sp2 to an sp3 server?

thanks!

 

[SQLBill]

I believe you have to move it to a system with the same SP level. I could be wrong. Can you download the SP3a to a machine, copy it to disk, apply it to the old system, and then move the database?

Anyways, for moving it....I would stop the services, COPY the .mdf and .ldf files, then move the copies to the new server and attach them.

-SQLBill

Posting advice: FAQ481-4875

 

[groovin]

thanks for the reply... i would like to go about this by causing as little disruption to the existing NT server as possible... i havent applied the sp3 patch yet to the new server... the only reason why i was going to was because win2k3 warns it only supports sp3. i guess i could try moving it over and then attaching and then upgrading to sp3...

 

[SQLBill]

Yes, you could try that. When you install SQL Server 2000 on a Win2k3 box, it will give you a pop-up error message that you need to have SP3a. Just click Continue. Finish everything to install it, add your database and then install SP3a (or SP4). Make sure your SA account has a strong password.

-SQLBill

Posting advice: FAQ481-4875

 

[groovin]

as for moving the databases, just curious since i am still really new to SQL Server, can i use the backup/restore in sql server? so run a backup of the db and transaction log, move them over to the new machine, and then restore?

when a full backup is done, am i correct in thinking it is a 'hot' or online backup?

thansk again!

 

[SQLBill]

When you do a SQL Server backup, yes it's on line while your users are using the database.

You have two ways to move a database.

1. Do a backup and restore of the database and the log file.

2. detach the database (puts the database offline), copy the .mdf and .ldf files (and any .ndf if you have them). Move the copy to the new location. Attach the database.

-SQLBill

Posting advice: FAQ481-4875