I've been trying to figure out how to make a copy of a database
newly upsized to SQL Server, and asked how to do it on
StackOverflow.com: 

http://tinyurl.com/26yhxuu =>
http://stackoverflow.com/questions/3612149/copy-sql-server-express-da
tabase-to-another-computer 

I have concluded that it's probably going to be a lot easier to just
import the data into an MDB file and then copy that to the USB drive
for transport. It then won't require installation of SQL Server on
the user's computer, for instance. 

I've already written code to import the ODBC tables (details not
important), but the show-stopper is that it's not importing indexes,
so these tables will be real dogs for data analysis. 

Is there some way to force TransferDatabase to import indexes from
ODBC? I'm concluding that there isn't, so I'm going to have to
instead import the data with INSERT queries into an empty template
database. 

Does everyone agree that it's impossible to import the indexes
(without mucking about with metadata about the ODBC database schema
and translating it into Jet indexes?)? 

[and another thing I discovered in doing this is that the
TransferDatabase help file has an error in it. Where it lists the
valid values for the second argument (DatabaseType), it reads "ODBC
Databases". It should be singular, "ODBC Database". Lots of people
have had an error with that, as it produces error 2507, and I didn't
encounter any explanation anywhere of how to resolve it. If you do
the same thing in the macro designer, you'll see the correct list of
values] 

-- 
David W. Fenton                  http://www.dfenton.com/ 
contact via website only     http://www.dfenton.com/DFA/

On Sep 1, 5:57=A0pm, "David W. Fenton" <NoEm...@SeeSignature.invalid>
wrote:
> I've been trying to figure out how to make a copy of a database
> newly upsized to SQL Server, and asked how to do it on
> StackOverflow.com:
>
> http://tinyurl.com/26yhxuu=3D>http://stackoverflow.com/questions/3612149/=
copy-sql-server-express-da
> tabase-to-another-computer
>
> I have concluded that it's probably going to be a lot easier to just
> import the data into an MDB file and then copy that to the USB drive
> for transport. It then won't require installation of SQL Server on
> the user's computer, for instance.
>
> I've already written code to import the ODBC tables (details not
> important), but the show-stopper is that it's not importing indexes,
> so these tables will be real dogs for data analysis.
>
> Is there some way to force TransferDatabase to import indexes from
> ODBC? I'm concluding that there isn't, so I'm going to have to
> instead import the data with INSERT queries into an empty template
> database.
>
> Does everyone agree that it's impossible to import the indexes
> (without mucking about with metadata about the ODBC database schema
> and translating it into Jet indexes?)?

I don't have a lot of experience with the TransferDatabase command
myself.  I always found it too slow.  I do remember however that with
lots of data, it's faster to transfer the data without the indexes
then add them in after the transfer is complete.

There are a couple of free ODBC SQL databases that will run on a USB
key without a install, for what it's worth.  SQLLite is one of them
(Firefox uses it as quite a few other programs), and I think MySQL
will run from a USB Drive without an install as well.  I remember
seeing a web development portable (thumb drive) app that included a
portable SQL Database, but I can't remember the name of it.  Try
portableapps.com or protablefreeware.com  It was probably on one of
those.  Since they are ODBC compliant, Access should be able to link
to them.  (I know I can do it with SQL Lite, though you have to
download the ODBC Drivers.)

The above said, I can't say as I'd recommend running the database on a
Thumb drive.  An external USB drive (with actual spinning platters) is
*much* faster.  USB Keys are great for transport, but other then that,
they suck for database work.  You can get external USB drives that fit
into your pocket as well.  They're bigger then a USB Key, but the
speed is worth it.  Pop TrueCrypt (in traveler mode) on them and
you've got a nice secure method of moving data around *and* using it
directly off the drive at a nice fast speed.

Chuck Grimsby <cgatgoo@gmail.com> wrote in
news:bafddf93-9c6a-4c98-a1e8-82fe89a086d0@x25g2000yqj.googlegroups.co
m: 

