May 24, 2009 at 6:56 am
1. I have a system which user tables was created in master.mdf in SQL server 2000.My understanding this is not good practice to create any user object in Master DB because it contains the system tables.
2. My system will be upgraded soon involving the database but using same SQL engine.However the new database schema is
different with current databases
3. I have plan to separate the user data into its own Db and will named as a TOOLS.
I plan to use DTS to migrate data between old and new database which is quite similar but having several differences in field mapping.
Could you guys suggest a better way of doing this..
My plan steps..
a) Install new instances of db under same server..
b) Create DTS to define data transformation
c) Run DTS to migrate data
d) Backup new Data
e) Fresh - Installation of new SQL Server
f) Restore the backup data
The new system need to be deployed to 100 over location
.. I might need same template for all location.
hope someone can give me advice ......
May 24, 2009 at 7:51 am
actually, you don't need another sql instance.
You might as well just create a new userdb (tools) and export your data from master to your userdb.
After that, it may be best to drop your non-system stuff from master db and at the end shrink master db and perform regular db maintenance on all involved dbs.
Run this in TEST before you actually go live!
You will need to modify applications because they will have to connect to your Toolsdb !
(btw. If you currently don't specify a db when you connect to the sqlserver instance, and even don't use "use master" in your applications, you may be able to pull it off by altering the connecting users default database to your new db.
Play if safe .... start with full backups of all your db !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 26, 2009 at 8:30 am
I'd agree with ALZDBA on this. You don't need another instance, and it will make upgrading easier if you do this now.
One thing that is probably happening is that users have "master" as their default database for their logins. You can change this for all users, but first you need to create "Tools" and then you can alter logins.
The timing will be strange. DTS can easily move (copy) your data to a new database, moving all tables, stored procedures, and views. Users and permissions can be scripted out, run in Tools, and then you can change the defaults.
It's not hard to do, but there are a lot of things. Be sure you test well.
May 27, 2009 at 10:10 pm
Thx for advised .......
July 13, 2009 at 6:45 pm
if there any query that can i drop all non-system stuff from master.Currently i drop the table one by one.Hope anyone can help me ......
July 14, 2009 at 3:28 am
you could generate your drop statements using this query
Select *
from sysobjects
where xtype = 'u'
and category = 0 --(in sql2005 that would become is_ms_shipped = 0)
order by name
Doublecheck you migrated all tables, views, , procs, indexes, ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply