At the moment I’m working on a web application, using ASP.net MVC connected to SQL Server 2005. As part of this work, I wanted to script out my current database schema into a flat file. I had created my SQL Database using SQL Management Studio on my laptop, but I wanted to reliability deploy this onto a machine in the office.
Luckily, I had Red Gate SQL Compare to hand (Surprising as I work for Red Gate :))! This allows for a number of different approaches, such as connecting to the office via VPN and syncing my two databases or syncing to a scripts folder, but I found SQL Compare could create a single SQL file containing my database schema just using the command line tool.
The command below will sync my local Northwind database schema into a file called Schema.sql. If the file already exists, I override it.
SQLCompare.exe /database1:Northwind /scriptfile:”C:NorthwindSchema.sql” /force
Perfect!! I can now use SQL Cmd or SSMS to execute the file to produce my schema.
The same command works for SQL Data Compare, allowing me to script all the data in the database to a single sql file.
SQLDataCompare.exe /database1:Northwind /scriptfile:”C:NorthwindData.sql” /force
NOTE: These scripts do not include the USE statement, as such they will run under the database your connected on. You just need to be aware of what database your connected to or which database you use in your connection string
Why not to use SQL Publishing Wizard?
by default resides in
C:Program FilesMicrosoft SQL Server90ToolsPublishing1.2SqlPubWiz.exe”
It was already installed with some SP on my PC, but can be additionally downloaded from
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
yep, +1 for Sql Publishing Wizard! not to dis your employer, like…