Backup Only a Section of a Massive Database Table

Unknown

Backup Only a Section of a Massive Database Table

Unknown

Can you generate a database insert script that is restricted to the results of a SQL WHERE clause?
I need to backup only a 5GB part of a 20GB table. I dont want to backup the whole thing because:

1. I would need to insert 15GBs of redundant data
2. I would overwrite the existing data, losing any new records written after the backup.

I want to backup this data because I dont really need it and want to delete it, but I dont feel comfortable simply deleting it in case there ends up being issues and we need to recover it.
[i]edited by Sissafriss on 5/9/2019[/i]
[i]edited by Sissafriss on 5/9/2019[/i]

Unknown

Yes, it is possible to do this.
The solution requires that you create a new table from the original that is populated only by the records you select. These records are the records you are “backing up” that you intend to delete. The new table will be your “back-up”. This way you can simply generate an insert script for this new table that inserts into the original table.
There are two caveats to this solution, and therefore two overall ways to implement it:

Preferred: [u]Method 1 (Create Temp Database):[/u]

    1. This query creates the new “back-up” table based on your where clause inside the temp DB

Query:

select *
into TempDB.dbo.TableName (Should be named the same as the original table)
from OriginalDB.dbo.TableName
where [Your Where Clause Here];

2. Delete records from original table using your where clause
3. Generate insert script using the new “back-up” table, replace the Use database name argument with the original database name (shown in picture attachment)

[u]Method 2 (Create table in same database):[/u]

1. This query creates the new “back-up” table based on your where clause

Query:

select *
into backupTableName
from originalTableName
where [Your Where Clause Here];

2. Delete records from original table using your where clause
3. Generate insert script using the new “back-up” table, find and replace all mentions of table name “backUpTableName” with “originalTableName”

[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif][color=#000000]Method 2 requires using a text-editor with a good find-and-replace feature, and would require performing this replace action on what I assume will be a gigantic insert script file.[/color][/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]
[/font][/color]
[color=rgb(34, 34, 34)][font=Arial, Helvetica, sans-serif]The resulting generated script from either solution will insert the “back-up” table records into the original table, restoring its original state.[/font][/color]