Schema compare and deploy script using VSDBCMD.exe

Question

Hi ,

I am using VSDBCMD.exe to compare two datbase schem and deploy sql script to target database. I am using following command

vsdbcmd.exe /a:deploy /dd:- /dsp:sql /model:D:\SourceDatabase.dbschema /targetmodelfile:D:\TargetDatabase.dbschema /DeploymentScriptFile:D:\UpdateScript.SQL /p:TargetDatabase="Test2"

This command generates UpdateScript.SQL file to deploy at target database.

when i run this script it does not execute because of following piece of code

IF (@@servername != '')
BEGIN
    RAISERROR(N'The server name in the build script %s does not match the name of the target server %s. Verify whether your database project settings are correct and whether your build script is up to date.', 16, 127,N'',@@servername) WITH NOWAIT
    RETURN
END

GO
PRINT N'Creating [Test1]...';


GO
ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [Test1], FILENAME = '$(DefaultDataPath)$(DatabaseName).mdf', SIZE = 2048 KB, FILEGROWTH = 1024 KB) TO FILEGROUP [PRIMARY];


GO
PRINT N'Creating [Test1_log]...';


GO
ALTER DATABASE [$(DatabaseName)]
    ADD LOG FILE (NAME = [Test1_log], FILENAME = '$(DefaultLogPath)$(DatabaseName)_log.ldf', SIZE = 1024 KB, MAXSIZE = 2097152 MB, FILEGROWTH = 10 %);

Here Test1 is source database and Test2 is target database.

If i delete this piece of code script runs successfully .

If i don't delete this code i get following error

Changed database context to 'master'.
Msg 50000, Level 16, State 127, Server INHL0925\AMITA, Line 4
The server name in the build script  does not match the name of the target serve
r INHL0925\AMITA. Verify whether your database project settings are correct and
whether your build script is up to date.

Please suggest what should i do to avoid this

Answer 1

Hello Amit,

That code validation is intended to make sure you are running the sql script on exactly the same server/database you generated the code from - so, in your example, it will only ran successfully if you execute the script on the Test2 database. Are you trying to run the script on a different database or different server?

It is a common practice to script the differences using a copy of the target database (e.g. on a test server), and then run the same script on the different database (e.g. on a production server), however that is not the best way to do it and it does not work with the VSTS Database projects.

The best practice is that instead of passing the sql script, pass along the database schema (the output of the database project) with the vsdb commands as part of your deployment package - then, the deployment will run 2 steps:

1. compare the database schema and generate the diff script
2. execute the generated script on the target database.

Thank you,
Alin,
PRAKTIK Group
TFS Hosting and TFS Consulting Services.

Answer 2

Hi Alin,

I did same as suggested by you

• compare the database schema and generate the diff script
• execute the generated script on the target database.

I have two different databases on same server . generated sql script consist a path of source database(mdf,ldf files)  when we run the script at target datbase we have to provide path so i want to eliminate that part in generated sql script.

Regards,

Amit

Answer 3

Hello Amit,

In order to do that, look into the database project and delete any files that creates/alters database source files. Also, when you reverse engineer data base objects into the project, go to the Options and unselect 'Database Files' - that will make sure the database files are not scripted into the project.

Thank you,
Alin,
PRAKTIK Group
TFS Hosting and TFS Consulting Services.

Answer 4

Dear Alin,

Please suggest how can i unselect those datbase files because i  am using vsdbcmd.exe to generate sql file.

Regards,

amit

Answer 5

Hello Amit,

By looking at your script, you are using D:\SourceDatabase.dbschema file as a source for your script generation. That dbschema file has to be modified - you will have to open the database project for the SourceDatabase and delete any files in the Files/FileGroups folders:

Thank you,
Alin,
PRAKTIK Group
TFS Hosting and TFS Consulting Services.

Answer 6

Everyone here is too focused on the files.  I'll tell you from Experance that not the problem.  The problem is with the built in script generation that is utilized in vsdbcmd, and all the checks it does to ensure that the db and server that the compare was orignally run against is the same as the script is executing against.  I was having the same type of problems within my organization and ended up creating an exe that effecivly did the same thing as vscmd (thank you reflector) but once the script was generated it would comment out all the SQL code prior to the pre-deployment step.