Post reply

Back Up Script Excluding Table

  • February 4, 2016 at 1:02 am

    #311249

    Hi,

    Iam using below code to export the database into .bak file, but i dont need all the tables to export i want to exclude some of the table..

    Please help on the script

    Declare @BackupFileName varchar(500)

    set @BackupFileName = N'D:\icemdb_backup\cmstest_'+REPLACE (convert(varchar(50), getdate(), 109), ':','_')+'.bak'

    BACKUP DATABASE [cmstest] TO DISK = @BackupFileName WITH NOFORMAT, NOINIT,

    NAME = N'cmstest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    Thanks

  • February 4, 2016 at 12:44 pm

    #1856651

    A backup is not intended as a tool for export. It is a tool for disaster recovery.

    There is no way to exclude specific tables from a backup, unless you planned for this when you created the database and objects and assigned the tables you want to include in a specific filegroup. However, I am not sure that you will ever be able to restore from a backup of only a single filegroup. I guess it is possible if you get the exact right sequence of steps, but I would definitely test both the backup and the restore a few times on play databases before committing my production data to this.

    If you do not know exactly how file- and filegroup-level backups and restores work, then don't even go there. This is not something you want to have to investigate when the CEO is standing behind you waiting for the company database to be restored from a disaster.

    If you are looking for export options, then either take and restore a full backup and delete files later, or use a specific export tool.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • February 7, 2016 at 5:14 am

    #1857026

    Hi Hugo,

    Thanks for the reply..

    Can you suggest me the tool where i can export the data into .bak file by excluding some of the tables..

    bcs some of the unnecessary tables having more than 500 MB size i dont want to use it..

    Thanks

  • February 7, 2016 at 5:28 am

    #1857027

    Venki508 (2/7/2016)

    Can you suggest me the tool where i can export the data into .bak file by excluding some of the tables..

    Again, this is not possible.

    If you want to export data, e.g. to flat files or to another database, then look into SSIS or into the Import and Export Data wizard. But they will not create a .bak file.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • February 7, 2016 at 5:33 am

    #1857028

    Venki508 (2/7/2016)

    Can you suggest me the tool where i can export the data into .bak file by excluding some of the tables..

    bcs some of the unnecessary tables having more than 500 MB size i dont want to use it..

    Backups are copies of the entire database, used for recoverability purposes (recreating the entire DB after a disaster). They're not exports.

    Use the import-export wizard, or write a SSIS package to export part of the DB to flat files

    500MB's pretty small these days, honestly not worth worrying about an extra GB or two in a backup file.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 7, 2016 at 6:05 am

    #1857029

    Hi,

    Thanks for the reply,

    Here i go with the exact requirement

    We have implmented ERP recently with sql server 2008 database. we are facing some of the issues with client data..so we want to take a backup and put it in local server for the testing..

    And this we are doing through .bak file only

    On this process we dont need to export all the tables since some of the tables having only non functinal data which is not at all usefull for testing and those are occupying half of the database size..

    And if i take full backup, it will be time consuming to move the file or to upload the file with total size.

    Please suggest....

  • February 7, 2016 at 7:15 am

    #1857033

    Venki508 (2/7/2016)

    Hi,

    Thanks for the reply,

    Here i go with the exact requirement

    We have implmented ERP recently with sql server 2008 database. we are facing some of the issues with client data..so we want to take a backup and put it in local server for the testing..

    And this we are doing through .bak file only

    On this process we dont need to export all the tables since some of the tables having only non functinal data which is not at all usefull for testing and those are occupying half of the database size..

    And if i take full backup, it will be time consuming to move the file or to upload the file with total size.

    Please suggest....

    Okay, so you really want to copy the database to another instance. It sounds like you're going about it the same way I would - take a backup and restore it on the destination server. However, like Gail and Hugo have already said, a backup is a backup of the database, not a utility for exporting data. Further, if you're going to test functionality, you're probably going to need the entire database structure in the test database. It depends on what you want to test, but my guess is that you'll need the whole structure. If you're only going to check data, then you might not need the whole structure. It depends on what you're going to test.

    All backups take time. How much time depends on a lot of factors, including the size of your database and the speed of your disks. No matter what hardware you're using, however, it will take time. The import will also take time. If you need to create a copy of the database, you're going to need to spend the time to do so.

    If you don't want to restore the whole table, then an alternative is required. You can use SSIS or T-SQL procedures to copy data from one server to the other. If, however, you have to develop these things, then you need to consider the time it'll take to write them against the time required to just backup/restore the whole database. Also, consider how many times you're going to have to perform this activity. The development time may be worth it or it might not.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • February 7, 2016 at 9:01 am

    #1857045

    Take a full backup, copy it, restore, drop the tables you don't need. It'll be the fastest way of doing what you want.

    Backups are of the entire database, there's no option in a backup to exclude a few tables.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 7, 2016 at 9:49 pm

    #1857069

    Thank you all,

    I will do the same..

  • February 7, 2016 at 10:02 pm

    #1857070

    Sorry to disturb you guys...

    Just for the confirmation..Iam new to the Sql SERVER database..

    So dont we have like this export script in Sql SERVER

    expdp username/password schemas=scott exclude=table:"IN('emp','history')" directory=data_pump_dir dumpfile=test.dmp logfile=data_dump_dir:test.log

    I have been using the above script to export Oracle Schema by excluding some of the tables.

    Thanks

  • February 8, 2016 at 1:38 am

    #1857074

    Venki508 (2/7/2016)

    So dont we have like this export script in Sql SERVER

    expdp username/password schemas=scott exclude=table:"IN('emp','history')" directory=data_pump_dir dumpfile=test.dmp logfile=data_dump_dir:test.log

    Hugo Kornelis (2/7/2016)

    If you want to export data, e.g. to flat files or to another database, then look into SSIS or into the Import and Export Data wizard. But they will not create a .bak file.

    GilaMonster (2/7/2016)

    Use the import-export wizard, or write a SSIS package to export part of the DB to flat files

    You can use bcp as well to copy tables out to flat file, but any export/import will probably be slower than a straight backup/restore.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 9, 2016 at 9:32 am

    #1857430

    Venki508 (2/7/2016)

    Sorry to disturb you guys...

    Just for the confirmation..Iam new to the Sql SERVER database..

    So dont we have like this export script in Sql SERVER

    expdp username/password schemas=scott exclude=table:"IN('emp','history')" directory=data_pump_dir dumpfile=test.dmp logfile=data_dump_dir:test.log

    I have been using the above script to export Oracle Schema by excluding some of the tables.

    Thanks

    datapump is an Oracle feature only, BCP won't do anything near what datapump does.

    If you want to move the database to another server, backup and restore and drop any objects from the restored database that you do not require, it's as simples as that!!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 12 posts - 1 through 12 (of 12 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy