| Author |
Topic |
|
keithc
Starting Member
35 Posts |
 Posted - 2004-11-26 : 15:28:55
|
| I posted on this before but have not had a test machine to work this out until today. I have a client that only has 1 box and is for sql. It's actually houses quite a few large DB's and many user accounts (Windows and Sql authentication) I need to format this machine and once thats done Sql needs to be reinstalled along with all of the databases and user accounts just like nothing ever happened. I was going to do this as so. I already tried this on a test machine and it didn't work.1)Backup all db's to share on my laptop somehow.2) also along with the backup I planned to just stop the sql service then copy all existing user/system db's off to a share on my laptop including master.3)Bring the server down format it etc then reinstall sql stop the service on the new sql install.4) Cut out all system/user db's and replace them with the db's that are on my laptop.5)Start the sql serviceI tried to do it this way and no luck, I admit that I was just guessing and trying things out as I do not have any material to aid me in this process. I would really apreciate step by step help on how to accomplish this and get all user accounts etc back for a hopefully smooth migration at this client and any issues that may come up. |
|
|
Kristen
Test
22859 Posts |
|
|
keithc
Starting Member
35 Posts |
Posted - 2004-11-27 : 19:35:05
|
| What if I just detach the user DB's. Then once server is formatted and SQL installed again if I just attach the user DB's? What would I be losing by not including the original system DB's into the new SQL install? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-28 : 04:27:20
|
| Logins (master), Backup history, Jobs, etc. (msdb), defaults for new databases that are created (model), sample data that has been seriously mucked about with (pubs and Northwind)Also anything else in master - bespoke SProcs, SProcs set to run at startupYou might not have / need any of those of course!!Kristen |
|
|
|
keithc
Starting Member
35 Posts |
Posted - 2004-11-28 : 10:59:33
|
| Thanks for the replies. I'm gonna set up a vmware machine with sql installed then copy over and attach the user db's to the vmware machine and change a client ODBC to point to new vmware box then test the application. I did try to restore master and sys db's to a new installation but i had lft out a couple user db's since size of these were to big to get on my test box, I beleive it did get it in single user mode and actually restore the master db but the service would no start. I checked the application log which showed severe errors relating to the user db's I did not include, the log writes were like V ID not found or something like that. Do you have to have all previous user db's atached if your going to restore sys db's on to a new install of sql? Hope that made sense |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-28 : 13:04:02
|
| "Do you have to have all previous user db's atached if your going to restore sys db's on to a new install of sql?"I doubt it - I imagine they would just come up as "suspect", or somesuch, if missing.Kristen |
|
|
|
keithc
Starting Member
35 Posts |
Posted - 2004-11-28 : 15:26:48
|
| I finaly managed to get the master db restored using single user mode and think I have everything fairly well iron'd out here.I am now questioning about any server side settings like when you rt click the sql server and select properties if there is anything specified in there do i need to manualy log any settings changed and apply to new sql install? Or if there are DTS created packages or anything else I should be taking note of outside the system/user databases to be applied to new installation? Thanks |
|
|
|
keithc
Starting Member
35 Posts |
Posted - 2004-11-30 : 18:19:00
|
| Kristen what about bringing up another box and setting up replication for all databases and just use this new machine and formatting the original sql box after replication completes? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-30 : 18:25:33
|
| I wouldn't do that. Just stop the MSSQLSERVER service on your server. Copy all MDFs and LDFs to another server for safe keeping. Make sure you also have a good backup of all databases. Format the server. Build the server again, install SQL Server using the same paths as the original server. Stop the MSSQLSERVER service. Copy over the MDF and LDFs from the safe keeping server to this newly formatted server. Start the service. You are now up and running and you've got everything, logins, DTS packages, user databases, everything.The key part of this way is that the paths must be the same for the MDFs and LDFs.Tara |
|
|
|
keithc
Starting Member
35 Posts |
Posted - 2004-11-30 : 19:22:32
|
| Tara, Hows it going thanks for the response. Hey i went ahead did backups then just reinstalled SQL then put SQL in single user mode and just restored Master then MSDB, was no need to do model as it was default settings on the original and everything seemed to be fine. Anyways i did this several times in testing and spent a bit of time developing a project outline to achieve this on a production box. Turns out I got reemed for the amt of time i had spent planning and documenting this and my boss went ahead and did replication on the production box without me and it worked fine for him. Anyways it turned pretty ugly as I said to him that just because he did it that way and it worked that didn't mean that the way I intended to do this and the planning I did was wrong. I have worked with replication and it is not an easy thing to uninstall and fix from my experience when something goes wrong and wasn't to confident in it. Just wondering if you wouldn't mind answering is what your reasons would be for not choosing replication if there are any? Thanks much |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-30 : 19:27:08
|
| Replication overcomplicates a pretty straight forward DBA task. Replication is to replicate data and is not meant for database administration tasks such as this. One of the problems with this solution for you is that the identity properties are not transferred. That only matters if you are using them of course. Replication will not handle the logins, jobs, DTS packages, and everything else not stored in the user tables. That's a lot of stuff that replication doesn't handle. But your solution and mine handle these. And agreed on the configuration of replication and troubleshooting being a pain.Tara |
|
|
|
keithc
Starting Member
35 Posts |
Posted - 2004-11-30 : 22:53:57
|
| Thanks again Tara |
|
|
|
|
SQL Migration Help