> On Sep 1, 5:57�pm, "David W. Fenton"
> <NoEm...@SeeSignature.invalid> wrote:
>> I've been trying to figure out how to make a copy of a database
>> newly upsized to SQL Server, and asked how to do it on
>> StackOverflow.com:
>>
>> http://tinyurl.com/26yhxuu=>http://stackoverflow.com/questions/361
>> 2149/ 
> copy-sql-server-express-da
>> tabase-to-another-computer
>>
>> I have concluded that it's probably going to be a lot easier to
>> just import the data into an MDB file and then copy that to the
>> USB drive for transport. It then won't require installation of
>> SQL Server on the user's computer, for instance.
>>
>> I've already written code to import the ODBC tables (details not
>> important), but the show-stopper is that it's not importing
>> indexes, so these tables will be real dogs for data analysis.
>>
>> Is there some way to force TransferDatabase to import indexes
>> from ODBC? I'm concluding that there isn't, so I'm going to have
>> to instead import the data with INSERT queries into an empty
>> template database.
>>
>> Does everyone agree that it's impossible to import the indexes
>> (without mucking about with metadata about the ODBC database
>> schema and translating it into Jet indexes?)?
> 
> I don't have a lot of experience with the TransferDatabase command
> myself.  I always found it too slow.  I do remember however that
> with lots of data, it's faster to transfer the data without the
> indexes then add them in after the transfer is complete.

It's not what I consider a lot of data (about 29MBs compacted --
that's data from 1989 to 2008, so it shows that it doesn't really
grow that much). 

The indexes have to be added back in for the tables to be usable, so
I don't really see how importing with no indexes then adding the
indexes is going to be preferable to just having the indexes in
place and running the import. Surely the overhead for the insert is
going to be pretty much equal to the amount of time it takes to
write the indexes upon creation (or close enough not to matter
much). 

> There are a couple of free ODBC SQL databases that will run on a
> USB key without a install, for what it's worth.

This is interesting, but I'm not seeing how this addresses the
problem I'm trying to solve. 

>  SQLLite is one of them
> (Firefox uses it as quite a few other programs), and I think MySQL
> will run from a USB Drive without an install as well.  I remember
> seeing a web development portable (thumb drive) app that included
> a portable SQL Database, but I can't remember the name of it.  Try
> portableapps.com or protablefreeware.com  It was probably on one
> of those.  Since they are ODBC compliant, Access should be able to
> link to them.  (I know I can do it with SQL Lite, though you have
> to download the ODBC Drivers.)

I can't see how running it from a USB drive makes it any easier for
me to copy the data from the SQL Server to the users PC for
statistical analysis. 

> The above said, I can't say as I'd recommend running the database
> on a Thumb drive.  An external USB drive (with actual spinning
> platters) is *much* faster.  USB Keys are great for transport, but
> other then that, they suck for database work.  You can get
> external USB drives that fit into your pocket as well.  They're
> bigger then a USB Key, but the speed is worth it.  Pop TrueCrypt
> (in traveler mode) on them and you've got a nice secure method of
> moving data around *and* using it directly off the drive at a nice
> fast speed. 

All very interesting, but I must be missing something: how do you
think all that addresses my question? Or were you just free
associating with some highly useful tangential information? 

-- 
David W. Fenton                  http://www.dfenton.com/ 
contact via website only     http://www.dfenton.com/DFA/

Have you considered using the osql utility to backup and restore the
db?. I think it's available when SQLServer is running.

Log into the command prompt as administrator.

The following command creates a backup  of database "FFDBA" on Server
"VOSTRO-EXPRESS\SQLEXPRESS" using Windows Security (-E) in the default
backup directory of the server. It takes about two seconds here.
Depending on Windows the SQLServer account may have very limited write
permissions and just using its default backup directory, by not
specifying any folder at all, may be easier than extending those
permissions. The default backup directory on my computer here is C:
\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup.

osql -S VOSTRO-EXPRESS\SQLEXPRESS -E
USE MASTER
GO
BACKUP DATABASE FFDBA TO DISK=3D'FFDBA.BAK'
GO
Exit

After copying the backup file to the new computer one can install it
so to speak using the same kind of commands.

RESTORE DATABASE FFDBA FROM DISK =3D 'C:\FFDBA.BAK'
Read permissions may not be as restricted as write permissions.

One can write these T-SQL commands to a simple text file and "execute"
that from osql; I suppose one could run these from Access using the
Shell command but I haven't done that.

The backup has everything worth copying I believe. It can be
compressed.

On Sep 1, 6:57=A0pm, "David W. Fenton" <NoEm...@SeeSignature.invalid>
wrote:
> I've been trying to figure out how to make a copy of a database
> newly upsized to SQL Server

David W. Fenton wrote:
> I've been trying to figure out how to make a copy of a database
> newly upsized to SQL Server, and asked how to do it on
> StackOverflow.com:
>
> http://tinyurl.com/26yhxuu =>
> http://stackoverflow.com/questions/3612149/copy-sql-server-express-da
> tabase-to-another-computer
>

And nobody mentioned sp_detach-db and sp_attach_db?

> I have concluded that it's probably going to be a lot easier to just
> import the data into an MDB file and then copy that to the USB drive
> for transport. It then won't require installation of SQL Server on
> the user's computer, for instance.
>
> I've already written code to import the ODBC tables (details not
> important), but the show-stopper is that it's not importing indexes,
> so these tables will be real dogs for data analysis.
>
> Is there some way to force TransferDatabase to import indexes from
> ODBC? I'm concluding that there isn't, so I'm going to have to
> instead import the data with INSERT queries into an empty template
> database.

No, TransferDatabase ignores indexes

>
> Does everyone agree that it's impossible to import the indexes
> (without mucking about with metadata about the ODBC database schema
> and translating it into Jet indexes?)?

Yes


You can detach the database from the server (using sp_detach_db), copy
the mdf and ldf files to your thumb drive, use sp_attach_db to reattach
the them to the server, carry the thumb drive to the other computer copy
them onto the destination drive and use sp_attach_db to attach them to
the destination server

Alternatively, you can use backup and restore as Lyle suggested.

-- 
HTH,
Bob Barrows

Ah! I should have read the thread in the link to see why my suggestion
was not acceptable.  Never mind.

Bob Barrows wrote:
> David W. Fenton wrote:
>> I've been trying to figure out how to make a copy of a database
>> newly upsized to SQL Server, and asked how to do it on
>> StackOverflow.com:
>>
>> http://tinyurl.com/26yhxuu =>
>> http://stackoverflow.com/questions/3612149/copy-sql-server-express-da
>> tabase-to-another-computer
>>
>
> And nobody mentioned sp_detach-db and sp_attach_db?
>
>> I have concluded that it's probably going to be a lot easier to just
>> import the data into an MDB file and then copy that to the USB drive
>> for transport. It then won't require installation of SQL Server on
>> the user's computer, for instance.
>>
>> I've already written code to import the ODBC tables (details not
>> important), but the show-stopper is that it's not importing indexes,
>> so these tables will be real dogs for data analysis.
>>
>> Is there some way to force TransferDatabase to import indexes from
>> ODBC? I'm concluding that there isn't, so I'm going to have to
>> instead import the data with INSERT queries into an empty template
>> database.
>
> No, TransferDatabase ignores indexes
>
>>
>> Does everyone agree that it's impossible to import the indexes
>> (without mucking about with metadata about the ODBC database schema
>> and translating it into Jet indexes?)?
>
> Yes
>
>
> You can detach the database from the server (using sp_detach_db), copy
> the mdf and ldf files to your thumb drive, use sp_attach_db to
> reattach the them to the server, carry the thumb drive to the other
> computer copy them onto the destination drive and use sp_attach_db to
> attach them to the destination server
>
> Alternatively, you can use backup and restore as Lyle suggested.

-- 
HTH,
Bob Barrows

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in
news:i5oes0$8il$1@news.eternal-september.org: 

> David W. Fenton wrote:
>> I've been trying to figure out how to make a copy of a database
>> newly upsized to SQL Server, and asked how to do it on
>> StackOverflow.com:
>>
>> http://tinyurl.com/26yhxuu =>
>> http://stackoverflow.com/questions/3612149/copy-sql-server-express
>> -da tabase-to-another-computer
> 
> And nobody mentioned sp_detach-db and sp_attach_db?

For what purpose? And how could it be used with all the conditions
involved? 

>> I have concluded that it's probably going to be a lot easier to
>> just import the data into an MDB file and then copy that to the
>> USB drive for transport. It then won't require installation of
>> SQL Server on the user's computer, for instance.
>>
>> I've already written code to import the ODBC tables (details not
>> important), but the show-stopper is that it's not importing
>> indexes, so these tables will be real dogs for data analysis.
>>
>> Is there some way to force TransferDatabase to import indexes
>> from ODBC? I'm concluding that there isn't, so I'm going to have
>> to instead import the data with INSERT queries into an empty
>> template database.
> 
> No, TransferDatabase ignores indexes

For ODBC, but not for Jet/ACE.

>> Does everyone agree that it's impossible to import the indexes
>> (without mucking about with metadata about the ODBC database
>> schema and translating it into Jet indexes?)?
> 
> Yes
> 
> You can detach the database from the server (using sp_detach_db),
> copy the mdf and ldf files to your thumb drive, use sp_attach_db
> to reattach the them to the server, carry the thumb drive to the
> other computer copy them onto the destination drive and use
> sp_attach_db to attach them to the destination server
> 
> Alternatively, you can use backup and restore as Lyle suggested.

Did you read any of the comments at all? It would appear not. This
is exactly what I suggested in the first place, but the point of
posting the question was to figure out HOW. The problem is that
doing so requires so many dependencies that I've concluded it's
probably easier to just create any MDB file from the data, copy it,
and then the target workstation doesn't even need SQL Server
installed. 

So, as of this point, I'm going to create an empty shell database
with empty indexed tables, then insert the data into that. I hope it
isn't significantly slower than the plain import. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
contact via website only     http://www.dfenton.com/DFA/

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in
news:i5ofl2$cjp$1@news.eternal-september.org: 

> Ah! I should have read the thread in the link to see why my
> suggestion was not acceptable.  Never mind.

That's why I posted the link, to save having to recapitulate
everything that had already been discussed. 

As I said in my previous reply, it seems obvious I'll have to import
the records into a shell database, with empty, indexed tables. It
remains to be seen how long this takes in comparison to the
TransferDatabase import. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
contact via website only     http://www.dfenton.com/DFA/

David W. Fenton wrote:
> I've been trying to figure out how to make a copy of a database
> newly upsized to SQL Server, and asked how to do it on
> StackOverflow.com:
>
> http://tinyurl.com/26yhxuu =>
> http://stackoverflow.com/questions/3612149/copy-sql-server-express-da
> tabase-to-another-computer
>
Wait a minute - the only thing keeping you from using the backup-restore
option is the lack of client tools on the client's machine? it's just
occurred to me that since the client has Access on his machine, you can
use VBA to run a passthrough query to run the sql statement to restore
the database on his SQL Express server, can't you? Take another look at
answer #4.

-- 
HTH,
Bob Barrows

Along the same line use VBA to create an ADODB connection and backup
and restore with T-SQL. If the computers are accessible on the LAN one
could probably backup directly to the "receiving" computer after a
little finagling with permissions. And I think, but am not sure,...
then connect to the receiving computer's server and restore the db,
all from the parent application ... maybe ... perhaps.
Properly programmed it should be just an automated procedure that runs
on the click of a button and unless the db is huge, requires only
seconds to complete.

On Sep 2, 2:40=A0pm, "Bob Barrows" <reb01...@NOyahoo.SPAMcom> wrote:
> David W. Fenton wrote:
> > I've been trying to figure out how to make a copy of a database
> > newly upsized to SQL Server, and asked how to do it on
> > StackOverflow.com:
>
> >http://tinyurl.com/26yhxuu=3D>
> >http://stackoverflow.com/questions/3612149/copy-sql-server-express-da
> > tabase-to-another-computer
>
> Wait a minute - the only thing keeping you from using the backup-restore
> option is the lack of client tools on the client's machine? it's just
> occurred to me that since the client has Access on his machine, you can
> use VBA to run a passthrough query to run the sql statement to restore
> the database on his SQL Express server, can't you? Take another look at
> answer #4.
>
> --
> HTH,
> Bob Barrows

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in
news:i5or3g$34c$1@news.eternal-september.org: 

> David W. Fenton wrote:
>> I've been trying to figure out how to make a copy of a database
>> newly upsized to SQL Server, and asked how to do it on
>> StackOverflow.com:
>>
>> http://tinyurl.com/26yhxuu =>
>> http://stackoverflow.com/questions/3612149/copy-sql-server-express
>> -da tabase-to-another-computer
>>
> Wait a minute - the only thing keeping you from using the
> backup-restore option is the lack of client tools on the client's
> machine? it's just occurred to me that since the client has Access
> on his machine, you can use VBA to run a passthrough query to run
> the sql statement to restore the database on his SQL Express
> server, can't you? Take another look at answer #4.

I don't want to make it dependent on the database, though, yes,
that's an option. 

I've since decided to not use SQL on the target machine at all. It's
a real pain installing SQL Server Express 2008 -- lots and lots of
dependencies that have to be installed ahead of time, and on a
workstation, it requires TWO reboots. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
contact via website only     http://www.dfenton.com/DFA/

On Sep 1, 8:45=A0pm, "David W. Fenton" <NoEm...@SeeSignature.invalid>
wrote:
> Chuck Grimsby <cgat...@gmail.com> wrote innews:bafddf93-9c6a-4c98-a1e8-82=
fe89a086d0@x25g2000yqj.googlegroups.co
> m:
> > On Sep 1, 5:57=A0pm, "David W. Fenton"
> > <NoEm...@SeeSignature.invalid> wrote:
> >> I've been trying to figure out how to make a copy of a database
> >> newly upsized to SQL Server, and asked how to do it on
> >> StackOverflow.com:
>
> >>http://tinyurl.com/26yhxuu=3D>http://stackoverflow.com/questions/361
> >> 2149/
> > copy-sql-server-express-da
> >> tabase-to-another-computer
>
> >> I have concluded that it's probably going to be a lot easier to
> >> just import the data into an MDB file and then copy that to the
> >> USB drive for transport. It then won't require installation of
> >> SQL Server on the user's computer, for instance.
>
> >> I've already written code to import the ODBC tables (details not
> >> important), but the show-stopper is that it's not importing
> >> indexes, so these tables will be real dogs for data analysis.
>
> >> Is there some way to force TransferDatabase to import indexes
> >> from ODBC? I'm concluding that there isn't, so I'm going to have
> >> to instead import the data with INSERT queries into an empty
> >> template database.
>
> >> Does everyone agree that it's impossible to import the indexes
> >> (without mucking about with metadata about the ODBC database
> >> schema and translating it into Jet indexes?)?
>
> > I don't have a lot of experience with the TransferDatabase command
> > myself. =A0I always found it too slow. =A0I do remember however that
> > with lots of data, it's faster to transfer the data without the
> > indexes then add them in after the transfer is complete.
>
> It's not what I consider a lot of data (about 29MBs compacted --
> that's data from 1989 to 2008, so it shows that it doesn't really
> grow that much).
>
> The indexes have to be added back in for the tables to be usable, so
> I don't really see how importing with no indexes then adding the
> indexes is going to be preferable to just having the indexes in
> place and running the import. Surely the overhead for the insert is
> going to be pretty much equal to the amount of time it takes to
> write the indexes upon creation (or close enough not to matter
> much).
>
> > There are a couple of free ODBC SQL databases that will run on a
> > USB key without a install, for what it's worth.
>
> This is interesting, but I'm not seeing how this addresses the
> problem I'm trying to solve.
>
> > =A0SQLLite is one of them
> > (Firefox uses it as quite a few other programs), and I think MySQL
> > will run from a USB Drive without an install as well. =A0I remember
> > seeing a web development portable (thumb drive) app that included
> > a portable SQL Database, but I can't remember the name of it. =A0Try
> > portableapps.com or protablefreeware.com =A0It was probably on one
> > of those. =A0Since they are ODBC compliant, Access should be able to
> > link to them. =A0(I know I can do it with SQL Lite, though you have
> > to download the ODBC Drivers.)
>
> I can't see how running it from a USB drive makes it any easier for
> me to copy the data from the SQL Server to the users PC for
> statistical analysis.
>
> > The above said, I can't say as I'd recommend running the database
> > on a Thumb drive. =A0An external USB drive (with actual spinning
> > platters) is *much* faster. =A0USB Keys are great for transport, but
> > other then that, they suck for database work. =A0You can get
> > external USB drives that fit into your pocket as well. =A0They're
> > bigger then a USB Key, but the speed is worth it. =A0Pop TrueCrypt
> > (in traveler mode) on them and you've got a nice secure method of
> > moving data around *and* using it directly off the drive at a nice
> > fast speed.
>
> All very interesting, but I must be missing something: how do you
> think all that addresses my question? Or were you just free
> associating with some highly useful tangential information?

