3

Is there a way to backup and restore a database without the data. I just want the tables, scheme, stored procedures, etc. without the data.

  1. How to backup the database?
  2. How to restore it in SSMS?
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
Peter Sun
  • 1,675
  • 4
  • 27
  • 50

1 Answers1

3

If I'm not mistaken you can do this:

  1. Right click the database
  2. Select Script Database As
  3. Select Create to
  4. Select file

This gives you a script that you can run on a different server to set up the db.

Update:

You probably need to follow the steps here

Community
  • 1
  • 1
Dennis Jaheruddin
  • 21,208
  • 8
  • 66
  • 122
  • 1
    Technically not a backup of the database, however, depending on the OP's actual needs (unstated), this may suffice. – RBarryYoung Feb 28 '14 at 16:04
  • so basically I am creating a script that will create a database like the one i am trying to create (with the tables\views\sp)? – Peter Sun Feb 28 '14 at 16:08
  • @user2252502 Exactly, you can in fact try it first on a single table with a few columns. That should give you an idea of how the script works. Have not tried it on full databases, but expect everything to be included. Especially since it is possible to make these scripts for individual tables, procedures, views... – Dennis Jaheruddin Feb 28 '14 at 16:10
  • So i am getting errors when I try to run the script: `User does not have permission to alter database 'chinesebak', the database does not exist, or the database is not in a state that allows access checks. Msg 5069, Level 16, State 1, Line 2 ALTER DATABASE statement failed. Msg 911, Level 16, State 4, Line 2 Database 'chinesebak' does not exist. Make sure that the name is entered correctly.` – Peter Sun Feb 28 '14 at 16:12
  • @user2252502 Make sure to use create to, rather than alter to in step 2. Furthermore make sure you have the correct user rights and that you don't try to create it in a place where it already exists. – Dennis Jaheruddin Feb 28 '14 at 16:34
  • It finally ran, but it just created a database not any of the tables/schema/views/etc. – Peter Sun Feb 28 '14 at 16:51
  • I take that back..it restored the views and stored procedures, but the tables are missing – Peter Sun Feb 28 '14 at 16:54
  • @user2252502 For what you want you probably need to follow the steps [here](http://stackoverflow.com/a/5114738/983722) – Dennis Jaheruddin Mar 12 '14 at 13:24