December 19, 2007 at 2:59 am
We are about to migrate from sql 2000 to 2005. Management want the minimum downtime for our production db (40Gb) and so are pressing us to use attach / detach rather than backup / restore.
Are there any issues with copying databases across from 2000 to 2005 using detach / attach?
December 19, 2007 at 3:04 am
While performing Detach\Attach operation the default db's for your logins might get misplaced, so pls take a list of default dbs before detaching the dbs and compare it after attaching them.
[font="Verdana"]- Deepak[/font]
December 19, 2007 at 3:42 am
Attach/Detach is definitely faster than backup/restore. The only thing I can think of is logins (but i guess this would happen with backup/restore too). Again, you can use the SSIS transfer login task to fix logins.
I hope you have already run your old database through upgrade advisor and fixed potential issues.
December 19, 2007 at 4:23 am
-As long as you dont forget to migrate and sync the users, it should be OK.
-Also keep in mind te perform full db maintenance after the migration.
(rebuild indexes, sp_updatestatistics, dbcc updateusage(0) with count_rows)
And off course schedule the backup jobs !
As mentioned by others, always start with the SQL2005 upgade advisor.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 20, 2007 at 3:13 am
if i have a SQL 2005 server ready and i have enough space for duplicate this database, i prefer use restore fullbackup and restore with transaction log + all the step mention as above. It take me less then 10 min for migration or move database from one server to other
December 20, 2007 at 8:18 am
Why not set up the New SQL 2005 server as a warm stand by. Before the final plug is pulled, close all external connection to the 2000 DB. once the last transaction log is restored in the Warm Stand By(2005) , make that as your prodoction. Wouldnt that be safest and fastest way? Ofcourse do all necesary check for Compatability issue and al the things mentioned by others.
You could always script the existing users with their SID and pre create them in the 2005 machine so you wont have any problems with the Users.
-Roy
December 20, 2007 at 8:44 am
I'd do what Roy has mentioned to minimize downtime.
also, be sure that you have looked for SQLAgent jobs or DTS packages that people might be expecting to exist on the new servers. You'll also want to make sure any alerts you have are moved.
December 20, 2007 at 2:31 pm
The other issue to consider is how fast will the moving of the bak vs mdf and ldf to new server will take place. If the bak is smaller it will move faster and can be restored fast. Attach and detach work just as fast but if it is a large database transfer will take longer. We did both methods to move our db's from 2000 box to 2005 box. Security logins never seemed to come over correctly so I just got into habit of creating the logins on the new server prior to the move, and once new db was in place dropped the existing db security logins and then added the "new" login to the "new"db. This had the additional benefit to allow me to also tighten up security on the new databases.
December 21, 2007 at 7:06 am
Lowry Kozlowski (12/20/2007)
Security logins never seemed to come over correctly so I just got into habit of creating the logins on the new server prior to the move, and once new db was in place dropped the existing db security logins and then added the "new" login to the "new"db.
Only thing you got to do is to create the Login in the new server with the same SID as the one in the 2000.
CREATE LOGIN TestUser
WITH PASSWORD = 'Whatever'
, CHECK_POLICY = ON
, SID =
I use this method regularly when ever a DB has to be set up in the test environment.
You can get teh SID by using this command
SELECT SUSER_SID ( 'TestUser' )
-Roy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply