Month: February 2011

How to Copy All Tables Definition with PowerShell

PROBLEM: You need to copy the table definition for all tables in a database between two SQL servers.

SOLUTION: One of the requirements is to use PowerShell and I will use it in combination with SQLPSX. Yes, I will use SQLPSX again and you probably have noticed I use it in all my PowerShell scripts that access MS SQL Server –I’m a fan, what else can I say.

There are a few highlighted lines of code I want to mention because I had a bit of trouble while scripting tables with dependencies on user defined data types and partitioned tables. Line 5 and 6 are string variables assigments for the target server and database. On line 13, I added the scripting option “NoFileGroup” because the source database was set up using multiple filegroups and the script failed when these file groups were not available on the target server; the target server in this example will have a single file group. On line 15, I added the “WithDependencies” which helped with the creation of partitioned table functions and schemas. And on line 16, I used the “IncludeIfNotExists” in order to avoid errors if the database objects already exists.

CLEAR 

$sSRCServer = "TUIRADEVELOPMENT" 
$sSRCDatabase = "AdventureWorks" 
$sTRGServer = "TUIRA" 
$sTRGDatabase = "AdventureWorks" 

$oSO = New-SqlScriptingOptions 
$oSO.Permissions = $false 
$oSO.ClusteredIndexes = $true 
$oSO.Indexes = $true 
$oSO.Triggers = $true 
$oSO.NoFileGroup = $true
$oSO.NonClusteredIndexes = $true 
$oSO.WithDependencies = $true
$oSO.IncludeIfNotExists = $true

$oTables = Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlTable 

foreach ($oTable in $oTables){  
    $oT = $oTable.Name  
    $oS = $oTable.Schema  
    $DDL = Get-SqlScripter $oTable -scriptingOptions $oSO 
    invoke-sqlcmd -ServerInstance "$sTRGServer" -Database "$sTRGDatabase" `
          -Query "$DDL" 
}

This solution can easily be converted to a function with optional parameters that map to scripting options. The target variables for server and/or database could hold arrays for copying the tables definition to multiple servers. A form could also be developed using WPF to make it user friendly. What do you think?

Related Links

En Español

Cómo Copiar el Esquema de Tablas Utilizando PowerShell

PROBLEMA: Necesitas copiar el esquema de tablas de un servidor Microsoft SQL a otro servidor SQL.

SOLUCION: Para resolver este problema utilizo PowerShell y el SQLPSX. Si sigues este blog, podrás notar que utilizo la extensión SQLPSX de SQL para PowerShell en todos mis PowerShell scripts de base de datos.

Hay algunas líneas en el código adjunto las cuales hago resaltar porque al escribir el script tuve ciertas dificultades ya que existen dependencias entre las tablas y tipos definidos por el usuario además de la existencia de tablas con particiones. La línea 5 y 6 son para asignar el nombre del servidor y base de datos de destino a variables. En la línea 13 agrego la opción de scripting “NoFileGroup” ya que la base de datos de origen está configurada con múltiples grupos de archivos (filegroups) y mi script termino en error cuando estos filegroups no se encontraron en la base de datos de destino. En este ejemplo, la base de datos de destino cuenta con un solo filegroup. En la línea 15 añado la opción de scripting “WithDependencies” para ayudar con la creación de funciones y esquemas de particiones de tablas. Finalmente en la línea 16, incluyo la opción de scripting “IncludeIfNotExists” para verificar que los objetos de base de datos a crear no existan en la base de datos de destino y así evitar los errores de creación.

CLEAR

$sSRCServer = "TUIRADEVELOPMENT"
$sSRCDatabase = "AdventureWorks"
$sTRGServer = "TUIRA"
$sTRGDatabase = "AdventureWorks"

$oSO = New-SqlScriptingOptions
$oSO.Permissions = $false
$oSO.ClusteredIndexes = $true
$oSO.Indexes = $true
$oSO.Triggers = $true
$oSO.NoFileGroup = $true
$oSO.NonClusteredIndexes = $true
$oSO.WithDependencies = $true
$oSO.IncludeIfNotExists = $true

$oTables = Get-SqlDatabase $sSRCServer $sSRCDatabase|Get-SqlTable

foreach ($oTable in $oTables){
    $oT = $oTable.Name
    $oS = $oTable.Schema
    $DDL = Get-SqlScripter $oTable -scriptingOptions $oSO
    invoke-sqlcmd -ServerInstance "$sTRGServer" -Database "$sTRGDatabase" `
         -Query "$DDL"
}

Esta solución puede ser convertida a una función de PowerShell y utilizar parámetros para vincular las opciones de scripting. Las variables de servidor y base de datos de destino pueden contener una matriz (array) con nombres de servidores y base de datos para así copiar el esquema de tablas a múltiples servidores. Un formulario web puede ser creado utilizando WPF para facilitar el uso de esta solución. ¿Qué te parece esta solución?

Artículos Relacionados

In English

PowerShell – The Automator (T-SQL Tuesday #15)

I read about T-SQL Tuesday #15 this evening while riding the train on my way home; I need to get in the scoop of these events when they first come out.

How to Review SQL Logs with PoweShell describes one of the many scripts I created in PoweShell to automate my daily tasks. I have also created the CodePlex project iSQLPS which is a collection of scripts for a SQL Server inventory system -it is a work in progress. PowerShell is by far, the greatest tool created for administrators of Microsoft systems and I’m surprise on how many administrators still resist the force or are unwilling to see the light!

Cómo Cambiar la Extensión de Todos los Archivos en un Directorio

PROBLEMA: Tienes un directorio con muchos archivos y deseas cambiarles la extensión sin tener que hacerlo uno a uno.

SOLUCION: Puedes lograr tu objetivo utilizando PowerShell y hacerlo con 3 líneas de código.

CLEAR
CD [PATH TO FILE LOCATION]
Get-ChildItem *.[FILE EXTENSION]|Rename-Item –NewName {$_.Name –replace "[FROM VALUE]","[TO VALUE]"}

Siempre me gusta empezar mis scripts con la consola de PowerShell limpia y por lo tanto empiezo con el comando CLEAR. Continúo con el comando CD para cambiar al directorio que contiene los archivos con los que voy a trabajar. De allí, ejecuto mi línea de comdlets para hacer el cambio de extensión de todos los archivos.

Aquí está un ejemplo de cómo cambiar la extensión de todos los archivos con extensión .TAB a extensión .SQL

CLEAR
CD C:DatabaseScripts
Get-ChildItem *.TAB|Rename-Item -NewName { `
    $_.Name -replace "TAB","SQL"
}

En Inglés