Restore 2005 DB to SQL server 2000

Hi all
there is a lot of questions and answers about how to transfer DB 2000 to SQL
server 2005. But is there way (I hope) that allow to transfer DB between SQL
server 2000 and 2005 vise versa?
I'd connetced between SQL server 2000 and 2005 and exported DB from 2005 to
DB in 2000. But from 30 MB it becone 18 Gb and cannot find way to decrease it.
thx for help
--
Best Regards
Denis Laskov
Arcnet Ltd
MCSE 2003You can only restore database from SQL Server 2000 to SQL Server 2005,
but you can not restore a database from SQL Server 2005 to SQL Server
2000. Think about it for minute, there are new features and data
types that exist only in SQL Server 2005, so it can not work in SQL
Server 2000.
You didn't write much about what you did that caused a 30MB database
to become 18 GB. Which file got so large? Is it a log file? is it a
data file? If you try to use DBCC command (shrinkfile or shrinkdb) do
you get an error message?
Adi
Denis Laskov wrote:
> Hi all
> there is a lot of questions and answers about how to transfer DB 2000 to SQL
> server 2005. But is there way (I hope) that allow to transfer DB between SQL
> server 2000 and 2005 vise versa?
> I'd connetced between SQL server 2000 and 2005 and exported DB from 2005 to
> DB in 2000. But from 30 MB it becone 18 Gb and cannot find way to decrease it.
> thx for help
> --
> Best Regards
> Denis Laskov
> Arcnet Ltd
> MCSE 2003|||Hello Adi
I am thinking about it about 48 hours and it's not looking good for me, that
upgraded version of software not support data exchange with previous version.
To transfer data, I'd installed SQL 2005 on other mashine, restored files
from 2005 backup in 2005 server Database. Then Export - to DB - Native SQL
Client (also tried Microsoft Ole DB provider for SQL server) - to remote 2000
SQL server - "Copy data for one or more Tables or views", "Select All",
"Next", "Next". As a result of 2 hours process run - DataFile is 18 GB and
Transaction log is 11 Gb.
I am not so good in DB as maybe I should, but I performed also data
optimosation and reorganisation as part of Maintanance plan, and also Shrink,
but from 18.654 Mb Database become 18.342 Mb.
Any ideas?
Best Regards
Denis Laskov
Arcnet Ltd
MCSE 2003
"Adi" wrote:
> You can only restore database from SQL Server 2000 to SQL Server 2005,
> but you can not restore a database from SQL Server 2005 to SQL Server
> 2000. Think about it for minute, there are new features and data
> types that exist only in SQL Server 2005, so it can not work in SQL
> Server 2000.
> You didn't write much about what you did that caused a 30MB database
> to become 18 GB. Which file got so large? Is it a log file? is it a
> data file? If you try to use DBCC command (shrinkfile or shrinkdb) do
> you get an error message?
> Adi
> Denis Laskov wrote:
> > Hi all
> > there is a lot of questions and answers about how to transfer DB 2000 to SQL
> > server 2005. But is there way (I hope) that allow to transfer DB between SQL
> > server 2000 and 2005 vise versa?
> > I'd connetced between SQL server 2000 and 2005 and exported DB from 2005 to
> > DB in 2000. But from 30 MB it becone 18 Gb and cannot find way to decrease it.
> > thx for help
> > --
> > Best Regards
> >
> > Denis Laskov
> > Arcnet Ltd
> > MCSE 2003
>|||Hi Denis
The upgraded version (SQL Server 2005) does support data exchange
with previous version (SQL Server 2000).
As for your problem. Can you run DBCC showfilestats in query
analyzer? Compare the columns TotalExtents with UsedExtents to
detrmind how much free space you have in the file. If you see that you
don't have lots of free space, then run dbcc showcontig on you tables
(start with the biggest tables). According to the results that you get
you can rebuild the clustered index of the tables that have lots of
fragmentation. After you rebuild the clustered index, you can try and
run dbcc shrink file.
Af for the log. If your database is in simple mode, then you can run
dbcc shrinkfile. If it isn't in simple mode, then you can backup the
log and then run dbcc shrinkfile or modify the database to simple mode
and then run dbcc shrinkfile.
You can find more information in books on line about dbcc showcontig,
dbcc shrinkfile, dbcc dbreindex and alter database.
Adi
Denis Laskov wrote:
> Hello Adi
> I am thinking about it about 48 hours and it's not looking good for me, that
> upgraded version of software not support data exchange with previous version.
> To transfer data, I'd installed SQL 2005 on other mashine, restored files
> from 2005 backup in 2005 server Database. Then Export - to DB - Native SQL
> Client (also tried Microsoft Ole DB provider for SQL server) - to remote 2000
> SQL server - "Copy data for one or more Tables or views", "Select All",
> "Next", "Next". As a result of 2 hours process run - DataFile is 18 GB and
> Transaction log is 11 Gb.
> I am not so good in DB as maybe I should, but I performed also data
> optimosation and reorganisation as part of Maintanance plan, and also Shrink,
> but from 18.654 Mb Database become 18.342 Mb.
> Any ideas?
> Best Regards
> Denis Laskov
> Arcnet Ltd
> MCSE 2003
>
> "Adi" wrote:
> > You can only restore database from SQL Server 2000 to SQL Server 2005,
> > but you can not restore a database from SQL Server 2005 to SQL Server
> > 2000. Think about it for minute, there are new features and data
> > types that exist only in SQL Server 2005, so it can not work in SQL
> > Server 2000.
> >
> > You didn't write much about what you did that caused a 30MB database
> > to become 18 GB. Which file got so large? Is it a log file? is it a
> > data file? If you try to use DBCC command (shrinkfile or shrinkdb) do
> > you get an error message?
> >
> > Adi
> >
> > Denis Laskov wrote:
> > > Hi all
> > > there is a lot of questions and answers about how to transfer DB 2000 to SQL
> > > server 2005. But is there way (I hope) that allow to transfer DB between SQL
> > > server 2000 and 2005 vise versa?
> > > I'd connetced between SQL server 2000 and 2005 and exported DB from 2005 to
> > > DB in 2000. But from 30 MB it becone 18 Gb and cannot find way to decrease it.
> > > thx for help
> > > --
> > > Best Regards
> > >
> > > Denis Laskov
> > > Arcnet Ltd
> > > MCSE 2003
> >
> >|||Hi Adi
Thx for Your pation, first of all. :)
Result of DBCC showfilestats is "Successfull without errors" .
I'll read about rebuild clustered indexes.
--
Best Regards
Denis Laskov
Arcnet Ltd
MCSE 2003
"Adi" wrote:
> Hi Denis
> The upgraded version (SQL Server 2005) does support data exchange
> with previous version (SQL Server 2000).
> As for your problem. Can you run DBCC showfilestats in query
> analyzer? Compare the columns TotalExtents with UsedExtents to
> detrmind how much free space you have in the file. If you see that you
> don't have lots of free space, then run dbcc showcontig on you tables
> (start with the biggest tables). According to the results that you get
> you can rebuild the clustered index of the tables that have lots of
> fragmentation. After you rebuild the clustered index, you can try and
> run dbcc shrink file.
> Af for the log. If your database is in simple mode, then you can run
> dbcc shrinkfile. If it isn't in simple mode, then you can backup the
> log and then run dbcc shrinkfile or modify the database to simple mode
> and then run dbcc shrinkfile.
> You can find more information in books on line about dbcc showcontig,
> dbcc shrinkfile, dbcc dbreindex and alter database.
> Adi
> Denis Laskov wrote:
> > Hello Adi
> > I am thinking about it about 48 hours and it's not looking good for me, that
> > upgraded version of software not support data exchange with previous version.
> > To transfer data, I'd installed SQL 2005 on other mashine, restored files
> > from 2005 backup in 2005 server Database. Then Export - to DB - Native SQL
> > Client (also tried Microsoft Ole DB provider for SQL server) - to remote 2000
> > SQL server - "Copy data for one or more Tables or views", "Select All",
> > "Next", "Next". As a result of 2 hours process run - DataFile is 18 GB and
> > Transaction log is 11 Gb.
> > I am not so good in DB as maybe I should, but I performed also data
> > optimosation and reorganisation as part of Maintanance plan, and also Shrink,
> > but from 18.654 Mb Database become 18.342 Mb.
> > Any ideas?
> > Best Regards
> >
> > Denis Laskov
> > Arcnet Ltd
> > MCSE 2003
> >
> >
> > "Adi" wrote:
> >
> > > You can only restore database from SQL Server 2000 to SQL Server 2005,
> > > but you can not restore a database from SQL Server 2005 to SQL Server
> > > 2000. Think about it for minute, there are new features and data
> > > types that exist only in SQL Server 2005, so it can not work in SQL
> > > Server 2000.
> > >
> > > You didn't write much about what you did that caused a 30MB database
> > > to become 18 GB. Which file got so large? Is it a log file? is it a
> > > data file? If you try to use DBCC command (shrinkfile or shrinkdb) do
> > > you get an error message?
> > >
> > > Adi
> > >
> > > Denis Laskov wrote:
> > > > Hi all
> > > > there is a lot of questions and answers about how to transfer DB 2000 to SQL
> > > > server 2005. But is there way (I hope) that allow to transfer DB between SQL
> > > > server 2000 and 2005 vise versa?
> > > > I'd connetced between SQL server 2000 and 2005 and exported DB from 2005 to
> > > > DB in 2000. But from 30 MB it becone 18 Gb and cannot find way to decrease it.
> > > > thx for help
> > > > --
> > > > Best Regards
> > > >
> > > > Denis Laskov
> > > > Arcnet Ltd
> > > > MCSE 2003
> > >
> > >
>|||Hi Denis
dbcc showfilestats should show file's statistics and not an
information message. I don't know why it doesn't show the statistics
to you. Also I mislead you. DBCC showfilestats is an undocumented
command, so you can't find anything about it in BOL. However it is
documented in Google:-)
Adi
Denis Laskov wrote:
> Hi Adi
> Thx for Your pation, first of all. :)
> Result of DBCC showfilestats is "Successfull without errors" .
> I'll read about rebuild clustered indexes.
> --
> Best Regards
> Denis Laskov
> Arcnet Ltd
> MCSE 2003
>
> "Adi" wrote:
> > Hi Denis
> >
> > The upgraded version (SQL Server 2005) does support data exchange
> > with previous version (SQL Server 2000).
> >
> > As for your problem. Can you run DBCC showfilestats in query
> > analyzer? Compare the columns TotalExtents with UsedExtents to
> > detrmind how much free space you have in the file. If you see that you
> > don't have lots of free space, then run dbcc showcontig on you tables
> > (start with the biggest tables). According to the results that you get
> > you can rebuild the clustered index of the tables that have lots of
> > fragmentation. After you rebuild the clustered index, you can try and
> > run dbcc shrink file.
> >
> > Af for the log. If your database is in simple mode, then you can run
> > dbcc shrinkfile. If it isn't in simple mode, then you can backup the
> > log and then run dbcc shrinkfile or modify the database to simple mode
> > and then run dbcc shrinkfile.
> >
> > You can find more information in books on line about dbcc showcontig,
> > dbcc shrinkfile, dbcc dbreindex and alter database.
> >
> > Adi
> >
> > Denis Laskov wrote:
> > > Hello Adi
> > > I am thinking about it about 48 hours and it's not looking good for me, that
> > > upgraded version of software not support data exchange with previous version.
> > > To transfer data, I'd installed SQL 2005 on other mashine, restored files
> > > from 2005 backup in 2005 server Database. Then Export - to DB - Native SQL
> > > Client (also tried Microsoft Ole DB provider for SQL server) - to remote 2000
> > > SQL server - "Copy data for one or more Tables or views", "Select All",
> > > "Next", "Next". As a result of 2 hours process run - DataFile is 18 GB and
> > > Transaction log is 11 Gb.
> > > I am not so good in DB as maybe I should, but I performed also data
> > > optimosation and reorganisation as part of Maintanance plan, and also Shrink,
> > > but from 18.654 Mb Database become 18.342 Mb.
> > > Any ideas?
> > > Best Regards
> > >
> > > Denis Laskov
> > > Arcnet Ltd
> > > MCSE 2003
> > >
> > >
> > > "Adi" wrote:
> > >
> > > > You can only restore database from SQL Server 2000 to SQL Server 2005,
> > > > but you can not restore a database from SQL Server 2005 to SQL Server
> > > > 2000. Think about it for minute, there are new features and data
> > > > types that exist only in SQL Server 2005, so it can not work in SQL
> > > > Server 2000.
> > > >
> > > > You didn't write much about what you did that caused a 30MB database
> > > > to become 18 GB. Which file got so large? Is it a log file? is it a
> > > > data file? If you try to use DBCC command (shrinkfile or shrinkdb) do
> > > > you get an error message?
> > > >
> > > > Adi
> > > >
> > > > Denis Laskov wrote:
> > > > > Hi all
> > > > > there is a lot of questions and answers about how to transfer DB 2000 to SQL
> > > > > server 2005. But is there way (I hope) that allow to transfer DB between SQL
> > > > > server 2000 and 2005 vise versa?
> > > > > I'd connetced between SQL server 2000 and 2005 and exported DB from 2005 to
> > > > > DB in 2000. But from 30 MB it becone 18 Gb and cannot find way to decrease it.
> > > > > thx for help
> > > > > --
> > > > > Best Regards
> > > > >
> > > > > Denis Laskov
> > > > > Arcnet Ltd
> > > > > MCSE 2003
> > > >
> > > >
> >
> >