An easy (but long) restore question

The situation is this. I have a "production test" system, which we are
about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
test fixes, code changes and the like, but it is effectively "pseudo
production" so I have to be careful with it. In effect the user needs
access to the current test data until the upgrade process is complete
and accepted. Since this is my first major upgrade project I am a bit
nervous. Now the issue --
The current database is totally mis-sized, especially the Logfile which
has been allowed to grow to well over 6G of which 3% to 5% is typically
in use. So as a part of this process I would like to resize the target
databases.
My plan is to install a new instance of Sql2005 along-side the existing
2000 instance, on the same server and set of disks. But now I need to
get a copy of the 2000 data over to it. If I back up and restore to an
empty disk location w/ move, I get the existing structure. If I pre-
create the physical structure and restore over it with a move, it will
still overwrite the files and leave me with a copy of the old bad
structure. I know I can go ahead with the copy with move, and then run
the appropriate shrinks and resizing against the copy, but I'd like to
do it in a single process in the interest of time and safety. I've
considered the copy database wizard, but I'm not sure of all of the
ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
of the afternoon researching this in BOL and in 3rd party books. But I
would really appreciate any insights the group might offer and any
advance warnings about "gotcha's" and "show-stoppers."
Thanks in advance.
Graham (Pete) Berry
PeteBerry@.Caltech.edu
sorry for the duplicate post -- it took over 12 hours for the first one
to appear and I got impatient.
> --
> Graham (Pete) Berry
> PeteBerry@.Caltech.edu
>
Graham (Pete) Berry
PeteBerry@.Caltech.edu

An easy (but long) restore question

The situation is this. I have a "production test" system, which we are
about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
test fixes, code changes and the like, but it is effectively "pseudo
production" so I have to be careful with it. In effect the user needs
access to the current test data until the upgrade process is complete
and accepted. Since this is my first major upgrade project I am a bit
nervous. Now the issue --
The current database is totally mis-sized, especially the Logfile which
has been allowed to grow to well over 6G of which 3% to 5% is typically
in use. So as a part of this process I would like to resize the target
databases.
My plan is to install a new instance of Sql2005 along-side the existing
2000 instance, on the same server and set of disks. But now I need to
get a copy of the 2000 data over to it. If I back up and restore to an
empty disk location w/ move, I get the existing structure. If I pre-
create the physical structure and restore over it with a move, it will
still overwrite the files and leave me with a copy of the old bad
structure. I know I can go ahead with the copy with move, and then run
the appropriate shrinks and resizing against the copy, but I'd like to
do it in a single process in the interest of time and safety. I've
considered the copy database wizard, but I'm not sure of all of the
ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
of the afternoon researching this in BOL and in 3rd party books. But I
would really appreciate any insights the group might offer and any
advance warnings about "gotcha's" and "show-stoppers."
Thanks in advance.
Graham (Pete) Berry
PeteBerry@.Caltech.edu
Pete
First of all run Upgrade Adviser (could be downloaded) on SQL Server 2000
to see what will not be able to work on SQL Server 2005. Then install SQL
Server 2005 and just restore a database . Make sure that you have
compatibily level of the database =90 and try to coonect thjrou the
application to see how is going on
"Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
news:MPG.20dcebecc608fc0d989686@.msnews.microsoft.c om...
> The situation is this. I have a "production test" system, which we are
> about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
> test fixes, code changes and the like, but it is effectively "pseudo
> production" so I have to be careful with it. In effect the user needs
> access to the current test data until the upgrade process is complete
> and accepted. Since this is my first major upgrade project I am a bit
> nervous. Now the issue --
> The current database is totally mis-sized, especially the Logfile which
> has been allowed to grow to well over 6G of which 3% to 5% is typically
> in use. So as a part of this process I would like to resize the target
> databases.
> My plan is to install a new instance of Sql2005 along-side the existing
> 2000 instance, on the same server and set of disks. But now I need to
> get a copy of the 2000 data over to it. If I back up and restore to an
> empty disk location w/ move, I get the existing structure. If I pre-
> create the physical structure and restore over it with a move, it will
> still overwrite the files and leave me with a copy of the old bad
> structure. I know I can go ahead with the copy with move, and then run
> the appropriate shrinks and resizing against the copy, but I'd like to
> do it in a single process in the interest of time and safety. I've
> considered the copy database wizard, but I'm not sure of all of the
> ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
> of the afternoon researching this in BOL and in 3rd party books. But I
> would really appreciate any insights the group might offer and any
> advance warnings about "gotcha's" and "show-stoppers."
> Thanks in advance.
> --
> Graham (Pete) Berry
> PeteBerry@.Caltech.edu
|||In article <#MAeoOMsHHA.4676@.TK2MSFTNGP03.phx.gbl>, urid@.iscar.co.il
says...
> Pete
> First of all run Upgrade Adviser (could be downloaded) on SQL Server 2000
> to see what will not be able to work on SQL Server 2005. Then install SQL
> Server 2005 and just restore a database . Make sure that you have
> compatibily level of the database =90 and try to coonect thjrou the
> application to see how is going on
>
>
>
> "Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
> news:MPG.20dcebecc608fc0d989686@.msnews.microsoft.c om...
>
>
Thanks Uri -- you are right on and that is already a part of the plan --
the open question remains about resizing and restoring.
Graham (Pete) Berry
PeteBerry@.Caltech.edu

