Moving MSDE databases to SQL 2000 on same server
ever...@mypacks.net
My company runs an SBS 2003 server and has a number of third party
products (backup software, virus checker, etc.) and MS products
(Sharepoint) installed that each use their own MSDE installations. I
am trying to move everything to SQL Server 2000, so I can then remove
all these unnecessary MSDE installs and reclaim that space on the C:
drive. (In the process, the databases will also be moving to a
different partition.) I understand the detach and re-attach process,
and also have docs for moving the system databases (master, model,
msdb, tempdb), but there's something I'm not understanding.
From what I understand, it's vitally important to maintain the link
between the application database(s) and their system databases. But
the process of consolidating everything would appear to want to link
the MSDE application databases to the SQL Server 2000 system
databases? Understand what I mean? Each database has its own master,
model, etc. database, but the act of merging everything would seem to
mean dropping all but the SQL Server 2000 system databases.
Is this some basic SQL Server architecture issue that I'm missing, or
will I not able to merge everything as I want? Also, if I am able to
merge everything, I assume this will mean that all future databases
will default to appearing on the new partition and not C: ?
Thanks for any suggestions,
Todd
Steve Foster
You don't need to try and copy/move the system databases. They're
internal to SQL Server, and store things like "what databases exist and
where", Agent jobs, and so on.
"Model" is the template database used for creating new ones.
All you want is the actual databases, plus the details for any SQL user
accounts the applications rely on.
--
Steve Foster
For SSL Certificates, Domains, etc, visit.:
https://netshop.virtual-isp.net
ever...@mypacks.net
> ever90...@mypacks.net wrote:
>
> [...]
>
> You don't need to try and copy/move the system databases. They're
> internal to SQL Server, and store things like "what databases exist and
> where", Agent jobs, and so on.
>
> "Model" is the template database used for creating new ones.
>
> All you want is the actual databases, plus the details for any SQL user
> accounts the applications rely on.
>
> --
> Steve Foster
> For SSL Certificates, Domains, etc, visit.:https://netshop.virtual-isp.net
Steve,
Thanks for the reply. Part of my plan was to remove all the MSDE junk
that exist in subdirectories of C:\Program Files\Microsoft SQL Server
and reclaim the space. To do that, I need to do something with the
system databases. Or are you saying this is something the software
won't support? It's a bit terse, and I haven't actually tried it yet,
but http://support.microsoft.com/kb/224071/en-us has instructions for
moving the system databases. I'm just trying to understand how to
follow through with the procedure without clobbering the databases
used by SQL Server itself. Can it be done?
Thanks again,
Todd
Todd
Jim Behning SBS MVP
Aren't the sample and master databases trivial in size? I would think
not worth goofing with. The few times I have moved SQL databases all I
moved were the actual databses that were big.
See what SBS support is working on
http://blogs.technet.com/sbs/default.aspx
Check your SBS with the SBS Best Practices Analyzer
http://blogs.technet.com/sbs/archive/tags/BPA/default.aspx
Steve Foster
My point was that you *don't* move the system databases between
instances of SQL Server. At least, not generally.
If you're consolidating content databases to fewer instances of SQL
Server, you just want to take those content databases, and add them to
a destination instance That instance already has its own set of system
databases, so doesn't need the ones from the "donor" instances.
What you do want to copy across from one set of system databases to
another is the critical bits of system data, like SQL Logins (assuming
the applications aren't so badly written that they assume use of "sa").
When you uninstall the redundant instances, you'll *throw away* their
system databases.
ever...@mypacks.net
> ever90...@mypacks.net wrote:
>
> [...]
>
> instances of SQL Server. At least, not generally.
>
> If you're consolidating content databases to fewer instances of SQL
> Server, you just want to take those content databases, and add them to
> a destination instance That instance already has its own set of system
> databases, so doesn't need the ones from the "donor" instances.
>
> What you do want to copy across from one set of system databases to
> another is the critical bits of system data, like SQL Logins (assuming
> the applications aren't so badly written that they assume use of "sa").
>
> When you uninstall the redundant instances, you'll *throw away* their
> system databases.
>
> --
> Steve Foster
> For SSL Certificates, Domains, etc, visit.:https://netshop.virtual-isp.net
Steve and Jim,
Thanks for the followups. Adding up all the system databases used by
SQL Server 2000 and five installations (?) of MSDE totals over 417 MB,
which would be gold for us. This SBS 2003 server was set up with just
a 12GB C: partition (I didn't do it!), and a software install over the
weekend came with a prerequisite of .Net 3.5. We've largely been .Net-
free (and I preferred it that way) up until now. I freed up a extra
GB before hand, but the total install grabbed roughly 1.75 GB, and
we're again perilously low on space.
Comparing the system databases between SQL Server and the various MSDE
installs, the sizes are all radically different so it wouldn't seem
wise to discard anything. Unless you have a better idea, my current
plan is to move all the system databases to our F: drive (where the
content databases currently reside), and leave the MSDE installations
intact. I'd love to make even more room, but this should give us
enough breathing space.
Thanks,
Todd
Erland Sommarskog
> Thanks for the followups. Adding up all the system databases used by
> SQL Server 2000 and five installations (?) of MSDE totals over 417 MB,
> which would be gold for us. This SBS 2003 server was set up with just
> a 12GB C: partition (I didn't do it!), and a software install over the
> weekend came with a prerequisite of .Net 3.5. We've largely been .Net-
> free (and I preferred it that way) up until now. I freed up a extra
> GB before hand, but the total install grabbed roughly 1.75 GB, and
> we're again perilously low on space.
C: partition? Moving 417 MB of system databases for SQL Server may
give you some breathing room, but only for a month or two. Then the
next thing comes.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
ever...@mypacks.net
Hehe. There won't be a next thing, at least not on my watch. I'm
tired of having four job titles and will be out of here at the end of
July. Then somebody else can choose between resize and
repartition. ;-) Thanks.