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
*/


declare @MSSQLVersionInt integer
-- declare @ExecuteMsgRf    varchar(max)
set @MSSQLVersionInt=@@microsoftversion / power(2, 24)

declare @productversion nvarchar(128)
declare @productlevel nvarchar(128)
declare @edition nvarchar(128)

SELECT
    @productversion = CONVERT(VARCHAR(15),SERVERPROPERTY('productversion')),
    @productlevel   = CONVERT(VARCHAR(15),SERVERPROPERTY('productlevel')),
    @edition        = CONVERT(VARCHAR(15),SERVERPROPERTY('edition'))

Print 'Info:'
Print ' DB...............: #DB_N#'
-- Print ' Productlevel.....: ' + @productlevel
-- Print ' Productversion...: ' + @productversion

Print ' Servername.......: ' + CONVERT(VARCHAR(max),@@servername)
Print ' Version..........: ' + CONVERT(VARCHAR(55),@@version)
Print ' Edition..........: ' + @edition
Print ' MSSQLVersionInt..: ' + CONVERT(VARCHAR(15),@MSSQLVersionInt)

-- Datenbank Namen Verzeichniss und Files festlegen
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'#DB_N#')
   BEGIN
     Print ' DB exist allready: No action has to be done'
   END
   ELSE
   BEGIN
     Print ' DB do not exist..: Database will be new created'
     -- 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

        IF @MSSQLVersionInt <= 10
         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'
         END
         BEGIN
          --  exec sp_dboption N'#DB_N#', N'autoclose', N'true'
          ALTER DATABASE #DB_N# SET AUTO_CLOSE ON
          --  exec sp_dboption N'#DB_N#', N'bulkcopy', N'true'
          --  exec sp_dboption N'#DB_N#', N'trunc. log', N'true'
          ALTER DATABASE #DB_N# SET RECOVERY SIMPLE
          --  exec sp_dboption N'#DB_N#', N'torn page detection', N'true'
          ALTER DATABASE #DB_N# SET TORN_PAGE_DETECTION ON

          -- exec sp_dboption N'#DB_N#', N'ANSI null default', N'false'
          ALTER DATABASE #DB_N# SET ANSI_NULL_DEFAULT OFF

          -- exec sp_dboption N'#DB_N#', N'recursive triggers', N'false'
          ALTER DATABASE #DB_N# SET RECURSIVE_TRIGGERS OFF

          -- exec sp_dboption N'#DB_N#', N'ANSI nulls', N'false'
          ALTER DATABASE #DB_N# SET ANSI_NULLS OFF

          -- exec sp_dboption N'#DB_N#', N'concat null yields null', N'false'
          ALTER DATABASE #DB_N# SET CONCAT_NULL_YIELDS_NULL OFF

          -- exec sp_dboption N'#DB_N#', N'cursor close on commit', N'false'
          ALTER DATABASE #DB_N# SET CURSOR_CLOSE_ON_COMMIT OFF

          -- exec sp_dboption N'#DB_N#', N'default to local cursor', N'false'
          ALTER DATABASE #DB_N# SET CURSOR_DEFAULT GLOBAL

          -- exec sp_dboption N'#DB_N#', N'quoted identifier', N'false'
          ALTER DATABASE #DB_N# SET QUOTED_IDENTIFIER OFF

          -- exec sp_dboption N'#DB_N#', N'ANSI warnings', N'false'
          ALTER DATABASE #DB_N# SET ANSI_WARNINGS OFF

          -- exec sp_dboption N'#DB_N#', N'auto create statistics', N'true'
          ALTER DATABASE #DB_N# SET AUTO_CREATE_STATISTICS ON

          -- exec sp_dboption N'#DB_N#', N'auto update statistics', N'true'
          -- #todo: 30.08.2019 siehe https://www.mssqltips.com/sqlservertip/1606/execute-update-statistics-for-all-sql-server-databases/
          ALTER DATABASE #DB_N# SET AUTO_UPDATE_STATISTICS ON

         END

          -- 17.12.2012 J.Fes RECOVERY SIMPLE voreingestellt
          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
