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 - step1 - importing MS access tables

Sorry - this question has probably been asked many times before as it
is about the first issue a newbie would hit.
I am moving a backend currently held in 5 (cos we keep exceeding file
size limit) MS access databases in to two SQL server databases .
After cleaning up a few data issues I am quite happily importing about
200 access tables - I have even set up some DTS packages to perform
the task.
Question:
Is there any tool available to assist in applying the indices ?
Something that scans the access tables and assembles a change sql
script for each table based on the indexing we were using under
access.
I wouldn't expect the scripts to run problem free - but just something
to cut down the slog work a bit.
A collection of cleaned up / refined scripts is what I would really
like at the end of the day - as I am testing all this out in my home
office and then would like to repeat it all at the client site.
I have had a look at the meta data facility but that does not appear
to pick up the MS Access indexing in the source databases
Many thanks in advance
Tony
With as many tables as you are talking about you could probably script
something.
Using DAO (yes.. the old old old Data Access Objects)
You can navigate your table hierarchy and pull the the index information out
that way. Then dump it to a file somewhere.
The hieararchy IIRC Goes:
DatabaseEngine
Workspaces
Databases
TableDefs
Indexes
Fields
Something like the following (Note, this is from memory and not tested, but
should get you on the right track).
HTH
Dim db as DAO.Database
Set db = CurrentDB()
Dim CreateIndex as String
Dim td as DAO.TableDef
Dim idx as DAO.Index
ForEach (td in db.TableDefs)
ForEach (idx in td.Indexes)
' Put your Create Index commands here.
' Add the fields involved in a loop as well. Something like
'Foreach (fld in idx.Fields)
Next
Next
Next
Rick Sawtell
MCT, MCSD, MCDBA
|||
>With as many tables as you are talking about you could probably script
>something.
>Using DAO <snip>
Thanks Rick
I was afraid that I would have to go down that path ... :-(
Your answer has been very helpful - because I was afraid I was
overlooking some really obvious, quick tool that everyone knew about -
but now I can go about writing the DAO code happy in the knowledge
that I am not wasting my time. :-)
Many thanks
Tony
ps I am ashamed to admit that I am still using DAO code for everything
I do.

Newbie - step1 - importing MS access tables

Sorry - this question has probably been asked many times before as it
is about the first issue a newbie would hit.
I am moving a backend currently held in 5 (cos we keep exceeding file
size limit) MS access databases in to two SQL server databases .
After cleaning up a few data issues I am quite happily importing about
200 access tables - I have even set up some DTS packages to perform
the task.
Question:
Is there any tool available to assist in applying the indices ?
Something that scans the access tables and assembles a change sql
script for each table based on the indexing we were using under
access.
I wouldn't expect the scripts to run problem free - but just something
to cut down the slog work a bit.
A collection of cleaned up / refined scripts is what I would really
like at the end of the day - as I am testing all this out in my home
office and then would like to repeat it all at the client site.
I have had a look at the meta data facility but that does not appear
to pick up the MS Access indexing in the source databases
Many thanks in advance
TonyWith as many tables as you are talking about you could probably script
something.
Using DAO (yes.. the old old old Data Access Objects)
You can navigate your table hierarchy and pull the the index information out
that way. Then dump it to a file somewhere.
The hieararchy IIRC Goes:
DatabaseEngine
Workspaces
Databases
TableDefs
Indexes
Fields
Something like the following (Note, this is from memory and not tested, but
should get you on the right track).
HTH
Dim db as DAO.Database
Set db = CurrentDB()
Dim CreateIndex as String
Dim td as DAO.TableDef
Dim idx as DAO.Index
ForEach (td in db.TableDefs)
ForEach (idx in td.Indexes)
' Put your Create Index commands here.
' Add the fields involved in a loop as well. Something like
'Foreach (fld in idx.Fields)
Next
Next
Next
Rick Sawtell
MCT, MCSD, MCDBA|||>With as many tables as you are talking about you could probably script
>something.
>Using DAO <snip>
Thanks Rick
I was afraid that I would have to go down that path ... :-(
Your answer has been very helpful - because I was afraid I was
overlooking some really obvious, quick tool that everyone knew about -
but now I can go about writing the DAO code happy in the knowledge
that I am not wasting my time. :-)
Many thanks
Tony
ps I am ashamed to admit that I am still using DAO code for everything
I do.

Newbie - ? on MsSQL

I had ms sql server 2000 in my PC. I am very new to ASP.NET as i have been working on PHP n MySQL for the past 3 years. Now i am moving to .NET framework 2. I installed visual studio express 2005 and then sql server 2005.

But now that i run sql 2000, it does connect me to localhost. but on sql 2005 i cant. can some one shade some light to this.

I have not used SQL server before and am just starting to learn it with ASP.NET 2. I am programmer that likes to code each and every line of codes but ASP.NET is a bit different. Well anyway, cheers

Try using a . instead of localhost in your connection string.

NEWB: Moving DBs to new install of SQL Serv. w/o sp_detach_db

Hi,
After a power outage (and UPS failure), my boss's SQL Server 2000 won't
start (or crashes quickly or something). We don't have any backups, as it's
a test server. Is there a way to import the database files into a new SQL
installation? By "installation," I mean either a re-installed SS, a second
installation on the same PC, or maybe even on another PC. In another words,
given only the files sitting on the harddrive, is there a way to "import"
the DBs in those files into SS?
Sp_attach_db would probably be the answer, except it seems to require
running sp_detach_db...which I cannot do, as SS is not working.
Thanks in advance,
JayYou could try sp_attach_single_file_db, though I believe that may require a
successful detach as well.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jay" <twentycavities@.hotmail.com> wrote in message
news:g2MCb.17882$Vg3.15076@.fe3.columbus.rr.com...
> Hi,
> After a power outage (and UPS failure), my boss's SQL Server 2000 won't
> start (or crashes quickly or something). We don't have any backups, as
it's
> a test server. Is there a way to import the database files into a new SQL
> installation? By "installation," I mean either a re-installed SS, a second
> installation on the same PC, or maybe even on another PC. In another
words,
> given only the files sitting on the harddrive, is there a way to "import"
> the DBs in those files into SS?
> Sp_attach_db would probably be the answer, except it seems to require
> running sp_detach_db...which I cannot do, as SS is not working.
>
> Thanks in advance,
> Jay
>
>|||Jay,
I dont see why you could not simply copy the MDF and LDF
files to another machine (or put in a safe place). Then
fix the issue with SQL2K not starting.
You should then be able to attache the database and log
file again via Enterprise Manager (right-click on
Databases/All tasks/Attach Database...).
Ron
>--Original Message--
>Hi,
>After a power outage (and UPS failure), my boss's SQL
Server 2000 won't
>start (or crashes quickly or something). We don't have
any backups, as it's
>a test server. Is there a way to import the database
files into a new SQL
>installation? By "installation," I mean either a re-
installed SS, a second
>installation on the same PC, or maybe even on another
PC. In another words,
>given only the files sitting on the harddrive, is there
a way to "import"
>the DBs in those files into SS?
>Sp_attach_db would probably be the answer, except it
seems to require
>running sp_detach_db...which I cannot do, as SS is not
working.
>
>Thanks in advance,
>Jay
>
>
>.
>|||I believe all he has to do is attach the databases. Find the old =databases and attach them. I have done this before and in fact will be =doing it again shortly. Attach through the GUI in your other SQL =Manager.
-- George Hester
__________________________________
"Jay" <twentycavities@.hotmail.com> wrote in message =news:g2MCb.17882$Vg3.15076@.fe3.columbus.rr.com...
> Hi,
> After a power outage (and UPS failure), my boss's SQL Server 2000 =won't
> start (or crashes quickly or something). We don't have any backups, as =it's
> a test server. Is there a way to import the database files into a new =SQL
> installation? By "installation," I mean either a re-installed SS, a =second
> installation on the same PC, or maybe even on another PC. In another =words,
> given only the files sitting on the harddrive, is there a way to ="import"
> the DBs in those files into SS?
> > Sp_attach_db would probably be the answer, except it seems to require
> running sp_detach_db...which I cannot do, as SS is not working.
> > > Thanks in advance,
> > Jay
> > > >|||>> I believe all he has to do is attach the databases.
This works fine when the databases have been detached successfully.
However, it's not always so easy when the "detach" wasn't clean (as in Jay's
case).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Well I have done it with no detaching. In fact I have to do it again =shortly. The Windows Server crashed and there is no reliable way I know =of to get SQL Server out of there functional and I need the databases. =So as I do it I'll probably remember the corners I had to cut to get it =to work. If he is interested I'll describe the steps here.
-- George Hester
__________________________________
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message =news:uGW9ANwwDHA.3428@.TK2MSFTNGP11.phx.gbl...
> >> I believe all he has to do is attach the databases.
> > This works fine when the databases have been detached successfully.
> However, it's not always so easy when the "detach" wasn't clean (as in =Jay's
> case).
> > -- > Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
> >|||> Well I have done it with no detaching.
I think you have been lucky, and this is certainly not guaranteed to work.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

NEWB: Moving DBs to new install of SQL Serv. w/o sp_detach_db

Hi,
After a power outage (and UPS failure), my boss's SQL Server 2000 won't
start (or crashes quickly or something). We don't have any backups, as it's
a test server. Is there a way to import the database files into a new SQL
installation? By "installation," I mean either a re-installed SS, a second
installation on the same PC, or maybe even on another PC. In another words,
given only the files sitting on the harddrive, is there a way to "import"
the DBs in those files into SS?

Sp_attach_db would probably be the answer, except it seems to require
running sp_detach_db...which I cannot do, as SS is not working.

Thanks in advance,

Jay[posted and mailed, please reply in news]

Jay (twentycavities@.hotmail.com) writes:
> After a power outage (and UPS failure), my boss's SQL Server 2000 won't
> start (or crashes quickly or something). We don't have any backups, as
> it's a test server. Is there a way to import the database files into a
> new SQL installation? By "installation," I mean either a re-installed
> SS, a second installation on the same PC, or maybe even on another PC.
> In another words, given only the files sitting on the harddrive, is
> there a way to "import" the DBs in those files into SS?
> Sp_attach_db would probably be the answer, except it seems to require
> running sp_detach_db...which I cannot do, as SS is not working.

sp_attach_db would indeed be the first thing to try. Since you did
not detach the database cleanly, it may not work. But it's worth to
give it a try. You most likely need both the data files and the log
files.

The other option to try is sp_attach_single_file_db. but if
sp_attach_db does not work, I would not place my bets on this one.

If that files, there is yet a way, but I wiill not post this method
here as it is too dangerous. Suffice to say that while you will be
able to access the database file this, you will get it exactly in
the state it was in at the time of the crash. So if this was a state
of inconsistency in the midst of a transaction, you have a mess of a
database.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <mHLCb.17818$Vg3.10491@.fe3.columbus.rr.com>,
twentycavities@.hotmail.com says...
> Sp_attach_db would probably be the answer, except it seems to require
> running sp_detach_db...which I cannot do, as SS is not working.

I got hung up on the same thing, but as Erland points out, it will
probably work. It did for me.

-- Rick