Changing Servers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Changing Servers

Author

Topic

spinman
Starting Member

9 Posts

Posted - 2007-01-24 : 15:16:08

I have read all the FAQs on restore and find myself still confused.
So I apologize if the information is there and I am missing it.
I want to move the databases from an old server to a new server, brand new, will be the same name and IP address once the database is transfered.
Everything I have read says do a full back up and then restore onto the new server. Makes sense so far, my question is how does the Database Master get handled?
Is it necessary to restore it?
If so what is the best way to go about it?

Thanks for your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-24 : 15:18:16

If the drive letters will remain the same, then the easiest way to do this is to install SQL Server on the new server, stop SQL Server on both servers, copy all MDFs and LDFs from the old server to the new server in the same paths, then startup SQL Server on the new server.

If the paths aren't going to be the same, then BACKUP/RESTORE is the way to go.

Tara Kizer

spinman
Starting Member

9 Posts

Posted - 2007-01-24 : 15:31:44

Yes all drive letters will be the same.

By doing it this way then when SQL Services restart it will then pick up the new databases?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-24 : 15:46:38

It will pickup everything if you do it that way. It will include logins, jobs, DTS packages, etc... You just need to ensure you grab all MDFs and LDFs and put them in the same location on the new server.

I've probably used this method about 10 times now.

Tara Kizer

spinman
Starting Member

9 Posts

Posted - 2007-01-28 : 15:54:47

OK I copied over all the MDF and LDF files to the exact location of the old server. Now for whatever reason SQL Services wont start up.

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-29 : 01:50:20

Did you copy all MDFs and LDFs? When I say all, that includes system databases. If so, then check out the errors in Event Viewer. Post whatever error corresponds to the service failure here.

Tara Kizer

spinman
Starting Member

9 Posts

Posted - 2007-01-29 : 08:59:43

Thanks for your help.
I actually got it to work. I am not sure what I did wrong - so I started from scratch and it worked the second time.

Kristen
Test

22859 Posts

Posted - 2007-01-29 : 09:45:31

Tara,

What's the score on Service Packs with this process?

Obviously if the NEW BOX is the same Service Pack as the Old Box its fine, and that would be the ideal scenario.

I wonder if its also fine for the New Box to be a LATER Service Pack than the Old Box (I wonder if there are likely to be special SProcs scripts etc. that the Service Pack install process may run on Master, which might not get run when restarting Master after over-copying the MDF/LDF file?)

And I presume its bad news if the New Box is an OLDER Service Pack ....

Thanks

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-29 : 11:24:52

I've never done it with different service packs.

Tara Kizer

ed6612
Starting Member

5 Posts

Posted - 2007-02-21 : 14:37:22

I have recently tested performing a complete SQL server move as mentioned in this thread (all DBs) on virtual machines before doing the same on our production database. Everything went smoothly except the following minor issues that had to be resolved:

1. After the move, in Enterprise Manager, Security, Logins, I had to re-enter Windows user logins, especially the one used for the SQL Server Agent service (it would not start otherwise). This is to be expected on a different Windows box.
2. Most user database owners were listed as "(Unknown)" on database general tabs and attempting to execute sp_helpdb on any of these resulted in:

"Cannot Insert the value NULL into column '', table '';"

Running sp_changedbowner on these databases to reset the owner fixed this issue. I believe this was also related to Windows accounts on the new system needing to be remapped.

-Ed

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-21 : 14:39:44

That should not have occurred if master was moved as well.

Tara Kizer

ed6612
Starting Member

5 Posts

Posted - 2007-02-26 : 00:20:22

But what if the db owner was a windows account that is not present on the new system?

-Ed

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-26 : 01:45:13