Backup database structure and schema only

Question

Backup database structure and schema only

monkeyDBA

Default port

Points: 1457
More actions
January 28, 2010 at 7:00 pm

#229514

Hi all,
I need to move one database which is 500GB huge to another server. I have confirmation from user that I can move only the database structure and schema, without data. My problem is how to do that?
I tried to script the database, but the script thing do not provide a peace of mind solution. If using MySQL, DBA can script the database structure and populate it to another server using a single SQL file. For Ms SQL Server, the script file I created only help if I already have the similar database exist in my new server. In my scenario, it doesn't help much on that.
I really appreciate if anyone can share his experience and help me on this. Thanks in advance.
Regards,
Jimmy Liew
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 1

Are you sure, the user understands that? Its odd that you don't to move the data.

Also, in SQL Server when you script the database structure, you can use it to create a database with the exact same structure.

Are you sure you don't want the data?

Unless I am missing something.

monkeyDBA Default port Points: 1457 More actions · Answer 2

Hi,

Thanks for your reply. Ya, I am sure with it. My application user can populate the data using their application. According to what they said it would be faster.

I have scripted by database, but I could not find any "CREATE DATABASE" inside the script. If I wish to execute the script on a new server, are you sure the script can help to create datafile, create tlog files, and create the databases?

Thanks and please advice.

Regards,

Jimmy Liew

Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

I have found that the easiest way to do this is to use a comparison tool like Redgate SQL Compare. To use the tool you do the following:

1) Create a new empty database on the new server

2) Perform a schema compare between the existing database and this new database

3) Review the list of objects and exclude any that you don't want (e.g. Users, Schemas, etc...)

4) Synchronize

SQL Compare will generate all of the scripts needed, and once the synchronization is done you have a full schema copy of your database.

If you don't want to buy a product, check out the free comparison utilities at Codeplex.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 4

Jimmy,

When you generate scripts, there is an option to "Script Database Create". It is set to False by default. If you set it to True, then it will include the database creation as well. This setting is on the Options dialog of the Script Wizard. Now on this dialog, there is a bunch of other options that you may want to look into as well. This may impact on how close or accurate a database structure will get scripted that you can use to create the new database.

You can launch Script Wizard by

1. Right click the database you need to script

3. Click Tasks in the context menu

3. Click Generate Script.

4. Click Next on the Welcome dialog

5. Select the database

6. Enable "Script all objects in the selected database" option. Click Next

7. It will bring you to the Options section of the dialog.

Here is a link from Book Online with more details. BOL - Generate Script

Hope this helps.

Paul White SSC Guru Points: 150467 More actions · Answer 5

In case anyone's interested, we might have a new option for this sort of thing in 2008 R2 - the stupidly-named but nonetheless intriguing 'DAC packs'. See http://www.brentozar.com/archive/2009/08/sql-server-2008-r2-the-dac-pack/

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Brent Ozar SSCrazy Points: 2451 More actions · Answer 6

DAC Packs aren't a solution, though, because they only support a subset of schema objects. For example, you can't include certain datatypes or stored procedures. Thanks for the link to my site though. 😉

Paul White SSC Guru Points: 150467 More actions · Answer 7

Brent Ozar (2/6/2010)
DAC Packs aren't a solution, though, because they only support a subset of schema objects. For example, you can't include certain datatypes or stored procedures. Thanks for the link to my site though. 😉

Thanks for the clarification Brent. Oh, and you're welcome.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

lionfan91 SSCrazy Eights Points: 8794 More actions · Answer 8

Jeffrey Williams-493691 (1/29/2010)
I have found that the easiest way to do this is to use a comparison tool like Redgate SQL Compare.

I will second this. Making a "clone" of a database using SQL Compare is a snap, even though you have to already have the target DB made (I think, maybe it can make the DB too, I've never tried). You can also just generate the SQL script that does this versus doing the sync "live". Not to overly "toot the horn" over SQL Compare, but I can't imagine how we got along with out it before we started using it.