Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    Unanswered: Can you backup/restore schema only?

    We have a giant database in production environment, and before we do any changes to it, we obviously want to test those changes against a development database.

    Problem is, the DB has become so huge that its becoming very time consuming and impractical to back it up, transfer it to or development server, restore it, and then then run our scripts against it.

    However, the nice thing is that 99% of our scripts have nothing to do with the actual data. we would still benefit greatly even if we were running the scripts against a completely empty database.

    Is there a way we can backup just the schema from our production database, and restore it to the development database? The schema would only be a few MB I would think. It would make my life a lot easier.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can just script out the database, with all the objects, and then run the script on an empty DB to recreate it.
    But you are going to miss all the reference and configuration table data in your app.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2010
    Posts
    4
    In mysql that will be:
    mysqldump --no-data --tables -u {USER_NAME} -p {PASSWORD} >> {FILE_NAME}

  4. #4
    Join Date
    Feb 2010
    Posts
    75
    blindman, great idea, but unfortunately its not that simple.
    When I try to run the script on a new/empty DB, I get a TON of errors. Im wading through them now to see what I can fix, but this is definetly not ideal.

    darke82, Although I'm a fan of mysql for small, no budget projects, thats not very helpful in the MS SQL Server forum

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The exact procedure varies a bit from one SQL Server build to another. The big "glitches" come at version changes (SQL 2000 to SQL 2005 to SQL 2008 to SQL 2008r2).

    The general idea is to use SSMS (SQL Server Managment Studio) to script the database, objects, schema, etc. Depending on what version of EM or SSIS you're using the process is a bit different but the concept is the same.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    To get a "clean build" in one shot, use one of the schema tools like ErWin or Visio.

    If you can't do that (and most people don't have that kind of "heavy duty" tool just sitting handy), then build multiple scripts. There might be some exceptions, but I'd build them as:

    Tables
    Views
    Code (Functions and Stored Procedures)
    Permissions

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Feb 2010
    Posts
    75
    When I go to the SSMS DB scripter, the available objects to script are:

    Database Roles
    Stored Procedures
    Tables
    User-defined data types
    User-defined functions
    Users


    Views and Permissions are not there, although its possible they are included in Tables and Roles or something.
    When I just clicked 'select all', it generated one big script, which had lots of problems running (cannot drop X, cannot create Y, etc etc).
    Are you saying that by default it generates the scripts in the wrong order, and I should script them individually so that I can run them in a better order?

    SQL Server 2008 BTW

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What are the errors you are getting? It is possible that pieces of your sql code are broken, but are never called any more.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2010
    Posts
    75
    blindman, you were right, some of my stored procedures were broken, so creating them anew was failing.

    But also, some of the roles and users creations were failing... not really sure why but actually I dont think I need them for my purpose so I might just omit them from the script.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The roles and users created at your database level script will fail if the server you run the script on does not have corresponding users defined at the server security level.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is a script I wrote a year or two ago to identify broken code in a database:
    Code:
    begin
    
    set nocount on
    create table #SQLStrings (StringID bigint identity(1, 1), SQLString varchar(max))
    create table #CompileErrors (ErrorMessage varchar(4000))
    declare	@SQLString varchar(max)
    declare	@ObjectName varchar(500)
    declare	@ObjectType varchar(10)
    
    declare CodeObjectList cursor for
        select	[schemas].[name] + '.' + [objects].[name], [objects].[type]
        from	sys.objects objects
    			inner join sys.schemas schemas on objects.schema_id = schemas.schema_id
        where	type in ('P', 'TR', 'V', 'TF', 'FN', 'IF')
    			and objects.name <> 'RecompileSQLCode'
        order by parent_object_id
    
    open CodeObjectList
    fetch next from CodeObjectList into @ObjectName, @ObjectType
    while @@fetch_status = 0
        begin
        truncate table #SQLStrings
        insert into #SQLStrings exec sp_helptext @ObjectName
        set @SQLString = ''
        select  @SQLString = @SQLString + ltrim(SQLString) from #SQLStrings order by StringID
    
        set	@SQLString = left(@SQLString, charindex('CREATE ', @SQLString)-1) + 'ALTER ' + right(@SQLString, len(@SQLString)-charindex('CREATE ', @SQLString) -6)
    
        begin try
    	exec (@SQLString)
        end try
        begin catch
    	insert into #CompileErrors (ErrorMessage) values (@ObjectName + ' (' + rtrim(@ObjectType) + '): ' + error_message())
        end catch
        fetch next from CodeObjectList into @ObjectName, @ObjectType
        end
    
    close CodeObjectList
    deallocate CodeObjectList
    
    select * from #CompileErrors
    
    drop table #SQLStrings
    drop table #CompileErrors
    
    end
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2010
    Posts
    75
    OK, I actually got the scripts in the right order and was able to reconstruct an empty, schema-only version of the DB on a different server. Im pretty impressed!

    However,

    Quote Originally Posted by blindman View Post
    You can just script out the database, with all the objects, and then run the script on an empty DB to recreate it.
    But you are going to miss all the reference and configuration table data in your app.
    blindman of course you were right. What I have done is only semi-useful because all of the config tables are empty.

    So I'm still looking for a better approach. Ideally, what I want to do is a regular database backup, EXCEPT dont backup the data from 2 specific tables. Simply because omitting these 2 tables will make the backup about 500 MB instead of 80 GB. Seems to me this would be a pretty handy thing to do... strange that SQL server doesnt support it directly.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Two options come right to my mind.

    The easy answer is to install LiiteSpeed Backup. It will make the backups quickly and easily, and will allow you to selectively restore individual tables at will.

    Another solution requires creating a new filegroup in your database, and then "banishing" all of the tables you want to migrate easily to the new filegroup. This doesn't require any third party software, and might well have other benefits in your day-to-day use of the database.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by Pat Phelan View Post
    Another solution requires creating a new filegroup in your database, and then "banishing" all of the tables you want to migrate easily to the new filegroup. This doesn't require any third party software, and might well have other benefits in your day-to-day use of the database.

    -PatP
    This is really cool.

    So now for optimal DB design I have:

    1 FG for Data
    1 FG for Lookup Data
    1 FG for Indexes
    All on separate raid 10 arrays for optimal performance (plus log on it's own raid array). Or if cost is an issue, each on their own disk. Actually I think Data and Lookup Data FG's can go on their own array/disk.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not sure where you're headed with the idea, but that doesn't sound right to me...

    You can backup a filegroup by itself using just standard Microsoft SQL backups. My thought was to get everything you wanted to move migrated to its own filegroup, so that would include the data (rows) and indicies for the tables that you want to be able to backup. Leave the behemoth tables on whatever filegroup they are on now. Move the smaller tables/indicies/etc onto a new filegroup so that you can back it up then restore it separately.

    The only potential downside is if some of the data you want to move has foreign key relationships into the data that you don't plan to move... That causes heartburn (but you can sometimes work around it too).

    Don't go crazy with filegroups. They are useful tools, but can become a nightmare if you use them indiscriminately.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •