Post reply

how to backup without the whole data

  • February 28, 2010 at 6:05 pm

    #386410

    hi,

    just wondering, how would i backup the database (eg:adventureworks) with only having first few rows of actual data from each table.?

    reason: have database on remote site with 100gb of data... now would like to get it locally with only few rows of data in it.

    thanks

  • February 28, 2010 at 7:55 pm

    #1126339

    There is no command that will do a partial backup, that would not really be a backup at all.

    You can go table by table with "select top 10 * from <table>" but that won't give you logical consistency.

    You will need to start at the top and cascade thru your related child records with the related records you want to move to the local server.

    Converting oxygen into carbon dioxide, since 1955.

  • February 28, 2010 at 9:39 pm

    #1126356

    thanks Steve,

    is there a way to backup database (tables,,sps...etc) without any data.

  • February 28, 2010 at 9:45 pm

    #1126358

    In the object explorer, right-click on the database you wish to script. Under Tasks, select Generate Scripts. You can script the tables, views, procedures, UDFs etc. You can save this to a file.

    Converting oxygen into carbon dioxide, since 1955.

  • February 28, 2010 at 10:58 pm

    #1126365

    thanks for your help:) much appreciated.

  • March 1, 2010 at 5:03 pm

    #1126783

    You could build an SSIS package to suck out a few rows of data from each table, but as (the other) Steve mentioned, this doesn't mean you have consistent data. The first 10 rows of data from a child table might not match up with the first 10 rows from a parent and you wouldn't be able to insert the data.

    Your best bet, IMHO, is to build a routine to pare down data. Then do a backup and restore on the source server (or another serveR), and run your package to remove data that you don't need.

  • March 1, 2010 at 5:06 pm

    #1126784

    cool..thanks i will see how I go...

Viewing 7 posts - 1 through 6 (of 6 total)

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