Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    63

    Unanswered: Backing up tables?

    Hi guys, im currently developing an app and am changing the database as i go (dropping and recreating), but the problem is i have to re-enter any data i had from scratch.

    Does anyone know how i can backup data from specific tables so i can import it again the new table (as long as the columns are the same, ofc) so i dont have to do it each time?

    Im using sql server manager 2008

    Thanks!

  2. #2
    Join Date
    Jul 2011
    Posts
    9
    Hi,

    I am not sure how many tables you are dealing with, but does it make sense to have a test database with you backed up data, and to insert it from the test db to the live db? I doubt that would be feasible for 100 tables, but it may work decently for a smaller amount.

    Jason
    Webmaster at SQL Optimizations School

  3. #3
    Join Date
    Nov 2003
    Posts
    167
    I guess I don't completely understand why you're having to completely drop tables instead of altering them, but you could just make a quick copy of the tables:

    select * into [backup_table] from [table]

    If you have to drop table for some reason and then recreate it empty, you can then just reverse the insert:

    select * into [table] from [backup_table]

    You could easily write this into a script using information_schema.tables.

    I use a similar method to make copies of tables without copying the data:
    select * into [table_copy] from [table] where 1=2

    Another option is to create a linked server to excel or csv copies of your tables, then just pop the new tables from the linked server. This is also scriptable.
    Kit Lemmonds

  4. #4
    Join Date
    Jun 2011
    Posts
    63
    Quote Originally Posted by Jason W. View Post
    Hi,

    I am not sure how many tables you are dealing with, but does it make sense to have a test database with you backed up data, and to insert it from the test db to the live db? I doubt that would be feasible for 100 tables, but it may work decently for a smaller amount.

    Jason
    Webmaster at SQL Optimizations School
    The thing is the database i am working with is "in-progress" mode so i am always adding and removing, but i dont want to input the test data each time.

    Quote Originally Posted by onansalad View Post
    I guess I don't completely understand why you're having to completely drop tables instead of altering them, but you could just make a quick copy of the tables:

    select * into [backup_table] from [table]

    If you have to drop table for some reason and then recreate it empty, you can then just reverse the insert:

    select * into [table] from [backup_table]

    You could easily write this into a script using information_schema.tables.

    I use a similar method to make copies of tables without copying the data:
    select * into [table_copy] from [table] where 1=2

    Another option is to create a linked server to excel or csv copies of your tables, then just pop the new tables from the linked server. This is also scriptable.
    I am not altering tables because i am using an external program to generate my SQL. Therefor, i have to drop everything and re-create when i want to change something. Does your "select * into [table] from [backup_table]" work from one db to the other?

    Thanks!

  5. #5
    Join Date
    Nov 2003
    Posts
    167
    Yes - as long as you fully qualify in the statement:

    Code:
    select * into BackupDB.dbo.[backup_table] from ProductionDB.dbo.[table]
    and to restore
    Code:
    select * into ProductionDB.dbo.[table] from BackupDB.dbo.[backup_table]
    Actually, "select into" creates the table and would throw an error if ProductionDB.dbo.[table] already existed. If the table already exists but is empty, you would actually need an insert statement:
    Code:
    insert	ProductionDB.dbo.[table](column1, column2,...)
    select	column1, column2,....
    from	BackupDB.dbo.[backup_table]
    Kit Lemmonds

  6. #6
    Join Date
    Jun 2011
    Posts
    63
    Thanks man, will try that. But what if i need to a full DB backup, can this be done easily or do i have to do all these steps for each table?

    Thanks

  7. #7
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    To backup:
    Code:
    backup database <name> to disk = 'full path'
    To restore:
    Code:
    restor database <name> from disk = 'full path' with replace
    with replace is required only if you will restore a database in full or bulk logged recovery model without backing up the transaction log first.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Posting Permissions

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