How To: Script a SQL Database Schema and Data to a single file

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

2 thoughts on “How To: Script a SQL Database Schema and Data to a single file”

Leave a Reply

Your email address will not be published. Required fields are marked *