Re-creating an SQL server database to another MSDE server

[NWx]

Hi,

I write an ASP,NET application who uses SQL Server (MSDE) as Back-end
I'm an experienced developer, but haven't used SQL server before and I need
some help.

Since I don't have full SQL server (but only MSDE), I don't have Enterprise
manager.
So I used Access XP (using an ADP file) to create the database on MSDE
server (tables, views, stored procs etc)

The ASP.NET app works very well with this BE.

Now I'm in the process to upload application on another server for broader
testing.
This server have also IIS / ASP.NET and MSDE installed

I know how to install ASP.NET application, but have no idea how to recreate
the original SQL server database on my test MSDE instance.
Of course I can create the SQL database the same way as I did initially (by
hand, with AccessXP and an ADP file), but I don't want to do this, since I
could misspell a field name, use some slightly different data type, forget a
relation, etc.
What I want is to install EXACTLY the same database on the new server.
So, is there any way with MSDE to generate a script file, which, when run on
another sql server instance, will recreate a database exactly as original
one? Are there any stored procs on SQL server who does this? Any sql server
tools or free 3'rd party tools?

Any help and suggestion will be appreciated.

Regards

 

[Keith Kratochvil]

You should be able to use BACKUP and RESTORE or sp_detach_db and sp_attach_db. You can execute these commands via the command line tool osql.exe. You should be able to execute the appropriate commands via ASP if you wanted.

You will find the specific syntax for the commands listed above within Books Online (SQL Server's documentation). If you don't have access to Books Online you can download it from this location: http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 

[NWx]

Thanks for the info.

Regards


You should be able to use BACKUP and RESTORE or sp_detach_db and
sp_attach_db. You can execute these commands via the command line tool
osql.exe. You should be able to execute the appropriate commands via ASP if
you wanted.

You will find the specific syntax for the commands listed above within Books
Online (SQL Server's documentation). If you don't have access to Books
Online you can download it from this location:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 

[Jodie Rapson]

Hi,

You could either write an sql script, or copy the .mdf to the other server.
( dunno if MSDE has these, as I have SQL 2000).

Jodie Rapson