Automate DBSchema imports

If you got a lot of DB's to manage and servers, and don't have it under source control, try this:
Read this Deploying your Database Project without VSTSDB installed, and then toil with below. Just another shotgun approach.


One step further, for pre-existing environments, capture all the schemas, then DEPLOY to a dev environment and start creating Database Projects(haven't figure out how to automate that yet). A few tweak to the sources, and you got the makings of GUI approach for change management and source control.

Can anyone advise on how to programmatically create a DB Project? I'm struggling to find resources on that topic.

@ECHO off
::Run this to create function calls
:: select
:: 'CALL:GOBANANA "' +name + '" "' + @@Servername + '"'
:: from
:: sysdatabases where name not in('tempdb') --// Yes I want master and model

SET VSDBCMD="C:\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Deploy\vsdbcmd.exe"

CALL:GOBANANA "BOBS_DB" "BOBS_SQLSERVER"

GOTO:EOF

:GOBANANA
SET DBNAME=%~1
SET SVRNAME=%~2
SET SCHEMAFOLDER="C:\temp\DB_Schemas\%SVRNAME:\=_%"
ECHO %SCHEMAFOLDER%

IF NOT EXIST %SCHEMAFOLDER% MKDIR %SCHEMAFOLDER%
:: Strip double quote, safety
SET SCHEMAFOLDER=###%SCHEMAFOLDER%###
SET SCHEMAFOLDER=%SCHEMAFOLDER:"###=%
SET SCHEMAFOLDER=%SCHEMAFOLDER:###"=%
SET SCHEMAFOLDER=%SCHEMAFOLDER:###=%

ECHO SAVING Database Schema for, %DBNAME% to "%SCHEMAFOLDER%\%DBNAME%.dbschema"

%VSDBCMD% /a:Import /cs:"Server=%SVRNAME%;Integrated Security=true;Pooling=false;Initial Catalog=%DBNAME%;" /dsp:Sql /model:"%SCHEMAFOLDER%\%DBNAME%.dbschema"
GOTO:EOF

Comments :

0 comments to “Automate DBSchema imports”