October 22, 2009 at 5:27 am
Hi
I've been trying to move my system dbs to another server using attach/detach. I net started the instance with t3608, connected, then diconnected, closed object browser and stated new query. I keep getting Msg 3710 Cannot detach an opened database when the server is in minimally configured mode. Can anybody help? Thanx
October 22, 2009 at 6:01 am
would have to ask what you are trying to achieve? Physically moving system database files to another server is not a common way of migrating data.
If you really want to do this. stop SQL and copy and paste the files. Or use backup\restore.
Unless the new server is going to end up with the same name I would not do this. Even then you will have problems with permissions assigned to the local groups created by SQL on install.
---------------------------------------------------------------------
October 22, 2009 at 6:10 am
I'm dealing with a new install, and we've been too click happy when installing. The instance is on our TS server, which doesn't have a good redundency (and only 40gb capacity), so I wanted to put all I can onto the fileserver. Perhaps should mention that I work for a charity and we are having to cheap-scate as much as possible.
So just copying and pasting, and then attach?
October 22, 2009 at 6:52 am
still not quite sure what you are trying to do, What's a TS server?
you cannot store the databases on a different server to the SQL install. what you could do is store the database backups on the fileserver.
---------------------------------------------------------------------
October 22, 2009 at 7:24 am
TS-terminal services, the server is underused - so we decided that we'd run sql on it, not being able to buy even a baby machine just running sql.
MS seem to think that you can move system dbs (http://msdn.microsoft.com/en-us/library/ms345408.aspx, though they say use detach/attach or backup and restore if moving to another instance or server) - though I have to say they're not that clear about it.
October 22, 2009 at 7:55 am
ckanow (10/22/2009)
TS-terminal services, the server is underused - so we decided that we'd run sql on it, not being able to buy even a baby machine just running sql.MS seem to think that you can move system dbs (http://msdn.microsoft.com/en-us/library/ms345408.aspx, though they say use detach/attach or backup and restore if moving to another instance or server) - though I have to say they're not that clear about it.
Oh yes you can do it but that does not mean its a good idea. Hence MS are coy about it.
Is your install complex? Are there lots of SSIS packages, linked servers, SQLAgent jobs?. If its just a database engine install move the instance by doing a fresh install on the other server, script out objects to move and load them in. Use sp_help_revlogin (google that to get the script) to get your logins, may well be all you need to do. Use either backup\restore or detach\attach for your user databases only. Both methods are good, really depends on whether you have space for the backups and time you have. Other objects cab right clicked on and scripted out.
When you do the install make sure you get your system databases where you want them, go into advanced options, expand database engine and define where you want the data files, which might not be where you want the binaries.
---------------------------------------------------------------------
October 22, 2009 at 8:49 am
Mhmm, I don't think the main server could manage the extra workload sql would produce. Looks like I've entangled myself before I even started development. I think in that case, I better just backup the system dbs onto the main server, as you suggested earlier, and well, user dbs where always going to be housed on the main server.
But I'm still not clear as to why would it be a bad idea to move system dbs onto a different server from from where the install is?
October 22, 2009 at 8:56 am
ckanow (10/22/2009)
But I'm still not clear as to why would it be a bad idea to move system dbs onto a different server from from where the install is?
because SQL server will not work! It wont be able to see the system databases so will not even start, they need to be on the same server
---------------------------------------------------------------------
October 22, 2009 at 9:02 am
I see, thanx for your help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply