Posted by Limunski Magarac on 07/13/07 15:08

Hi all :)

My apologies if I posted in the wrong groups, but I just jumped
in MS SQL waters, so any guidance will be appreciated.

What I'm trying to do is the following process:

[1] present operator with a web page (script)
[2] once filled with db name and username, script will create
..sql file
[3] osql.exe will be called with, i presume, -i file.sql and
create a database

I have limited SQL knowledge, but I got the information from
Enterprise Manager when I ran 'All Tasks -> Generate SQL
Script' on how the .sql file should look like.

I realized what are the commands that would create a fresh DB
(I ran this for newly created DB), and figured my .php script
should create such a file.

It's fairly basic, and I'm almost sure all of you know how
outputed .sql file looks like, but anyway here it is:

Script is called with parameters 'six' as database name and
'magarac' as user name:

---------------------------------------------------------------
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name
= N'six')
DROP DATABASE [six]
GO

CREATE DATABASE [six] ON (NAME = N'six_Data', FILENAME = N'E:
\Databasepath\six_Data.MDF' , SIZE = 1, MAXSIZE = 20,
FILEGROWTH = 10%) LOG ON (NAME = N'six_Log', FILENAME = N'E:
\Databasepath\six_Log.LDF' , SIZE = 1, MAXSIZE = 20, FILEGROWTH
= 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'six', N'autoclose', N'false'
GO

....

use [six]
GO

if not exists (select * from dbo.sysusers where name =
N'guest' and hasdbaccess = 1)
EXEC sp_grantdbaccess N'guest'
GO

if not exists (select * from dbo.sysusers where name =
N'sinisam')
EXEC sp_grantdbaccess N'magarac', N'magarac'
GO

exec sp_addrolemember N'db_owner', N'magarac'
GO
---------------------------------------------------------------

I managed to get an exact replica of .sql file that Enterprise
Manager created. It leads me to believe that this way of
automated database creation is indeed possible.

Really sorry for making you to go through all this text, but
after I get a green light on this from you guys, I'll have a
bit more problematic question.

Is there any reason why this should not be used, or would fail?

Thanks in advance :)


P.S.
Just as a heads-up, next part of my problem is automated
creation of new MS SQL server login to use with new DB.

[Back to original message]