Post reply

Backup database structure and schema only

  • 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.

  • January 28, 2010 at 8:10 pm

    #1110427

    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.

  • January 28, 2010 at 8:51 pm

    #1110436

    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.

  • January 29, 2010 at 12:54 pm

    #1110961

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • January 29, 2010 at 2:00 pm

    #1110990

    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.

  • January 31, 2010 at 3:16 am

    #1111301

    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

  • February 6, 2010 at 5:24 pm

    #1115109

    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. 😉

  • February 7, 2010 at 3:44 am

    #1115164

    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

  • February 8, 2010 at 6:51 am

    #1115443

    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.

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

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