How to back up a table in SQL Server?

1 year ago
Noah Thompson
2 minutes

You can back up a table using the built-in backup tool of SQL Server or by writing a script.

Option 1: Utilize the SQL Server backup tool.

  1. Open SQL Server Management Studio (SSMS).
  2. Connect to your database server.
  3. Locate the database you want to back up in the object explorer.
  4. Right-click on the database, select “Tasks” and then choose “Backup”.
  5. In the backup database dialog box, select the “Tables/Views/Indexes” tab.
  6. In the “Select Table/View/Index Backup” section, choose the table you want to back up.
  7. Specify the location and name of the backup file.
  8. Click “OK” to start the backup.

Option 2: Writing a script for backup
You can backup a table using T-SQL commands. Here is an example script:

USE [YourDatabaseName]
GO

-- 创建一个新的文件组用于存储备份
ALTER DATABASE [YourDatabaseName]
ADD FILEGROUP [TableBackup]
GO

-- 将表移到新的文件组
ALTER TABLE [YourTableName]
MOVE TO [TableBackup]
GO

-- 创建一个新的文件来存储表的备份数据
DECLARE @BackupFile NVARCHAR(200) = 'C:\Backup\YourTableName.bak'
BACKUP DATABASE [YourDatabaseName]
FILEGROUP = N'TableBackup'
TO DISK = @BackupFile
WITH FORMAT
GO

-- 将表移回到原来的文件组
ALTER TABLE [YourTableName]
MOVE TO [PRIMARY]
GO

请将 YourDatabaseName 替换为你的数据库名称，YourTableName 替换为你要备份的表的名称，C:\Backup\YourTableName.bak 替换为你希望备份文件保存的位置和名称。

This script will move the table to a new file group, backup the new file group, and then move the table back to the original file group. This ensures that the backup only contains the data from that table.

These are two methods for backing up data tables, you can choose the one that best fits your needs.

bannerAds