Sorry for the long delay in replying, I've been mostly unavailable the
for the past week.

The thought I had was based on a assumption that you had a Access
front-end attached to a SQL Server back-end.  Since you didn't want
the users to use SQL Server Express, how could you allow them access
to the data in a highly mobile methodology.  Thus the USB variations
to SQL Server that didn't require an install, yet could still be
worked through an Access front-end with just a re-pointing of the
front-end to the new back-end.  Since both SQL back-ends (SQL Server
and the substitutions I mentioned) are SQL compliant databases, you
should be able to transport the data back and forth without too much
trouble, and would allow for the indexing of the data.

Did that not meet the criteria of your post?  If this is not an
acceptable solution, I'd like to know why for my own future reference,
if not for the group's!

Chuck Grimsby <cgatgoo@gmail.com> wrote in
news:83c5f2ff-9a45-406f-b12e-4f7441d78fc7@k10g2000yqa.googlegroups.co
m: 

> The thought I had was based on a assumption that you had a Access
> front-end attached to a SQL Server back-end.  Since you didn't
> want the users to use SQL Server Express, how could you allow them
> access to the data in a highly mobile methodology.

Either you did not read the cited statement of my situations
carefully, or you didn't read it at all: 

http://tinyurl.com/26yhxuu =>
http://stackoverflow.com/questions/3612149/copy-sql-server-express-da
tabase-to-another-computer 

>  Thus the USB variations
> to SQL Server that didn't require an install, yet could still be
> worked through an Access front-end with just a re-pointing of the
> front-end to the new back-end.  Since both SQL back-ends (SQL
> Server and the substitutions I mentioned) are SQL compliant
> databases, you should be able to transport the data back and forth
> without too much trouble, and would allow for the indexing of the
> data. 
> 
> Did that not meet the criteria of your post?

Not even close.

>  If this is not an
> acceptable solution, I'd like to know why for my own future
> reference, if not for the group's!

It helps if you read the question you're responding to, which
includes the cited URL that provides all the background to the
question. 

Gad, that's harsh, but I don't know what to say. Your followups are
really interesting, but really have nothing at all to do with the
question I asked. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
contact via website only     http://www.dfenton.com/DFA/