2

I need to Truncate a table in Sql server which containing around 250GB data but before truncating i need to take backup of this,it may to csv file or other mode. What is best way to do it.

  • 1
    Use the bcp utility. – Scott Hodgin Jul 16 at 12:27
  • 2
    I would rather create an empty database, copy the table into it and then take a full compressed backup. Getting text presentation of 250GB data to text file isn't such a good idea on my opinion. – Denis Rubashkin Jul 16 at 12:38
  • Select * into bkptable from table. You should be able to create a new DB and backup that DB as a compressed backup. Select * into NewDB.dbo.bkptable Note that this mode will NOT copy indexes, constraints or other stuff, you will need to script your original table. (right click -> Script table as -->Create to) – dbamex Jul 16 at 16:00
2

I'd recommend using the BCP utility. From that documentation:

Copying table rows into a data file (with a trusted connection)

Basic

This example creates a data file named StockItemTransactions_character.bcp and copies the table data into it using character format.

At a command prompt, enter the following command:

bcp WideWorldImporters.Warehouse.StockItemTransactions out D:\BCP\StockItemTransactions_character.bcp -c -T
  • Can i reimport with the same file?As i can see in msdn If you use bcp to back up your data, create a format file to record the data format. bcp data files do not include any schema or format information, so if a table or view is dropped and you do not have a format file, you may be unable to import the data. – Darshan M Jul 16 at 13:03
  • @DarshanM - BCP handles both importing and exporting. You just need to use the correct out OR in parameters. The link I provided has many examples. A format file is not necessarily a requirement. You can script the current table structure as DDL, create that table on a different database or server and use the bcp utility to import the data from the file. – Scott Hodgin Jul 16 at 13:06
  • @DarshanM - test your bcp export/import using a smaller table so you can get the process down and feel confident it will work for you. – Scott Hodgin Jul 16 at 13:09
  • Thanks. But i am worried about format file or schema .what is format file and how to create it as i mentioned in the previous command. Or shall i take backup without any worries – Darshan M Jul 16 at 13:12
  • @DarshanM - I've used bcp many times to take quick backups of individual tables and never needed to define a format file. I'm sure there are reasons why you might need a format file for special situations.. I'd suggest using the example I provided (without specifying a format file), export the data, and try importing the data into another database. You definitely want to feel confident about importing the file before you truncate the original table. – Scott Hodgin Jul 16 at 13:16
0

Just to provide an alternative to exporting to a file via BCP or SSIS, you can export this data to a new database (as suggested by Denis Rubashkin in the comments) and then create a backup of that database.

The benefits that exporting data to a secondary database provide over exporting the table to a .csv or some other external flat file are as follows:

  1. Easier to secure
    • You can limit user access to this data via the same mechanisms you use in your current system (e.g. same AD groups, users, etc.)
    • You can encrypt this data via TDE and/or column compression
  2. Smaller data footprint
    • You can compress this data (after the fact) via compressed pages/rows or clustered columnstore indexes (pending proper SQL Server version and edition¹)
  3. Likely faster
    • BCP suffers from one main problem in my opinion, and that is that it runs serially. I am unaware of a way to export data to a flat file, in parallel, via BCP or even SSIS. Running a SELECT INTO statement in SQL Server 2014 and later, or performing an INSERT INTO tbl WITH (TABLOCK) SELECT statement in SQL Server 2016 or later will load this data into your backup database in parallel. 250GB is a sizable amount of data, and depending on your system, parallel operations could significantly decrease the amount of time needed to complete your operation.

¹ - Columnstore Indexing is exclusive to Enterprise edition for SQL 2014 - SQL 2016 CU9 (13.0.2216.0 or 13.0.2218.0) and available in Standard with SQL Server 2016 SP1 and thereafter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.