January 16, 2008 at 2:18 am
Hi,
I am trying to migrate the whole SQL Server environment from one server to another server.Trying to perform this task by copying all databases's MDF and LDF file to the new server from old server.
Is this a feasible way or we can goahead with some other options which will be faster and without downtime?
Please share with me in this regard.
The server environment is SQL Server 2000.
Thanks,
Karthik
January 16, 2008 at 2:59 am
Do take care SQL logins by following the Microsoft KB 246133.
Or else you can script all the users and logins by choosing All Task --> Generate script -> option --> Script SQL and windows logins.
As per my opinion please manually note down all the windows logins and permission for more safety.
Once done you can backup the source databases and restore it in the target.
http://www.sqlservercentral.com/articles/Administering/2688/
support.microsoft.com/kb/314546
"More Green More Oxygen !! Plant a tree today"
January 16, 2008 at 10:52 am
I second the suggestion to backup and restore because detaching and moving the database files will require downtime.
Greg
January 24, 2008 at 3:12 pm
hi
even by ssis also u can proceed.
thx sreejith
MCAD
January 25, 2008 at 12:16 am
Hi,
You can follow this:
- Backup the Databases
- Make sure to note the jobs that are running for those databases and if they are DTS/SSIS dependent, back them up also. Dependencies should be noted down.
- Restore them on another server
- Create the logins and fix the users
- Create or deply the packages and jobs.
If you plan to create a similar sort of environment on new server, restoring master and msdb on the new server also makes sense.
Rgds,
January 25, 2008 at 4:30 pm
you can detach - reatch or even backup and restore. Even though backup and restore will "avoid downtime" there is the possibility of the database not being in sync. i.e. the orginal db being updated whilst being migrated. so i don't think you can avoid downtime unless you look at log shipping between the old and new database even then there may be downtime.... again some downtime may be inevitable
Gethyn Elliswww.gethynellis.com
February 17, 2008 at 11:33 pm
Dear All,
Thanks a lot for all your suggestion.
Let me check and update you in yhis regard.
Thanks,
Karthik
February 27, 2008 at 12:05 pm
You do a detach and attache method so that you can rely on the correct data. Also script jobs and logins and run in the other server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
March 8, 2008 at 10:56 pm
I will prefer backup and restore. Don't forget to change 'Compatibility Level' to 90 and
DBCC UPDATEUSAGE (0);
thanks
March 10, 2008 at 8:19 am
The first question to ask yourself is how much time do you have before the databases are needed again. detach/retach is great if you have the time, backup restore is great if you don't, with one caveat, any changes made after the backup will not be reflected in the new version.
So as you can see, there are different ways to accomplish what you need to be done. In a busy environment, I would generate a script of the old server and run it on the new server to place the database shell there, I'd then point all insert operations to the new server, and use SSIS to transfer all of the old data over to the new server, hopefully that will eliminate your downtime.
The key being, how much time do you have to accomplish the task, and can you do it one database at a time. If you have time, the detach/reattach method is definately the way to go, but know that the database will be down from the time you start the detach and complete the reattach. Just remember, you must factor in the time it will take to move the files from one server to the other.
An exciting time for you as it is not a routine daily DBA procedure, so take time to decide what you are comfortable with and if you have questions after the decision, post back and I'm sure many of us will gladly provide additional insight.
Good luck
Marvin Dillard
Senior Consultant
Claraview Inc
March 11, 2008 at 1:52 am
Dear All,
It is really marvelous.
I am very glad to have this many supporters.
I have decided to go with detach/attach method for this migration.
so here I have a question.Shall we detach and attach Master database from one server to another server also.
Thanks,
Karthikraj.L
March 11, 2008 at 2:19 am
Are you going to migrate from the existing Sql Server 2000 to another Sql 2000 server ?if thats then ensure the following,
1.You can attach/restore system databases only if they have the same build and same edition of Sql server.
i.e if your source server is Sql 2000 SP4 std edition then you need to restore/attach the files only to another Sql 2000 SP4 std edition. Otherwise you will get errors as mentioned in this KB article,
http://support.microsoft.com/kb/264474
Good Luck !
[font="Verdana"]- Deepak[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply