Print ''
Print '-------------------------------------------------------------------------'
Print ' Script Creation Date : 22.03.2006'
Print ' Script Autor         : J.Feuerstein'
Print '-------------------------------------------------------------------------'
Print ' Create/Update Database'
Print ' DB Name :[#DB_N#]'
Print ' DB file :#DB_mdf_DPNE#'
Print ' Log file:#DB_ldf_DPNE#'
Print '-------------------------------------------------------------------------'

/*
-- Datenbank Verzeichniss des Servers feststellen
DECLARE @DBS_DIR varchar(128)
SELECT  @DBS_Dir = Substring(phyname, 1, CharIndex(N'master.mdf',Lower(phyname)) - 1)
 FROM master.dbo.sysdevices
 WHERE  (name = N'master')
Print ' Datbase Origin Root:' + @DBS_Dir
*/

-- Datenbank Namen Verzeichniss und Files festlegen
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'#DB_N#')
   BEGIN
     Print ''
     Print 'Info:'
     Print '  DB: #DB_N#'
     Print '   |-> exist allready. No action has to be done'
   END
   ELSE
   BEGIN
     -- 09.05.2007 J.fes geändert SIZE = 50MB, FILEGROWTH = 50MB auf: SIZE = 200MB, FILEGROWTH = 200MB
     -- 08.03.2008 J.Fes #DB_ldf_NE# und #DB_ldf2_NE# ergänzt
     -- 02.11.2009 J.Fes COLLATE Latin1_General_CI_AS ergänzt
     -- 02.11.2009 J.Fes #DB_ldf1_DPNE# #DB_ldf2_DPNE#  ersetzt durch nur noch einen logfile #DB_ldf_DPNE#
     CREATE DATABASE [#DB_N#] ON
      (NAME = N'#DB_mdf_NE#',
              FILENAME = N'#DB_mdf_DPNE#' ,
              SIZE = 200MB, 
              FILEGROWTH = 200MB,
              MAXSIZE = UNLIMITED)
      LOG ON
      (NAME = N'#DB_ldf_NE#',
              FILENAME = N'#DB_ldf_DPNE#',
              SIZE = 20MB, 
              FILEGROWTH = 100MB,
              MAXSIZE = UNLIMITED)
      -- COLLATE Latin1_General_CI_AS
              
     -- Check if DB Exist's 
     IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'#DB_N#')
        BEGIN
          exec sp_dboption N'#DB_N#', N'autoclose', N'true'
          exec sp_dboption N'#DB_N#', N'bulkcopy', N'true'
          exec sp_dboption N'#DB_N#', N'trunc. log', N'true'
          exec sp_dboption N'#DB_N#', N'torn page detection', N'true'
          exec sp_dboption N'#DB_N#', N'read only', N'false'
          exec sp_dboption N'#DB_N#', N'dbo use', N'false'
          exec sp_dboption N'#DB_N#', N'single', N'false'
          exec sp_dboption N'#DB_N#', N'autoshrink', N'true'
          exec sp_dboption N'#DB_N#', N'ANSI null default', N'false'
          exec sp_dboption N'#DB_N#', N'recursive triggers', N'false'
          exec sp_dboption N'#DB_N#', N'ANSI nulls', N'false'
          exec sp_dboption N'#DB_N#', N'concat null yields null', N'false'
          exec sp_dboption N'#DB_N#', N'cursor close on commit', N'false'
          exec sp_dboption N'#DB_N#', N'default to local cursor', N'false'
          exec sp_dboption N'#DB_N#', N'quoted identifier', N'false'
          exec sp_dboption N'#DB_N#', N'ANSI warnings', N'false'
          exec sp_dboption N'#DB_N#', N'auto create statistics', N'true'
          exec sp_dboption N'#DB_N#', N'auto update statistics', N'true'
          
          if( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) )
          	exec sp_dboption N'#DB_N#', N'db chaining', N'false'
          	
          ALTER DATABASE [#DB_N#]
               SET RECOVERY FULL
          --   oder
          --    SET RECOVERY BULK_LOGGED  
          --   oder
          --    SET RECOVERY SIMPLE
          --   GO    
          ALTER DATABASE [#DB_N#]
               SET AUTO_SHRINK ON

          ALTER DATABASE [#DB_N#]
                COLLATE Latin1_General_CI_AS
          Print '------------------------------------------------------------------'
          Print 'OK:Db:#DB_N#'
          Print '   succesfull created'
        END 
        else
        BEGIN
         Print '------------------------------------------------------------------'
         Print 'ERR:Db:#DB_N#'
         Print '    !!NOT!! succesfull created'
        END 
   END


GO
