Scripting create Database (SQL Server)

We have a script to generate create db but for one of the databases , It seems to add ALTER

DATABASE ADD FILEGROUP for one db but not other..

Final text looks like this.. Could it be a option ? If it is which one is it?

/****** Object:  Database [APIDW]    Script Date: 06/30/2015 10:34:05 ******/
CREATE DATABASE [xx] ON  PRIMARY 
( NAME = xxx', FILENAME = N'Mxx.mdf' , SIZE = 17117184KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
( NAME = N'xxx'...

ALTER DATABASE xx ADD FILEGROUOP A; ---DON'T NEED THIS

ALTER DATABASE xx ADD FILEGROUOP B; ---DON'T NEED THIS
;

CREATE DATABASE [YY] ON  PRIMARY 
( NAME = xxx', FILENAME = N'Mxx.mdf' , SIZE = 17117184KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB ), 
( NAME = N'xxx'...

..
CREATE DATABASE [ZZ]

This script generates create db statements for all online user db

try { ... #Get a server object which corresponds to the default instance $srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $Inst foreach($db in $srv.databases) { If($db.IsAccessible -and !$db.IsSystemObject -and !$db.IsDatabaseSnapshot) { If($db.name -ne "DBADB") { $db.name $db.script() | out-file $OutFile -append } } } $file = New-Object System.IO.StreamReader -Arg $OutFile $file.Close(); } catch { ExitProg $false $error[0] }





  • Edited by SQL_Jay 15 hours 52 minutes ago
June 30th, 2015 11:18am

Only thing I can think of is the option NoFileGroup.

https://msdn.microsoft.com/en-IN/library/microsoft.sqlserver.management.smo.scriptingoptions.nofilegroup.aspx

Free Windows Admin Tool Kit Click here and download it now
June 30th, 2015 12:28pm

Your issue is not a scripting issue.  You should be posting in the MSSQLServer forum.
June 30th, 2015 1:05pm

If I script a database as you have I will get the following:

# script out database with defaults
$srv.Databases.Item('northwind').Script()|Out-File northwind.sql

CREATE DATABASE [northwind]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Northwind', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\NORTHWND.MDF' , SIZE = 5504KB , MAXSIZE = UNLIMITED, FILEGROWTH = 104857600KB )
 LOG ON 
( NAME = N'Northwind_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\NORTHWND_log.ldf' , SIZE = 3456KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )
 COLLATE SQL_Latin1_General_CP1_CI_AS
ALTER DATABASE [northwind] SET COMPATIBILITY_LEVEL = 90
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [northwind].[dbo].[sp_fulltext_database] @action = 'disable'
end
ALTER DATABASE [northwind] SET ANSI_NULL_DEFAULT OFF 
ALTER DATABASE [northwind] SET ANSI_NULLS OFF 
ALTER DATABASE [northwind] SET ANSI_PADDING OFF 
ALTER DATABASE [northwind] SET ANSI_WARNINGS OFF 
ALTER DATABASE [northwind] SET ARITHABORT OFF 
ALTER DATABASE [northwind] SET AUTO_CLOSE ON 
ALTER DATABASE [northwind] SET AUTO_SHRINK OFF 
ALTER DATABASE [northwind] SET AUTO_UPDATE_STATISTICS ON 
ALTER DATABASE [northwind] SET CURSOR_CLOSE_ON_COMMIT OFF 
ALTER DATABASE [northwind] SET CURSOR_DEFAULT  GLOBAL 
ALTER DATABASE [northwind] SET CONCAT_NULL_YIELDS_NULL OFF 
ALTER DATABASE [northwind] SET NUMERIC_ROUNDABORT OFF 
ALTER DATABASE [northwind] SET QUOTED_IDENTIFIER OFF 
ALTER DATABASE [northwind] SET RECURSIVE_TRIGGERS OFF 
ALTER DATABASE [northwind] SET  DISABLE_BROKER 
ALTER DATABASE [northwind] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
ALTER DATABASE [northwind] SET DATE_CORRELATION_OPTIMIZATION OFF 
ALTER DATABASE [northwind] SET TRUSTWORTHY OFF 
ALTER DATABASE [northwind] SET ALLOW_SNAPSHOT_ISOLATION OFF 
ALTER DATABASE [northwind] SET PARAMETERIZATION SIMPLE 
ALTER DATABASE [northwind] SET READ_COMMITTED_SNAPSHOT OFF 
ALTER DATABASE [northwind] SET HONOR_BROKER_PRIORITY OFF 
ALTER DATABASE [northwind] SET RECOVERY SIMPLE 
ALTER DATABASE [northwind] SET  MULTI_USER 
ALTER DATABASE [northwind] SET PAGE_VERIFY TORN_PAGE_DETECTION  
ALTER DATABASE [northwind] SET DB_CHAINING OFF 
ALTER DATABASE [northwind] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
ALTER DATABASE [northwind] SET TARGET_RECOVERY_TIME = 0 SECONDS 
ALTER DATABASE [northwind] SET  READ_WRITE 

Free Windows Admin Tool Kit Click here and download it now
June 30th, 2015 1:16pm

Here is the option set to adjust as you can do in SSMS:

$so=New-Object Microsoft.SqlServer.Management.SMO.ScriptingOptions
$so.NoFileGroup=$true
$srv.Databases.Item('northwind').Script($so)

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptoption.aspx

June 30th, 2015 1:27pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics