Can't detach or drop database! 1

[donutman]

I'm trying to upgrade my OS on the SQL Server box. I want to do a clean install. So I've backed up the dbs but I'd like to also just do a file copy. I'm being prevented from detaching one db because it says it is being replicated. Same if I try to drop it. I have no replication publications or subscriptions. About 2 years ago I did replicate that db. So somehow it's status has gotten stuck. Anyone know how to fix that?
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[nigelrivett]

You will be better off using backup/restore than detach/attach.

For the detach you will have to bacup the database and test do a test restore anyway in case the attach fails.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Corran007]

If it says about db replication and its not being done, then you should clean it up.

see this post by sql god jay!

http://www.tek-tips.com/viewthread.cfm?spid=962&sqid=778056&newpid=962

to help find out which tables have replication use:

select name from sysobjects where replinfo <> 0

those have replication. i was able to use step 2 of them to fix mine

good luck to you.

 

[donutman]

Awesome. Thanks to you and Jay the replication issue is cleaned up. Now onto the next problem. I can't seem to detach the Master, tempdb, Model or Msdb databases even while in single user mode. The option is grayed out in EM. However this may be an additional clue. If I try to update a system table in Single UM then it says only one use at a time can modify...blah blah blah. I think it's saying that I've logged into the db (using EM) with a different user name. I don't know my way around the login issues. I generally use sa as I am the only IT guy around here.
I've been playing around with a backup server by just deleting the dbs from the sysdatabases table. BTW, that gets rid of the replication issue too (after I reattach everything seems to be ok). When it comes to the system db, I was playing with just changing the file location column in that table (cuz the local path is different from the production server and the backup server and cuz if I delete them then SQL Server won't start). Changing the relative location for all the system dbs works except for tempdb. After changing that db SQL Server will no longer start. This paragraph is kind of a side note.
Bottom line how do I detach the system dbs the right way?
If I need to log into the db with different credentials, you'll have to walk me through that.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Maybe I should just forget about copying the system databases over to the new OS. I'll have to re-install SQL Server, so is there any point in having the original Master db etc?
Hmm, there is (I think) becuase of all of the scheduled jobs and maintenance plans etc. What db is that info in? Tempdb is the only one I probably can't mess with according to my experiments with my backup server. Maybe I should just do standard backup/restore on those dbs?
BTW, I will be changing the data path once I install the new OS, so the detach/attach (or backup/restore) has to be done. Another words I can't get away with just a file copy of the data directory containing the .mdf files.
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[nigelrivett]

I still advise backup/restore as you may lose databases with a detach/attach if the file isn't attachable (it's fairly rare but does happen).
The attach forcess you to specify the file path to that's a red herring. The restore allows a "with move" option to create the files in the correct path.

tempdb is recreated every time you restart the server so no point in transferring it.

Jobs are held in msdb. For a restore you may have to update the servername in sysjobs.
Master holds logins so you may want to restoe it - if you recreate them the login IDs probably won't match with the database users (unless you specify the sid) and you will have to fix them - see sp_changeuserslogin.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[donutman]

Thanks Nigel. I've decided to call it a day. I'll restart next Sunday and try to upgrade the network to 2003 then...I had to much trouble with the replication issue today.
I've been testing the detach/attach using the backup server and it's been working. Any backups I make I do the same test. I certainly don't want to drop my server and then find that my backup media doesn't work.
The only remaining issue isn't really a SQL issue but I haven't been able to get an answer in the proper forum, so I'll ask here. I want to break the mirror between Drive 0 and 1. The SQL Data is mirrored. Drive 1 also has a volume containing more or less stable data. I of course intend to back up at least portions of it, but I'm wondering if I can rely on being able to read Drive 1 after installing the new OS on Drive 0 (with a complete reformat). After creating the appropriate volumes on Drive 0 and copying the data from Drive 1, I could then reformat Drive 1 and recreate the necessary volumes there too. And finally re-mirror the appropriate volumes. Is this workable?
-Karl

[red] Cursors, Triggers and User Definded Functions are part of the Axis of Evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]