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 :
Post a Comment