An easy (but long) restore question

The situation is this. I have a "production test" system, which we are
about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
test fixes, code changes and the like, but it is effectively "pseudo
production" so I have to be careful with it. In effect the user needs
access to the current test data until the upgrade process is complete
and accepted. Since this is my first major upgrade project I am a bit
nervous. Now the issue --
The current database is totally mis-sized, especially the Logfile which
has been allowed to grow to well over 6G of which 3% to 5% is typically
in use. So as a part of this process I would like to resize the target
databases.
My plan is to install a new instance of Sql2005 along-side the existing
2000 instance, on the same server and set of disks. But now I need to
get a copy of the 2000 data over to it. If I back up and restore to an
empty disk location w/ move, I get the existing structure. If I pre-
create the physical structure and restore over it with a move, it will
still overwrite the files and leave me with a copy of the old bad
structure. I know I can go ahead with the copy with move, and then run
the appropriate shrinks and resizing against the copy, but I'd like to
do it in a single process in the interest of time and safety. I've
considered the copy database wizard, but I'm not sure of all of the
ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
of the afternoon researching this in BOL and in 3rd party books. But I
would really appreciate any insights the group might offer and any
advance warnings about "gotcha's" and "show-stoppers."
Thanks in advance.
--
Graham (Pete) Berry
PeteBerry@.Caltech.eduPete
First of all run Upgrade Adviser (could be downloaded) on SQL Server 2000
to see what will not be able to work on SQL Server 2005. Then install SQL
Server 2005 and just restore a database . Make sure that you have
compatibily level of the database =90 and try to coonect thjrou the
application to see how is going on
"Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
news:MPG.20dcebecc608fc0d989686@.msnews.microsoft.com...
> The situation is this. I have a "production test" system, which we are
> about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
> test fixes, code changes and the like, but it is effectively "pseudo
> production" so I have to be careful with it. In effect the user needs
> access to the current test data until the upgrade process is complete
> and accepted. Since this is my first major upgrade project I am a bit
> nervous. Now the issue --
> The current database is totally mis-sized, especially the Logfile which
> has been allowed to grow to well over 6G of which 3% to 5% is typically
> in use. So as a part of this process I would like to resize the target
> databases.
> My plan is to install a new instance of Sql2005 along-side the existing
> 2000 instance, on the same server and set of disks. But now I need to
> get a copy of the 2000 data over to it. If I back up and restore to an
> empty disk location w/ move, I get the existing structure. If I pre-
> create the physical structure and restore over it with a move, it will
> still overwrite the files and leave me with a copy of the old bad
> structure. I know I can go ahead with the copy with move, and then run
> the appropriate shrinks and resizing against the copy, but I'd like to
> do it in a single process in the interest of time and safety. I've
> considered the copy database wizard, but I'm not sure of all of the
> ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
> of the afternoon researching this in BOL and in 3rd party books. But I
> would really appreciate any insights the group might offer and any
> advance warnings about "gotcha's" and "show-stoppers."
> Thanks in advance.
> --
> Graham (Pete) Berry
> PeteBerry@.Caltech.edu|||In article <#MAeoOMsHHA.4676@.TK2MSFTNGP03.phx.gbl>, urid@.iscar.co.il
says...
> Pete
> First of all run Upgrade Adviser (could be downloaded) on SQL Server 200
0
> to see what will not be able to work on SQL Server 2005. Then install SQL
> Server 2005 and just restore a database . Make sure that you have
> compatibily level of the database =90 and try to coonect thjrou the
> application to see how is going on
>
>
>
> "Pete Berry" <PeteBerry@.Caltech.edu> wrote in message
> news:MPG.20dcebecc608fc0d989686@.msnews.microsoft.com...
>
>
Thanks Uri -- you are right on and that is already a part of the plan --
the open question remains about resizing and restoring.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu

An easy (but long) restore question

The situation is this. I have a "production test" system, which we are
about to upgrade from 2000SP4 to 2005SP2. The use of the database is to
test fixes, code changes and the like, but it is effectively "pseudo
production" so I have to be careful with it. In effect the user needs
access to the current test data until the upgrade process is complete
and accepted. Since this is my first major upgrade project I am a bit
nervous. Now the issue --
The current database is totally mis-sized, especially the Logfile which
has been allowed to grow to well over 6G of which 3% to 5% is typically
in use. So as a part of this process I would like to resize the target
databases.
My plan is to install a new instance of Sql2005 along-side the existing
2000 instance, on the same server and set of disks. But now I need to
get a copy of the 2000 data over to it. If I back up and restore to an
empty disk location w/ move, I get the existing structure. If I pre-
create the physical structure and restore over it with a move, it will
still overwrite the files and leave me with a copy of the old bad
structure. I know I can go ahead with the copy with move, and then run
the appropriate shrinks and resizing against the copy, but I'd like to
do it in a single process in the interest of time and safety. I've
considered the copy database wizard, but I'm not sure of all of the
ramifications of upgrading to 2005 using a 2000 wizard. I've spent most
of the afternoon researching this in BOL and in 3rd party books. But I
would really appreciate any insights the group might offer and any
advance warnings about "gotcha's" and "show-stoppers."
Thanks in advance.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edusorry for the duplicate post -- it took over 12 hours for the first one
to appear and I got impatient.
> --
> Graham (Pete) Berry
> PeteBerry@.Caltech.edu
>
Graham (Pete) Berry
PeteBerry@.Caltech.edu

Am I missing something - DBCC SHRINKFILE('NewFile', EMPTYFILE)

I create a filegroup, added a file to it and created a table with 1
varchar(256) column to it. I then added several rows of data. I wanted to
test removing the filegroup and can't. First I ran:
DBCC SHRINKFILE('NewFile', EMPTYFILE)
and received:
DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
UsedPages = 8, EstimatedPages = 8
I then run:
ALTER DATABASE TestDB REMOVE FILE NewFile
and get:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'Newfile' cannot be removed because it is not empty.
The table is not miving to another filegroup.
You have to create another file in the same filegroup to hold the table or
you have to move the table to another filegroup. DBCC SHRINKFILE will not
reassign objects to a different filegroup.
To answer the next question, create a clustered index on the table in the
target file group to move a table.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Charlie" <SQL@.newsgroups.nospam> wrote in message
news:DE1296AC-082F-48D9-97E8-8FC5AF1D5452@.microsoft.com...
>I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>
|||I have no idea why I thought it would magicaly reassign it to another
filegroup.
"Charlie" wrote:

> I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>

Am I missing something - DBCC SHRINKFILE('NewFile', EMPTYFILE)

I create a filegroup, added a file to it and created a table with 1
varchar(256) column to it. I then added several rows of data. I wanted to
test removing the filegroup and can't. First I ran:
DBCC SHRINKFILE('NewFile', EMPTYFILE)
and received:
DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
UsedPages = 8, EstimatedPages = 8
I then run:
ALTER DATABASE TestDB REMOVE FILE NewFile
and get:
Server: Msg 5042, Level 16, State 1, Line 1
The file 'Newfile' cannot be removed because it is not empty.
The table is not miving to another filegroup.You have to create another file in the same filegroup to hold the table or
you have to move the table to another filegroup. DBCC SHRINKFILE will not
reassign objects to a different filegroup.
To answer the next question, create a clustered index on the table in the
target file group to move a table.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Charlie" <SQL@.newsgroups.nospam> wrote in message
news:DE1296AC-082F-48D9-97E8-8FC5AF1D5452@.microsoft.com...
>I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>|||I have no idea why I thought it would magicaly reassign it to another
filegroup.
"Charlie" wrote:

> I create a filegroup, added a file to it and created a table with 1
> varchar(256) column to it. I then added several rows of data. I wanted to
> test removing the filegroup and can't. First I ran:
> DBCC SHRINKFILE('NewFile', EMPTYFILE)
> and received:
> DbId = 34, FileId = 8, CurrentSize = 13056, MinimumSize = 1280,
> UsedPages = 8, EstimatedPages = 8
> I then run:
> ALTER DATABASE TestDB REMOVE FILE NewFile
> and get:
> Server: Msg 5042, Level 16, State 1, Line 1
> The file 'Newfile' cannot be removed because it is not empty.
> The table is not miving to another filegroup.
>