Script a database definition

Recreating a database in a higher environment is a common task. Quite often it is not that smart just to restore a back, as you then get database users, file sizes, obsolete schemas etc into the higher environment.
Scripting out a database can be done from SQL Server Management Studio, but this is a manual operation. When you are serving a small army of developers, they need a definition of the production database on a regular basis. Doing that by SQL Server Management Studio quickly gets boring.
There are third party tools like Red Gate SQL Compare that can generate a schema snapshot or a direct comparison, but such a tool is expensive for a simple task like this. In many other situations I am really happy with such tools, but that is another story.
This PowerShell script generates the T-SQL script to create a database and its structure. The script can be edited and reviewed before execution, and can serve as documentation in a Change procedure.
[string]$SqlServerName = '(local)'
[string]$DbName = 'AdventureWorks'

[string]$SqlBatchSeperator = 'GO'  # SQL Server Management Studio default: GO

"/*`nT-SQL script to define the structure of the database '$($DbName)' on the server '$($SqlServerName)'."
"Script generated $($(Get-Date).ToUniversalTime().ToString('s')) UTC"
"  by the user $([Security.Principal.WindowsIdentity]::GetCurrent().Name)"
"  on the computer $($env:COMPUTERNAME)"
"  with the script file '$($MyInvocation.InvocationName)'"

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServerName
$Options = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$Options.IncludeHeaders = $true
$Options.Permissions = $true

"`n--*** Logins ***"
$Server.Logins | Where-Object { ($_.Name -ne 'sa') -and ($_.Name -notlike '##MS_Policy*') -and ($_.Name -notlike 'NT *') } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n--*** Database definition ***"
"`n--EXECUTE [$DbName].dbo.sp_changedbowner @loginame = N'sa', @map = true;`n$SqlBatchSeperator"
"`nALTER AUTHORIZATION ON DATABASE::[$DbName] TO [sa];`n$SqlBatchSeperator"
"`nUSE [$DbName];`n$SqlBatchSeperator"
"`n`n--*** Userdefined applicationroles ***"
$Server.Databases[$DbName].ApplicationRoles | Where-Object { $_.IsFixedRole -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined databaseroles ***"
$Server.Databases[$DbName].Roles | Where-Object { $_.IsFixedRole -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined schemas ***"
$Server.Databases[$DbName].Schemas | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }

$Options.IncludeDatabaseRoleMemberships = $true

"`n`n--*** Database users ***"
$Server.Databases[$DbName].Users | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }

$Options.DriAll = $true
$Options.Indexes = $true

"`n`n--*** Userdefined assemblies ***"
$Server.Databases[$DbName].Assemblies | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined certificates ***"
$Server.Databases[$DbName].Certificates | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined defaults ***"
$Server.Databases[$DbName].Defaults | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined fulltext catalogs ***"
$Server.Databases[$DbName].FullTextCatalogs | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined rules ***"
$Server.Databases[$DbName].Rules | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined stored procedures ***"
$Server.Databases[$DbName].StoredProcedures | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined tables with indexes ***"
$Server.Databases[$DbName].Tables | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined Triggers ***"
$Server.Databases[$DbName].Triggers | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined functions ***"
$Server.Databases[$DbName].UserDefinedFunctions | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined datatypes ***"
$Server.Databases[$DbName].UserDefindTypes | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }
"`n`n--*** Userdefined views ***"
$Server.Databases[$DbName].Views | Where-Object { $_.IsSystemObject -eq $false } | ForEach-Object { $_.Script($Options);$SqlBatchSeperator }

The script covers the basics, and I think that it is easy to fit it to a equal task.

The script can be used in a Visual Studio solution to compare with a database in development. Such a comparison can give a deployment script for a higher and isolated environment.


TechNet Library: SMO "Server Class"


PowerShell 3 CTP

The preview is officially released with "Windows Management Framework 3.0 Community Technology Preview". The details are described in a entry on the Windows PowerShell Blog:
Windows Management Framework 3.0 Community Technology Preview (CTP) #1 Available for Download

The autumn looks to be most interesting :-)


Windows Server 8 and SQL Server 2008 R2

Together with the very public release of Windows 8, Microsoft released Windows Server 8.
Earlier this year I attended TechEd North America, and asked the Microsoft people about a "Windows Server Next", but nobody could say anything. Actually most of the answers were like "I don't know anything about a new Windows Server version.". On the other hand there were a lot about SQL Server "Denali" and Visual Studio Next. The three products usually are released in a new major version rather close together.
But now the preview for Windows Server 8 is here. I got mine from my MSDN subscription. It is not general available like the Windows 8 desktop edition.

Installing Windows Server 8 on vmware was quite simple and without challenges. The installation was done on vmware workstation 8 as Windows 8 the other day.
I installed the full version. Windows Core I will take a look at another time. Especially when I have figured out how to install SQL Server "Denali" on Windows Server 8.

After the initial installation when the system started for the first time, I took a quick look at the used storage.
The storage amount used for the Windows installation is ok. This is nice when you consider consolidation by server virtualization.

Installing SQL Server 2008 R2 (Enterprise Edition x64) requires some .NET and Windows Installer.
This is handled by the installation. Just click OK. The screen shows nothing for a minute or two, but some activity can be spotted in the Task Manager.
In this case I installed Database Services with Fulltext Services, Reporting Services, Integration Services,  Business Intelligence Development Studio and all Management Tools.
After the installation, a little more store is used.
More precisely 5.9 GB are used by the SQL Server installation.

The new Start design I am working on getting used to.
In general the SQL Server 2008 behaves normally.

BTW - When I activated Windows Update, there were 13 critical updates for Windows Server Preview.

SQL Server on Windows 8

The next Windows has just been released as a preview, both as desktop and as server.
Downloading from MSDN took some time. It looked like I was not the only one downloading ;-)

When I tried to install the Windows 8 desktop as a virtual computer i vmware, the initial boot failed with a "HAL_INITIALIZATION_FAILED".
A little browsing using my favorite web search engine, I found several  indications that the problem is a new HAL (Hardware Abstraction Layer) in Windows 8. A HAL that vmware 7.n can not handle...
The next version of vmware (8.0) was released 2011-09-13, but the licensing is not open until the week of 2011-09-19.
Still it was no problem to acquire a trial license and install it (vmware workstation) on my desktop running Windows 7 Ultimate x64.

On the new vmware the Windows 8 installation went without any problems. :-)

The vmware tools also installed on the virtual Windows 8 without any issues.
One thing I learned the hard way is that installing the vmware tools locks the GUI on the virtual Windows 8.

I tried to install SQL Server "Denali", but it failed right in the beginning without any clues. No folders are created, and no entries are made in the registry.

The installation of SQL Server 2008 R2 (Developer Edition, x64) went as on Windows 7. Installing Service Pack 1 went as normal.
Also it looks like everything is running fine. :-)

The new Start menu in Windows 8 holds the SQL Server 2008 icons like this:
From a SQL Server perspective, I have not found a major difference between Windows 7 and Windows 8.