Copying SQL Server website to CD

Hi!

Not sure if this is the right place to post. I need to copy a website that I created on a CD. It is using SQL Server as the database and VB, ASP as front end. However, the problem is that I need to copy it in such a way so that it can be viewed from the CD itseld without needing MSDE or SQL Server. Need help.

Thanks.

This is a fine place to post.
The problem here is that it is difficult to make a SQL Server databaseread-only, which it needs to be on a CD. I realize that you probablywant to use the same db as you use on a Web site, but it's a hassle.
That said, there are a few options. Check out the sp_create_removablesystem stored procedure, which lets you create a removable mediadatabase. It's a bit tricky to get to work right, but ask questionshere if you want to use that option. I think I have some sample codelaying around.
But wait...you don't want SQL Server or MSDE installed at all? Ifthat's what you mean, you're out of luck. You're going to have to useanother way to store and use the data on the CD. An XML file mightwork. You could maybe use another database engine, such as Access, butit's going to have to be installed on the machine. (Anyone know if theAccess runtime is still available? I'm not an Access guy.)
Please clarify what you want to do and we'll try to help.
Don
|||

Thanks for replying.

ACtually I just realized that if there is a way to get the MSDE on the CD itself and make it work that is fine. SO if that is the case then we would not need Access on the user machine and the user would not have to install the MSDE. You think this would work? How ?

Thanks again.

|||I'm confused. What do you mean that you can get MSDE on the CD? MSDE has to beinstalled on the computer in order to access the database. You can't just put a program on a CD and not have to install anything.
So no, it won't work. (Unless there is something I don't know about MSDE, which is quite possible!)
Don

Copying databases

Hello,

I'm attempting to launch a new ASP .NET site and I'm having problems moving my database, caused I'm sure by my own ignorance.

I built the database in SQL Server 2005 Express but found that I couldn't use it to copy the database to the deployment server, so I uninstalled it and now I'm using a 6 month trial version of SQL Server 2005 Enterprise Edition.

The server to which I want to copy my database is SQL Server 2003 (version 8.0.760).

My first attempt succeeded in uploading the tables and their data, I then used the "Generate Scripts" command to produce a large query detailing how to create my stored procedures, I ran this script on the destination server and it eventually worked ok.

However, for some reason my Primary Keys, Foreign Keys, Constraints and Identities were not copied to the database on the deployment server at all. The columns all arrived, with the correct datatypes, but the other objects weren't defined at all.

Obviously, these are crucial, so what went wrong and how do I fix it please?

Many thanks

Ben

Are you using "Generate Scripts" or the "Import/Export Data"?

If you're using Generate Scripts, make sure all the options under Table/View Options are true.

If you're using Import/Export Data, It doesn't work so well. Use the Generate Scripts option to actually create the tables/Stored Procedures/Views, etc., then copy the data using the Import/Export option.

Another option is to backup the database, then restore it to the other server.

|||

Thank you for responding.

I initially used "import/export data" but when I found out that it hadn't done my stored procedures I used the "generate scripts" to get the stored procedures as well.

I've tried using generate scripts the way you suggest, and I think it would work IF I didn't already have a load of tables and procedures in the target database. Is there a quick way to drop everything in the database, without deleting the database itself so that I can have a second go at copying my objects?

I daren't delete the database and recreate it because it's on a remote host that I can't get to and only have permissions to alter my own database.

|||

BenCh1: "Is there a quick way to drop everything in the database, without deleting the database itself so that I can have a second go at copying my objects?"

I believe this is enabled with SP2 - November CTP. You can script a "DROP" statement before each create statement. You can obtain a CTP of Service Pack 2 here: http://www.microsoft.com/sql/ctp.mspx.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Copying data from PRD to TST

I've got two DBs in the same SQL instance. They are named TST and PRD. I am using 2.0 so there are many ASP generated tables also.

Every once in a while I want to refresh data from PRD to TST. But I don't want to copy the data from ASP tables.

What is the easiest way to do so?

I moved your post from the FAQ fourm to here. Please note that the FAQ forum is for posting answers to commonly asked questions, not asking questions.

Ryan

|||I've always used SQL Server's built-in Data Transformation Services (DTS) to transfer data from one database to another. Be sure not to check the option to Create Destination Objects; choose only to Copy Data.|||Can you suggest a good resource for DTS? If I Google, there are so many hits that it is kinda hard to find a good one.|||Which version of SQL Server are you running? (Please be sure to put this information into any of your posts related to SQL Server.)|||Sorry about that. SQL Server 2005.|||Well, I haven't used SQL Server 2005 much; I currently only have SQL Express on my machine, and no Management Studio. However, I found this article which might help you get started:SQL Server 2005 Integration Services - Import and Export Wizard.

Copy table from one SQL Server to Another

Using ASP.NET code (VB) I need to copy tables from one SQ: Server to another
SQL Server. This is a poor mans safety backup of some critical data. The
source SQL Server is hosted by an ISP where there is no way to access the DB
via EM or from anywhere other than web pages on that ISP web account so DTS
and such are not options.
WayneIf you're going "poor man's" route ...
Setup the source server as a linked server via your local SQL Server EM
using the credentials you use to login. Then while in your local DB, run
something like
select *
into [My Backup]
from [sqlServer.ispName.com].databasename.dbo.table
-- Alex Papadimoulis
"Wayne Wengert" wrote:

> Using ASP.NET code (VB) I need to copy tables from one SQ: Server to anoth
er
> SQL Server. This is a poor mans safety backup of some critical data. The
> source SQL Server is hosted by an ISP where there is no way to access the
DB
> via EM or from anywhere other than web pages on that ISP web account so DT
S
> and such are not options.
> Wayne
>
>|||The source server can only be "seen" from web pages on the ISP account!
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:7C69D8B7-1870-484D-8658-A21E230F2C0C@.microsoft.com...
> If you're going "poor man's" route ...
> Setup the source server as a linked server via your local SQL Server EM
> using the credentials you use to login. Then while in your local DB, run
> something like
> select *
> into [My Backup]
> from [sqlServer.ispName.com].databasename.dbo.table
> -- Alex Papadimoulis
> "Wayne Wengert" wrote:
>
another
the DB
DTS|||Wayne Wengert wrote:
> Using ASP.NET code (VB) I need to copy tables from one SQ: Server to
> another SQL Server. This is a poor mans safety backup of some
> critical data. The source SQL Server is hosted by an ISP where there
> is no way to access the DB via EM or from anywhere other than web
> pages on that ISP web account so DTS and such are not options.
> Wayne
I'm not sure how you can do that. You would probably need access to the
"other" SQL Server from the ISP's SQL Server by adding a linked server,
but I doubt you have those rights based on what you wrote. You could
create an ASP.Net page to display the contents of the table in a
datagrid (control name?), but from there all you have is the data in an
HTML page. I suppose you could package the table as XML and return the
raw XML in the HTML results.
Are you saying that the ISP won't provide you a backup of the database?
It's strange that you have ADO.Net access to the SQL Server from
ASP.Net, but can't access the server using something like the web data
administrator or even Query Analyzer. I guess the SQL Server has no
direct internet access and is behind the web server (which is a secure
way of setting things up). It's just more difficult from a maintenance
standpoint.
Something tells me there is more harmonious solution here... that I'm
probably just not thinking of. I would request from the ISP they give
you a backup of the database.
David Gugick
Imceda Software
www.imceda.com|||Since this is "critical data" ... get a new ISP ;-)
What about creating a page that outputs CSV or XML and then writing a DTS
package (on local) to grab the data from the URL?
-- Alex Papadimoulis
"Wayne Wengert" wrote:

> The source server can only be "seen" from web pages on the ISP account!
> Wayne
> "Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
> news:7C69D8B7-1870-484D-8658-A21E230F2C0C@.microsoft.com...
> another
> the DB
> DTS
>
>|||Alex;
Thanks for the response. I don't get to choose the ISP! I know that if I
have the table structure on the backup server I can write code to read each
row form the source server and "Insert Into" the backup copy. My problem is
that if they add or remove a field from a table on the source server I need
to reflect that change?
I guess that what I am looking for is a way to create the "CREATE TABLE..."
syntax in code?
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:9AECDF77-5D17-44AD-864E-A883FE054F4A@.microsoft.com...
> Since this is "critical data" ... get a new ISP ;-)
> What about creating a page that outputs CSV or XML and then writing a DTS
> package (on local) to grab the data from the URL?
>
> -- Alex Papadimoulis
> "Wayne Wengert" wrote:
>
EM
run
The
access
so|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:esSLUXFJFHA.2884@.tk2msftngp13.phx.gbl...
> Wayne Wengert wrote:
> I'm not sure how you can do that. You would probably need access to the
> "other" SQL Server from the ISP's SQL Server by adding a linked server,
> but I doubt you have those rights based on what you wrote. You could
> create an ASP.Net page to display the contents of the table in a
> datagrid (control name?), but from there all you have is the data in an
> HTML page. I suppose you could package the table as XML and return the
> raw XML in the HTML results.
> Are you saying that the ISP won't provide you a backup of the database?
The ISP does nightly backups but their restore services don't give me a warm
fuzzy (-;
I also want to get copies of the tables into an environment where I can use
other tools to see what is going on. The only export they offer is a CSV of
the data rows (no way to export the table structure I see.)

> It's strange that you have ADO.Net access to the SQL Server from
> ASP.Net, but can't access the server using something like the web data
> administrator or even Query Analyzer. I guess the SQL Server has no
> direct internet access and is behind the web server (which is a secure
> way of setting things up). It's just more difficult from a maintenance
> standpoint.
Amen to that!!!!!
> Something tells me there is more harmonious solution here... that I'm
> probably just not thinking of. I would request from the ISP they give
> you a backup of the database.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Oy.
Well, looks like you're just going to have to write it yourself. DDL isn't
very hard (I create my tables like that, instead of using EM). And then
you've got the INFORMATION_SCHEMA and sys* tables. Just ... go at it ;-)
Some quick queries ...
select table_name from information_schema.tables
select column_name, data_type, data_size from information_schema.columns
where table_name = ?
Look thru the results, build your CREATE TABLE statement.
Good luck!
-- Alex
"Wayne Wengert" wrote:

> Alex;
> Thanks for the response. I don't get to choose the ISP! I know that if I
> have the table structure on the backup server I can write code to read eac
h
> row form the source server and "Insert Into" the backup copy. My problem i
s
> that if they add or remove a field from a table on the source server I nee
d
> to reflect that change?
> I guess that what I am looking for is a way to create the "CREATE TABLE...
"
> syntax in code?
> Wayne
> "Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
> news:9AECDF77-5D17-44AD-864E-A883FE054F4A@.microsoft.com...
> EM
> run
> The
> access
> so
>
>|||Thanks for the suggestion and the sample queries. I'll give it a try.
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:4714AABE-11B2-4ABF-A289-BAA9C6092524@.microsoft.com...
> Oy.
> Well, looks like you're just going to have to write it yourself. DDL isn't
> very hard (I create my tables like that, instead of using EM). And then
> you've got the INFORMATION_SCHEMA and sys* tables. Just ... go at it ;-)
> Some quick queries ...
> select table_name from information_schema.tables
> select column_name, data_type, data_size from information_schema.columns
> where table_name = ?
> Look thru the results, build your CREATE TABLE statement.
> Good luck!
> -- Alex
>
> "Wayne Wengert" wrote:
>
each
is
need
TABLE..."
DTS
account!
message
Server
DB,
Server to
data.
account|||Bummer... they don't allow access to "master" SP's. On to Plan C
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:4714AABE-11B2-4ABF-A289-BAA9C6092524@.microsoft.com...
> Oy.
> Well, looks like you're just going to have to write it yourself. DDL isn't
> very hard (I create my tables like that, instead of using EM). And then
> you've got the INFORMATION_SCHEMA and sys* tables. Just ... go at it ;-)
> Some quick queries ...
> select table_name from information_schema.tables
> select column_name, data_type, data_size from information_schema.columns
> where table_name = ?
> Look thru the results, build your CREATE TABLE statement.
> Good luck!
> -- Alex
>
> "Wayne Wengert" wrote:
>
each
is
need
TABLE..."
DTS
account!
message
Server
DB,
Server to
data.
account