CODEWITHSUNDEEP
sqlsql-serveroracle-databasesql-server-2005backup
Tom Grochowicz
Tom Grochowicz

Reputation: 5485

Backup SQL Schema Only?

I need to create a backup of a SQL Server 2005 Database that's only the structure...no records, just the schema. Is there any way to do this?

EDIT: I'm trying to create a backup file to use with old processes, so a script wouldn't work for my purposes, sorry

Upvotes: 23

Views: 25272

Answers (6)

Erik Anderson
Erik Anderson

Reputation: 5299

While I prefer using DBCC CLONEDATABASE and generating a database script from the output, Data-tier applications (DAC) deserve an honorable mention as well.

Upvotes: 0

Daniel Brink
Daniel Brink

Reputation: 2504

As of SQL Server 2012 (patched), you can make a schema only clone of your database using DBCC CLONEDATABASE. Then simply backup the clone.

dbcc clonedatabase(Demo, Demo_Clone) with verify_clonedb;
alter database [Demo_Clone] set read_write;
backup database [Demo_Clone] to disk = N'C:\temp\Demo_SchemaOnly_20220821.bak';
drop database [Demo_Clone];

Read more here: Schema Only Database Backup

Upvotes: 1

mauriciopastrana
mauriciopastrana

Reputation: 5080

I make heavy use of this tool:
SQLBalance for MySQL

Unfortunately; its windows only... but works like a charm to move databases around, data or no data, merge or compare.

Upvotes: 0

Mark Harrison
Mark Harrison

Reputation: 304614

Toad for SQL Server does this nicely, if you're considering a commercial product.

Upvotes: 1

Greg Hurlman
Greg Hurlman

Reputation: 17804

Use a 3 step process:

  1. Generate a script from the working database
  2. Create a new database from that script
  3. Create a backup of the new database

Upvotes: 12

Brandon Wood
Brandon Wood

Reputation: 5337

Why not just use SQL Management Studio to create a complete script of your database and the objects?

Upvotes: 1

Related Questions