Creating a database using script

[lechuck]

I want to make a script out of a database I have created. I have tried to use the "Generate SQL scripts" tool in the Enterprise Manager.
But when I want to create the database on another server using the query analyser to run the generaded script it fails. Any suggestion of what i might have done wrong, or a very small example of how a script will look like for creating a database whith one table.
/Tobias

 

[DougP]

Maybe you could put the script here so we could see it.
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[cdukes]

What goes wrong with your script. It is often that the script you have generated it not in the correct order. ie you try and install a stored procedure that refers to a table that does not exist yet. etc.

If you could indicate the error messages produced from your script then it would help,

Chris D

 

[lechuck]

My generated script file is over 1200 rows, I made a smaller database and generated a script. In my new example i called the database 'Test' and made a table 'myTable' with a few columns. I generated the script:
if exists (select * from sysobjects where id = object_id(N'[dbo].[myTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[myTable]
GO

if not exists (select * from sysusers where name = N'isAppPerm_Administration' and uid > 16399)
EXEC sp_addrole N'isAppPerm_Administration'
GO

if not exists (select * from sysusers where name = N'isAppPerm_Print' and uid > 16399)
EXEC sp_addrole N'isAppPerm_Print'
GO

CREATE TABLE [dbo].[myTable] (
[myColumn1] [varchar] (50) NULL ,
[myColumn2] [int] IDENTITY (1, 1) NOT NULL ,
[myColymn3] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[myTable] WITH NOCHECK ADD
CONSTRAINT [PK_myTable] PRIMARY KEY NONCLUSTERED
(
[myColumn2]
) ON [PRIMARY]
GO

-----------------
Now I started the query analyser and logged on to another server. I've got confused here... what DB should i choose in the combobox?. I created the database manually with the name Test, and then choosed the 'Test' database from the combobox. After that i runed the script, successfully. (The Table 'MyTable' and my 2 db roles where added correctly)

 

[cdukes]

It sounds like you need a create database command at the top of the script as the database has to exist before you create any tables in it.

syntax:

USE master
GO

CREATE DATABASE Products
ON
( NAME = prods_dat,
FILENAME = 'c:\mssql7\data\prods.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1
)
GO

USE prods_dat
GO

Hope this helps,

Chris D