Newbie Question - moving DB to new SQL Server

I need to move our existing DB from our old production server to a new
server.
More information:
- We are an 8-5, M-F operation, so downtime is allowable after-hours.
- The DB is about 14GB
- The current production server runs everything on a single system volume.
- The new server has a RAID1 set for the system, and a RAID5 set for the DB
files.
I understand from my reading so far that detach/attach might be the best
option for us. What I haven't found yet is a clear discussion of the impact
of changing the location of the DB files relative to where they used to
reside. In other words, I can't tell if detach/attach will make it either
automatic (best-case) or reasonably doable (perfectly acceptable) to update
all pointers to the new path of the database files.
Does anyone have links to resources that discuss this? My searches have
yeilded some good stuff so far, but still not quite what I'm looking for.
If it matters, the old server runs 2000 Server, the new server is running
Server 2003. The new server will have a clean install of SQL 2000 SP3.
Thanks in advance,
Bryan
Hi,
See the below URL's
http://support.microsoft.com/default...b;en-us;224071
http://support.microsoft.com/default...b;en-us;314546
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q240872
Thanks
Hari
SQL Server MVP
"Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
news:%23$%23Lsx6lFHA.360@.TK2MSFTNGP09.phx.gbl...
>I need to move our existing DB from our old production server to a new
>server.
> More information:
> - We are an 8-5, M-F operation, so downtime is allowable after-hours.
> - The DB is about 14GB
> - The current production server runs everything on a single system volume.
> - The new server has a RAID1 set for the system, and a RAID5 set for the
> DB files.
> I understand from my reading so far that detach/attach might be the best
> option for us. What I haven't found yet is a clear discussion of the
> impact of changing the location of the DB files relative to where they
> used to reside. In other words, I can't tell if detach/attach will make
> it either automatic (best-case) or reasonably doable (perfectly
> acceptable) to update all pointers to the new path of the database files.
> Does anyone have links to resources that discuss this? My searches have
> yeilded some good stuff so far, but still not quite what I'm looking for.
> If it matters, the old server runs 2000 Server, the new server is running
> Server 2003. The new server will have a clean install of SQL 2000 SP3.
> Thanks in advance,
> Bryan
>
|||"Bryan L" wrote:

> I need to move our existing DB from our old production server to a new
> server.
> More information:
> - We are an 8-5, M-F operation, so downtime is allowable after-hours.
> - The DB is about 14GB
> - The current production server runs everything on a single system volume.
> - The new server has a RAID1 set for the system, and a RAID5 set for the DB
> files.
> I understand from my reading so far that detach/attach might be the best
> option for us. What I haven't found yet is a clear discussion of the impact
> of changing the location of the DB files relative to where they used to
> reside. In other words, I can't tell if detach/attach will make it either
> automatic (best-case) or reasonably doable (perfectly acceptable) to update
> all pointers to the new path of the database files.
> Does anyone have links to resources that discuss this? My searches have
> yeilded some good stuff so far, but still not quite what I'm looking for.
> If it matters, the old server runs 2000 Server, the new server is running
> Server 2003. The new server will have a clean install of SQL 2000 SP3.
> Thanks in advance,
> Bryan
>
Hi Brian,
Just a quick question, is there any reason why you're using RAID 5 on your
new server for your database files? I believe (and I may be wrong so anyone
else who knows better please do correct me) that RAID 1+0 is the best way to
go for database files.
Our SQL Servers here are all operating on RAID 1+0 in 3 sets of 2 discs.
One for OS; one for SQL Server .exe files and data files, and one for
Transaction Log files. This seems to serve us pretty well from a performance
point of view.
Cheers,
Ian
|||did you hear "=?Utf-8?B?SWFuIE11cnBoeQ==?="
<IanMurphy@.discussions.microsoft.com> say in
news:7A234F86-809B-4C2E-8F85-7B189450CE27@.microsoft.com:

> Just a quick question, is there any reason why you're using RAID 5 on
> your new server for your database files? I believe (and I may be
> wrong so anyone else who knows better please do correct me) that RAID
> 1+0 is the best way to go for database files.
while I agree, there is a cost issue that may prevent RAID 1+0 (or 0+1 -
they are different). 3 RAID 5 disks will be cheaper than 12 (in your
case) RAID 1+0 disks.
Also some SAN solutions mitigate the need by doing a virtual mirroring or
striping before adding in a RAID 5 volume.
my 2
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||did you hear "Hari Prasad" <hari_prasad_k@.hotmail.com> say in
news:e4sYt26lFHA.1372@.TK2MSFTNGP10.phx.gbl:

> Hi,
> See the below URL's
> http://support.microsoft.com/default...b;en-us;224071
> http://support.microsoft.com/default...b;en-us;314546
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q240872
>
> Thanks
> Hari
> SQL Server MVP
additionally:
http://vyaskn.tripod.com/moving_sql_server.htm
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||> Hi Brian,
> Just a quick question, is there any reason why you're using RAID 5 on your
> new server for your database files? I believe (and I may be wrong so
> anyone
> else who knows better please do correct me) that RAID 1+0 is the best way
> to
> go for database files.
> Our SQL Servers here are all operating on RAID 1+0 in 3 sets of 2 discs.
> One for OS; one for SQL Server .exe files and data files, and one for
> Transaction Log files. This seems to serve us pretty well from a
> performance
> point of view.
> Cheers,
> Ian
Yep. The reason is, the server was ordered and configured with a RAID 5 set
before I became fully aware that RAID 1+0 was being used in the Enterprise.
I would not at all mind running RAID 1+0 instead. I actually ordered two
new servers, and I can't remember exactly how they came configured; it may
be that I can rearrange the RAID levels and swap drives around so I can run
RAID 1+0 for my DB Files. I'm afraid, though I can't manage another 1+0 set
just for the transaction logs; would it be better to keep those on the 1+0
set with the DB files, or put them on the OS RAID1 volume?
Although drives are cheap, I don't think management would look too kindly on
ordering additional new drives for the new servers we just barely began
using. We are supporting fewer than 30 users; is there threshold below
which separating the logs out to a separate volume doesn't really gain you
much in performance?
Bryan
|||...just a brief comment...
We are currently looking at a reconfguration/upgrade of our Storage used for
our major SQL server. We are using an IBM FastT 650 Turbo storage and had a
meeting with a storage expert from IBM. He gave us some recommendations of
how we could reconfigure the storage, but on top of that he said that the
performance difference between RAID 5 and RAID 1/10/0+1 is getting smaller
and smaller with a modern storage. We actually ended up putting our Database
on a RAID 5 arrary and then the logs on a RAID 1. Actually he wouldn't be
afraid of having the logfiles on a RAID 5 array as well. Placing the log
files on a RAID 10/0+1 would in his opinion be overkill from a performance
point of view.
This might be different depending on how the usage are on your system (we
have far more reads than writes...) and also if you are using internal
disk/controllers and if you are using an external storage.
Regards
Steen
Bryan L wrote:
> Yep. The reason is, the server was ordered and configured with a
> RAID 5 set before I became fully aware that RAID 1+0 was being used
> in the Enterprise. I would not at all mind running RAID 1+0 instead. I
> actually ordered two new servers, and I can't remember exactly how
> they came configured; it may be that I can rearrange the RAID levels
> and swap drives around so I can run RAID 1+0 for my DB Files. I'm
> afraid, though I can't manage another 1+0 set just for the
> transaction logs; would it be better to keep those on the 1+0 set
> with the DB files, or put them on the OS RAID1 volume?
> Although drives are cheap, I don't think management would look too
> kindly on ordering additional new drives for the new servers we just
> barely began using. We are supporting fewer than 30 users; is there
> threshold below which separating the logs out to a separate volume
> doesn't really gain you much in performance?
> Bryan

Newbie Question - moving DB to new SQL Server

I need to move our existing DB from our old production server to a new
server.
More information:
- We are an 8-5, M-F operation, so downtime is allowable after-hours.
- The DB is about 14GB
- The current production server runs everything on a single system volume.
- The new server has a RAID1 set for the system, and a RAID5 set for the DB
files.
I understand from my reading so far that detach/attach might be the best
option for us. What I haven't found yet is a clear discussion of the impact
of changing the location of the DB files relative to where they used to
reside. In other words, I can't tell if detach/attach will make it either
automatic (best-case) or reasonably doable (perfectly acceptable) to update
all pointers to the new path of the database files.
Does anyone have links to resources that discuss this? My searches have
yeilded some good stuff so far, but still not quite what I'm looking for.
If it matters, the old server runs 2000 Server, the new server is running
Server 2003. The new server will have a clean install of SQL 2000 SP3.
Thanks in advance,
BryanHi,
See the below URL's
http://support.microsoft.com/defaul...kb;en-us;224071
http://support.microsoft.com/defaul...kb;en-us;314546
http://support.microsoft.com/defaul...scid=kb;[LN];Q240872
Thanks
Hari
SQL Server MVP
"Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
news:%23$%23Lsx6lFHA.360@.TK2MSFTNGP09.phx.gbl...
>I need to move our existing DB from our old production server to a new
>server.
> More information:
> - We are an 8-5, M-F operation, so downtime is allowable after-hours.
> - The DB is about 14GB
> - The current production server runs everything on a single system volume.
> - The new server has a RAID1 set for the system, and a RAID5 set for the
> DB files.
> I understand from my reading so far that detach/attach might be the best
> option for us. What I haven't found yet is a clear discussion of the
> impact of changing the location of the DB files relative to where they
> used to reside. In other words, I can't tell if detach/attach will make
> it either automatic (best-case) or reasonably doable (perfectly
> acceptable) to update all pointers to the new path of the database files.
> Does anyone have links to resources that discuss this? My searches have
> yeilded some good stuff so far, but still not quite what I'm looking for.
> If it matters, the old server runs 2000 Server, the new server is running
> Server 2003. The new server will have a clean install of SQL 2000 SP3.
> Thanks in advance,
> Bryan
>|||"Bryan L" wrote:

> I need to move our existing DB from our old production server to a new
> server.
> More information:
> - We are an 8-5, M-F operation, so downtime is allowable after-hours.
> - The DB is about 14GB
> - The current production server runs everything on a single system volume.
> - The new server has a RAID1 set for the system, and a RAID5 set for the D
B
> files.
> I understand from my reading so far that detach/attach might be the best
> option for us. What I haven't found yet is a clear discussion of the impa
ct
> of changing the location of the DB files relative to where they used to
> reside. In other words, I can't tell if detach/attach will make it either
> automatic (best-case) or reasonably doable (perfectly acceptable) to updat
e
> all pointers to the new path of the database files.
> Does anyone have links to resources that discuss this? My searches have
> yeilded some good stuff so far, but still not quite what I'm looking for.
> If it matters, the old server runs 2000 Server, the new server is running
> Server 2003. The new server will have a clean install of SQL 2000 SP3.
> Thanks in advance,
> Bryan
>
Hi Brian,
Just a quick question, is there any reason why you're using RAID 5 on your
new server for your database files? I believe (and I may be wrong so anyone
else who knows better please do correct me) that RAID 1+0 is the best way to
go for database files.
Our SQL Servers here are all operating on RAID 1+0 in 3 sets of 2 discs.
One for OS; one for SQL Server .exe files and data files, and one for
Transaction Log files. This seems to serve us pretty well from a performanc
e
point of view.
Cheers,
Ian|||did you hear "examnotes"
<IanMurphy@.discussions.microsoft.com> say in
news:7A234F86-809B-4C2E-8F85-7B189450CE27@.microsoft.com:

> Just a quick question, is there any reason why you're using RAID 5 on
> your new server for your database files? I believe (and I may be
> wrong so anyone else who knows better please do correct me) that RAID
> 1+0 is the best way to go for database files.
while I agree, there is a cost issue that may prevent RAID 1+0 (or 0+1 -
they are different). 3 RAID 5 disks will be cheaper than 12 (in your
case) RAID 1+0 disks.
Also some SAN solutions mitigate the need by doing a virtual mirroring or
striping before adding in a RAID 5 volume.
my 2
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear "Hari Prasad" <hari_prasad_k@.hotmail.com> say in
news:e4sYt26lFHA.1372@.TK2MSFTNGP10.phx.gbl:

> Hi,
> See the below URL's
> http://support.microsoft.com/defaul...kb;en-us;224071
> http://support.microsoft.com/defaul...kb;en-us;314546
> http://support.microsoft.com/defaul...scid=kb;[LN];Q240872
>
> Thanks
> Hari
> SQL Server MVP
additionally:
http://vyaskn.tripod.com/moving_sql_server.htm
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||> Hi Brian,
> Just a quick question, is there any reason why you're using RAID 5 on your
> new server for your database files? I believe (and I may be wrong so
> anyone
> else who knows better please do correct me) that RAID 1+0 is the best way
> to
> go for database files.
> Our SQL Servers here are all operating on RAID 1+0 in 3 sets of 2 discs.
> One for OS; one for SQL Server .exe files and data files, and one for
> Transaction Log files. This seems to serve us pretty well from a
> performance
> point of view.
> Cheers,
> Ian
Yep. The reason is, the server was ordered and configured with a RAID 5 set
before I became fully aware that RAID 1+0 was being used in the Enterprise.
I would not at all mind running RAID 1+0 instead. I actually ordered two
new servers, and I can't remember exactly how they came configured; it may
be that I can rearrange the RAID levels and swap drives around so I can run
RAID 1+0 for my DB Files. I'm afraid, though I can't manage another 1+0 set
just for the transaction logs; would it be better to keep those on the 1+0
set with the DB files, or put them on the OS RAID1 volume?
Although drives are cheap, I don't think management would look too kindly on
ordering additional new drives for the new servers we just barely began
using. We are supporting fewer than 30 users; is there threshold below
which separating the logs out to a separate volume doesn't really gain you
much in performance?
Bryan|||...just a brief comment...
We are currently looking at a reconfguration/upgrade of our Storage used for
our major SQL server. We are using an IBM FastT 650 Turbo storage and had a
meeting with a storage expert from IBM. He gave us some recommendations of
how we could reconfigure the storage, but on top of that he said that the
performance difference between RAID 5 and RAID 1/10/0+1 is getting smaller
and smaller with a modern storage. We actually ended up putting our Database
on a RAID 5 arrary and then the logs on a RAID 1. Actually he wouldn't be
afraid of having the logfiles on a RAID 5 array as well. Placing the log
files on a RAID 10/0+1 would in his opinion be overkill from a performance
point of view.
This might be different depending on how the usage are on your system (we
have far more reads than writes...) and also if you are using internal
disk/controllers and if you are using an external storage.
Regards
Steen
Bryan L wrote:
> Yep. The reason is, the server was ordered and configured with a
> RAID 5 set before I became fully aware that RAID 1+0 was being used
> in the Enterprise. I would not at all mind running RAID 1+0 instead. I
> actually ordered two new servers, and I can't remember exactly how
> they came configured; it may be that I can rearrange the RAID levels
> and swap drives around so I can run RAID 1+0 for my DB Files. I'm
> afraid, though I can't manage another 1+0 set just for the
> transaction logs; would it be better to keep those on the 1+0 set
> with the DB files, or put them on the OS RAID1 volume?
> Although drives are cheap, I don't think management would look too
> kindly on ordering additional new drives for the new servers we just
> barely began using. We are supporting fewer than 30 users; is there
> threshold below which separating the logs out to a separate volume
> doesn't really gain you much in performance?
> Bryansql

Newbie Question - moving DB to new SQL Server

I need to move our existing DB from our old production server to a new
server.
More information:
- We are an 8-5, M-F operation, so downtime is allowable after-hours.
- The DB is about 14GB
- The current production server runs everything on a single system volume.
- The new server has a RAID1 set for the system, and a RAID5 set for the DB
files.
I understand from my reading so far that detach/attach might be the best
option for us. What I haven't found yet is a clear discussion of the impact
of changing the location of the DB files relative to where they used to
reside. In other words, I can't tell if detach/attach will make it either
automatic (best-case) or reasonably doable (perfectly acceptable) to update
all pointers to the new path of the database files.
Does anyone have links to resources that discuss this? My searches have
yeilded some good stuff so far, but still not quite what I'm looking for.
If it matters, the old server runs 2000 Server, the new server is running
Server 2003. The new server will have a clean install of SQL 2000 SP3.
Thanks in advance,
BryanHi,
See the below URL's
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q240872
Thanks
Hari
SQL Server MVP
"Bryan L" <blinton.nospam@.connellinsurance.nospam.com> wrote in message
news:%23$%23Lsx6lFHA.360@.TK2MSFTNGP09.phx.gbl...
>I need to move our existing DB from our old production server to a new
>server.
> More information:
> - We are an 8-5, M-F operation, so downtime is allowable after-hours.
> - The DB is about 14GB
> - The current production server runs everything on a single system volume.
> - The new server has a RAID1 set for the system, and a RAID5 set for the
> DB files.
> I understand from my reading so far that detach/attach might be the best
> option for us. What I haven't found yet is a clear discussion of the
> impact of changing the location of the DB files relative to where they
> used to reside. In other words, I can't tell if detach/attach will make
> it either automatic (best-case) or reasonably doable (perfectly
> acceptable) to update all pointers to the new path of the database files.
> Does anyone have links to resources that discuss this? My searches have
> yeilded some good stuff so far, but still not quite what I'm looking for.
> If it matters, the old server runs 2000 Server, the new server is running
> Server 2003. The new server will have a clean install of SQL 2000 SP3.
> Thanks in advance,
> Bryan
>|||"Bryan L" wrote:
> I need to move our existing DB from our old production server to a new
> server.
> More information:
> - We are an 8-5, M-F operation, so downtime is allowable after-hours.
> - The DB is about 14GB
> - The current production server runs everything on a single system volume.
> - The new server has a RAID1 set for the system, and a RAID5 set for the DB
> files.
> I understand from my reading so far that detach/attach might be the best
> option for us. What I haven't found yet is a clear discussion of the impact
> of changing the location of the DB files relative to where they used to
> reside. In other words, I can't tell if detach/attach will make it either
> automatic (best-case) or reasonably doable (perfectly acceptable) to update
> all pointers to the new path of the database files.
> Does anyone have links to resources that discuss this? My searches have
> yeilded some good stuff so far, but still not quite what I'm looking for.
> If it matters, the old server runs 2000 Server, the new server is running
> Server 2003. The new server will have a clean install of SQL 2000 SP3.
> Thanks in advance,
> Bryan
>
Hi Brian,
Just a quick question, is there any reason why you're using RAID 5 on your
new server for your database files? I believe (and I may be wrong so anyone
else who knows better please do correct me) that RAID 1+0 is the best way to
go for database files.
Our SQL Servers here are all operating on RAID 1+0 in 3 sets of 2 discs.
One for OS; one for SQL Server .exe files and data files, and one for
Transaction Log files. This seems to serve us pretty well from a performance
point of view.
Cheers,
Ian|||did you hear "=?Utf-8?B?SWFuIE11cnBoeQ==?="
<IanMurphy@.discussions.microsoft.com> say in
news:7A234F86-809B-4C2E-8F85-7B189450CE27@.microsoft.com:
> Just a quick question, is there any reason why you're using RAID 5 on
> your new server for your database files? I believe (and I may be
> wrong so anyone else who knows better please do correct me) that RAID
> 1+0 is the best way to go for database files.
while I agree, there is a cost issue that may prevent RAID 1+0 (or 0+1 -
they are different). 3 RAID 5 disks will be cheaper than 12 (in your
case) RAID 1+0 disks.
Also some SAN solutions mitigate the need by doing a virtual mirroring or
striping before adding in a RAID 5 volume.
my 2¢
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||did you hear "Hari Prasad" <hari_prasad_k@.hotmail.com> say in
news:e4sYt26lFHA.1372@.TK2MSFTNGP10.phx.gbl:
> Hi,
> See the below URL's
> http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> http://support.microsoft.com/default.aspx?scid=kb;[LN];Q240872
>
> Thanks
> Hari
> SQL Server MVP
additionally:
http://vyaskn.tripod.com/moving_sql_server.htm
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||> Hi Brian,
> Just a quick question, is there any reason why you're using RAID 5 on your
> new server for your database files? I believe (and I may be wrong so
> anyone
> else who knows better please do correct me) that RAID 1+0 is the best way
> to
> go for database files.
> Our SQL Servers here are all operating on RAID 1+0 in 3 sets of 2 discs.
> One for OS; one for SQL Server .exe files and data files, and one for
> Transaction Log files. This seems to serve us pretty well from a
> performance
> point of view.
> Cheers,
> Ian
Yep. The reason is, the server was ordered and configured with a RAID 5 set
before I became fully aware that RAID 1+0 was being used in the Enterprise.
I would not at all mind running RAID 1+0 instead. I actually ordered two
new servers, and I can't remember exactly how they came configured; it may
be that I can rearrange the RAID levels and swap drives around so I can run
RAID 1+0 for my DB Files. I'm afraid, though I can't manage another 1+0 set
just for the transaction logs; would it be better to keep those on the 1+0
set with the DB files, or put them on the OS RAID1 volume?
Although drives are cheap, I don't think management would look too kindly on
ordering additional new drives for the new servers we just barely began
using. We are supporting fewer than 30 users; is there threshold below
which separating the logs out to a separate volume doesn't really gain you
much in performance?
Bryan|||...just a brief comment...
We are currently looking at a reconfguration/upgrade of our Storage used for
our major SQL server. We are using an IBM FastT 650 Turbo storage and had a
meeting with a storage expert from IBM. He gave us some recommendations of
how we could reconfigure the storage, but on top of that he said that the
performance difference between RAID 5 and RAID 1/10/0+1 is getting smaller
and smaller with a modern storage. We actually ended up putting our Database
on a RAID 5 arrary and then the logs on a RAID 1. Actually he wouldn't be
afraid of having the logfiles on a RAID 5 array as well. Placing the log
files on a RAID 10/0+1 would in his opinion be overkill from a performance
point of view.
This might be different depending on how the usage are on your system (we
have far more reads than writes...) and also if you are using internal
disk/controllers and if you are using an external storage.
Regards
Steen
Bryan L wrote:
>> Hi Brian,
>> Just a quick question, is there any reason why you're using RAID 5
>> on your new server for your database files? I believe (and I may be
>> wrong so anyone
>> else who knows better please do correct me) that RAID 1+0 is the
>> best way to
>> go for database files.
>> Our SQL Servers here are all operating on RAID 1+0 in 3 sets of 2
>> discs. One for OS; one for SQL Server .exe files and data files, and
>> one for Transaction Log files. This seems to serve us pretty well
>> from a performance
>> point of view.
>> Cheers,
>> Ian
> Yep. The reason is, the server was ordered and configured with a
> RAID 5 set before I became fully aware that RAID 1+0 was being used
> in the Enterprise. I would not at all mind running RAID 1+0 instead. I
> actually ordered two new servers, and I can't remember exactly how
> they came configured; it may be that I can rearrange the RAID levels
> and swap drives around so I can run RAID 1+0 for my DB Files. I'm
> afraid, though I can't manage another 1+0 set just for the
> transaction logs; would it be better to keep those on the 1+0 set
> with the DB files, or put them on the OS RAID1 volume?
> Although drives are cheap, I don't think management would look too
> kindly on ordering additional new drives for the new servers we just
> barely began using. We are supporting fewer than 30 users; is there
> threshold below which separating the logs out to a separate volume
> doesn't really gain you much in performance?
> Bryan

NEWBIE Question - How do I take an existing Database and Create an exact Copy of it under

Ladies / Gentlemen
How do I take an existing Database and Create an Exact Copy of it
under a new Database Name and having the name of the Database .MDF and .LOG
files changed also?
Thanks for your help.
Mark Moss
How about backup/restore (using the MOVE option for the RESTORE command)?
Or detach, copy files, then attach?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mark Moss" <markmoss@.adelphia.net> wrote in message news:OGyb3Q%23mHHA.668@.TK2MSFTNGP05.phx.gbl...
> Ladies / Gentlemen
>
> How do I take an existing Database and Create an Exact Copy of it under a new Database Name
> and having the name of the Database .MDF and .LOG files changed also?
>
> Thanks for your help.
>
> Mark Moss
>
|||Hi Mark
Since you did not mention your version number I will assume SQL Server 2005.
Please refer to the BOL (Books Online) documentation for syntax details:
Detach the original database using sp_detach_db
Copy the .mdf and .ldf files and give them new names.
Use CREATE DATABASE ... FOR ATTACH to create the new database using the
copied files.
Also see the following in BOL:
How to: Move a Database Using Detach and Attach (Transact-SQL)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Mark Moss" <markmoss@.adelphia.net> wrote in message
news:OGyb3Q%23mHHA.668@.TK2MSFTNGP05.phx.gbl...
> Ladies / Gentlemen
>
> How do I take an existing Database and Create an Exact Copy of it
> under a new Database Name and having the name of the Database .MDF and
> .LOG files changed also?
>
> Thanks for your help.
>
> Mark Moss
>
|||Another new thing for me! "... FOR ATTACH" option of CREATE DATABASE.
TheSQLGuru
President
Indicium Resources, Inc.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ul1ggW%23mHHA.5032@.TK2MSFTNGP02.phx.gbl...
> Hi Mark
> Since you did not mention your version number I will assume SQL Server
> 2005.
> Please refer to the BOL (Books Online) documentation for syntax details:
>
> Detach the original database using sp_detach_db
> Copy the .mdf and .ldf files and give them new names.
> Use CREATE DATABASE ... FOR ATTACH to create the new database using the
> copied files.
> Also see the following in BOL:
> How to: Move a Database Using Detach and Attach (Transact-SQL)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Mark Moss" <markmoss@.adelphia.net> wrote in message
> news:OGyb3Q%23mHHA.668@.TK2MSFTNGP05.phx.gbl...
>

NEWBIE Question - How do I take an existing Database and Create an exact Copy of it under

Ladies / Gentlemen
How do I take an existing Database and Create an Exact Copy of it
under a new Database Name and having the name of the Database .MDF and .LOG
files changed also?
Thanks for your help.
Mark MossHow about backup/restore (using the MOVE option for the RESTORE command)?
Or detach, copy files, then attach?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mark Moss" <markmoss@.adelphia.net> wrote in message news:OGyb3Q%23mHHA.668@.TK2MSFTNGP05.phx.gbl...
> Ladies / Gentlemen
>
> How do I take an existing Database and Create an Exact Copy of it under a new Database Name
> and having the name of the Database .MDF and .LOG files changed also?
>
> Thanks for your help.
>
> Mark Moss
>|||Hi Mark
Since you did not mention your version number I will assume SQL Server 2005.
Please refer to the BOL (Books Online) documentation for syntax details:
Detach the original database using sp_detach_db
Copy the .mdf and .ldf files and give them new names.
Use CREATE DATABASE ... FOR ATTACH to create the new database using the
copied files.
Also see the following in BOL:
How to: Move a Database Using Detach and Attach (Transact-SQL)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Mark Moss" <markmoss@.adelphia.net> wrote in message
news:OGyb3Q%23mHHA.668@.TK2MSFTNGP05.phx.gbl...
> Ladies / Gentlemen
>
> How do I take an existing Database and Create an Exact Copy of it
> under a new Database Name and having the name of the Database .MDF and
> .LOG files changed also?
>
> Thanks for your help.
>
> Mark Moss
>|||Another new thing for me! "... FOR ATTACH" option of CREATE DATABASE.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ul1ggW%23mHHA.5032@.TK2MSFTNGP02.phx.gbl...
> Hi Mark
> Since you did not mention your version number I will assume SQL Server
> 2005.
> Please refer to the BOL (Books Online) documentation for syntax details:
>
> Detach the original database using sp_detach_db
> Copy the .mdf and .ldf files and give them new names.
> Use CREATE DATABASE ... FOR ATTACH to create the new database using the
> copied files.
> Also see the following in BOL:
> How to: Move a Database Using Detach and Attach (Transact-SQL)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Mark Moss" <markmoss@.adelphia.net> wrote in message
> news:OGyb3Q%23mHHA.668@.TK2MSFTNGP05.phx.gbl...
>> Ladies / Gentlemen
>>
>> How do I take an existing Database and Create an Exact Copy of it
>> under a new Database Name and having the name of the Database .MDF and
>> .LOG files changed also?
>>
>> Thanks for your help.
>>
>> Mark Moss
>>
>

newbie query question

Hi,

I have a table [myRecipes] with 4 fields; Users can Modify existing recipes or add new recipes and my table stores the new values with a timestamp.

Question: Now that I have some sample data, I need a query or view to pull out each unique recipe in my table, but only return the latest and greatest recipe for each unique recipe.

[foodType] nvarchar

[recipeName] nvarchar

[lastSaved] datetime

[cupsOfSugar] float

Sample data:

foodType recipeName lastSaved cupsOfSugar

cookie, peanutButter, 3/1/2007, 1.0

cookie, peanutButter, 3/5/2007, 1.5

cookie, sugar, 2/28/2007, 5.0

How to:

What would be the query to return the latest and greatest recipes in my db? The resultset should return

cookie, sugar, 2/28/2007, 5

cookie, peanutButter, 3/5/2007, 1.5

...and not the original recipe for peanutButter Cookies with only 1.0 cups of sugar

thanks in advance,

bsierad

select TOP 1 foodType, recipeName, lastSaved, cupsOfSugar
FROM myRecipes Order by cupsOfSugar DESC --Returns greatest
UNION
select TOP 1 foodType, recipeName, lastSaved, cupsOfSugar

FROM myRecipes Order by lastSaved -- Returns latest

PS. Best forum for this question is Transact-SQL
|||

Thanks,

but, doesn't this only return one row?

I'm looking for:

For each unique foodType and recipeName, please return all the fields in my table, and, if there are any duplicate records with foodType and recipeName, please only return that record whose lastSaved field is the max for that particular set.

This table basically holds a history of all saved recipes created by the user, but he/she should only ever see the latest and greatest...

PS: The primary key on this table is foodType + recipeName + lastSaved

thanks again in advance,

ben

|||Check my response in TransactSQL

newbie query question

Hi,

I have a table [myRecipes] with 4 fields; Users can Modify existing recipes or add new recipes and my table stores the new values with a timestamp.

Question: Now that I have some sample data, I need a query or view to pull out each unique recipe in my table, but only return the latest and greatest recipe for each foodType / recipeName pair.

Primary Key = foodType + recipeName + lastSaved

[foodType] nvarchar

[recipeName] nvarchar

[lastSaved] datetime

[cupsOfSugar] float

Sample data:

foodType recipeName lastSaved cupsOfSugar

cookie, peanutButter, 3/1/2007, 1.0

cookie, peanutButter, 3/5/2007, 1.5

cookie, sugar, 2/28/2007, 5.0

How to:

What would be the query to return the latest and greatest recipes in my db? The resultset should return

cookie, sugar, 2/28/2007, 5

cookie, peanutButter, 3/5/2007, 1.5

...and not the original recipe for peanutButter Cookies with only 1.0 cups of sugar

thanks in advance,

bsierad

This should get you started. It will provide the lastest of each reciept variation.

SELECT
[FoodType],
[RecipeName],
max( [LastSaved] )
FROM [myRecipes]
GROUP BY
[FoodType],
[RecipeName]
ORDER BY
[FoodType],
[RecipeName]

This gets you the PK of each qualifying row, and then you could use it as a subquery or a JOIN derived table to get the remaining ingredients.

|||

Thanks, this really helps me out!

this is great...don't have to use a JOIN with this?

I'm under the impression subqueries as input to a parent query can only return one field?

I also did this:

SELECT FoodType,
recipeName,
LastSaved,
cupsOfSugar
FROM myRecipes q
WHERE cast(q.LastSaved as varchar(10)) in
(select MAX(cast(LastSaved as varchar(10)) ) from myRecipes
where FoodType= q.FoodType
and
recipeName= q.recipeName)

thanks again in advance,

bsierad

|||I was thinking as a sub-query in a WHERE clause to return the PK. Also, as a derived table for a JOIN.

Newbie needs to be told where to go! (Cross-Post)

Greetings:
I've been developing web applications with asp/sql and access for some
time now on existing servers. I am now in a situation that requires me to
install SQL Server (SBS 2K sp3) as the backend for an intranet served from
IIS. I've installed the SQL services and have created the database and I
have some of the web based application written, no problem, but I've yet to
be able to access the database that I created on the newly installed SQL
server from the web based applications. Even after setting up the virtual
directory with the IIS SQL snap-in, I only get a DNS Server not found error
when I try to browse the site. If I remove the IIS/SQL virtual directory, I
can browse pages that don't error out due to data references.
Where can I find a walk through for setting up SQL Server and IIS to
function together in this very basic manner?
Thanks in advance.
--
Jerry M. Gartner
www.gartnerwebdev.comHi,
Have a look at these...
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;176378
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;176380
http://support.microsoft.com/default.aspx?scid=kb;zh-
cn;chs264921
http://support.microsoft.com/default.aspx?scid=kb;PL;264921
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;245243
>--Original Message--
>Greetings:
> I've been developing web applications with asp/sql
and access for some
>time now on existing servers. I am now in a situation
that requires me to
>install SQL Server (SBS 2K sp3) as the backend for an
intranet served from
>IIS. I've installed the SQL services and have created
the database and I
>have some of the web based application written, no
problem, but I've yet to
>be able to access the database that I created on the
newly installed SQL
>server from the web based applications. Even after
setting up the virtual
>directory with the IIS SQL snap-in, I only get a DNS
Server not found error
>when I try to browse the site. If I remove the IIS/SQL
virtual directory, I
>can browse pages that don't error out due to data
references.
> Where can I find a walk through for setting up SQL
Server and IIS to
>function together in this very basic manner?
>Thanks in advance.
>
>--
>Jerry M. Gartner
>www.gartnerwebdev.com
>
>.
>|||Peter:
Preliminarily, this appears to be what I'm looking for. I didn't think
to use ODBC and a DSN. What are the major differences between ODBC and
"direct" connection? There will be under 30 clients using the DB - this is
an on demand intranet application and will be hit lightly and sporadically
throughout the day. The SQL server is also the PDC and fileserver/DFS root
and gateway. (2K SP4)
--
Jerry M. Gartner
www.gartnerwebdev.com
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:03de01c3a83a$14ef9140$a001280a@.phx.gbl...
> Hi,
> Have a look at these...
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;176378
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;176380
> http://support.microsoft.com/default.aspx?scid=kb;zh-
> cn;chs264921
> http://support.microsoft.com/default.aspx?scid=kb;PL;264921
> http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;245243