Ouch.. master DB question

[Jasen]

I was asked to look at a SQL server at a remote site that's been acting up and bogged down.
What I found was that they had built their main production database into the Master DB. So this thing is continuously logging production data into tables in Master. I about cried.

What I'm asking is, how safe does it sound if I were to:
script all the non-system tables and stored procedures in Master
recreate these in a new DB
use DTS to copy over the existing data
delete them from the Master
change all DSN's to point to new table.
I'll be doing it all over RDP.

Obviously, this is after backing it up and cutting off any clients or services that might be writing to it.
It seems pretty straightforward and simple, are there any caveats I'm missing?

 

[bborissov]

Your clients are lucky they not create all in TempDB Imagine what will happens when someone restart SQL Server

I see nothing scarry to act the way you decide. but make a veeery good backup first, else you will be guilty about everything

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[mrdenny]

Your method of moving the data should work out.

You may need to remove forign keys and then put them back after you load up all the data.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com