0

In SQL Server, I want to clear personal info and backup it

  1. Backup original DB
  2. Restore as another DB name
  3. Clear personal info in another DB
  4. Backup another DB
  5. Delete rest files
  6. Zip Backup DB

I finished 1~5. but couldn't find a way to do 6.

I Want to compress bak file to zip here. For instance, below code can be used in Powershell script. Is there a way to use this .Net function in SQL script?

[System.IO.Compression.ZipFile]::CreateFromDirectory($CurrentPath, $DeployHistoryFilePath)

Below is my full script.

DECLARE @DBName NVARCHAR(MAX) = N'TestDB'
DECLARE @BackupPath NVARCHAR(MAX) = N'D:\Database\Backup'

EXEC ('master.dbo.xp_create_subdir N'''+ @BackupPath +'''')

DECLARE @BackupName NVARCHAR(MAX) = N'OnCube_' + REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(MAX), GETDATE(), 120), N'-', N''), N':', N''), N' ', N'_')
DECLARE @DiskFile NVARCHAR(MAX) = @BackupPath + N'\' + @BackupName + N'.bak'

BACKUP DATABASE @DBName TO DISK = @DiskFile

DECLARE @SQL NVARCHAR(MAX) = 'SELECT TOP (1) @OriginalMdf = name FROM ' + @DBName + '.sys.database_files WHERE file_id = 1'
DECLARE @OriginalMdf NVARCHAR(MAX)
EXEC sp_executesql @SQL, N'@OriginalMdf NVARCHAR(MAX) OUT', @OriginalMdf out

SET @SQL = 'SELECT TOP (1) @OriginalLdf = name FROM ' + @DBName + '.sys.database_files WHERE file_id = 2'
DECLARE @OriginalLdf NVARCHAR(MAX)
EXEC sp_executesql @SQL, N'@OriginalLdf NVARCHAR(MAX) OUT', @OriginalLdf out

DECLARE @PartialMdf NVARCHAR(MAX) = @BackupPath + N'\' + @BackupName + N'.mdf'
DECLARE @PartialLdf NVARCHAR(MAX) = @BackupPath + N'\' + @BackupName + N'_0.ldf'

RESTORE FILELISTONLY FROM DISK = @DiskFile

RESTORE DATABASE @BackupName
    FROM DISK = @DiskFile
    WITH MOVE @OriginalMdf TO @PartialMdf,
    MOVE @OriginalLdf TO @PartialLdf

EXEC (N'
USE [' + @BackupName + ']
UPDATE Person
SET
    PatientNo       = NULL
,   PatientName     = N''Cleared'' + CONVERT(NVARCHAR(MAX), RawID)
,   RoomNo          = NULL
,   BedNo           = NULL
,   Birthday        = NULL
,   Sex             = NULL
,   Address         = NULL
,   AdmitDate       = NULL
,   AdmitNo         = NULL
,   Description     = NULL
,   DischargedDate  = NULL
')

DECLARE @ClearedDiskFile NVARCHAR(MAX) = @BackupPath + N'\' + @BackupName + N'_PatientInfoCleared.bak'

BACKUP DATABASE @BackupName TO DISK = @ClearedDiskFile

EXEC('DROP DATABASE [' + @BackupName + ']')
EXEC ('xp_cmdshell ''del "' + @DiskFile + '"''')

-- I Want to compress bak file to zip here
-- For instance, below code can be used in Powershell script. Is there a way to use this .Net function in SQL script?
--  [System.IO.Compression.ZipFile]::CreateFromDirectory($CurrentPath, $DeployHistoryFilePath)

PRINT N'Success to make ' + @ClearedDiskFile + '. Patient informations are all cleared'
sugy21
  • 119
  • 9
  • You should *really* be using `QUOTENAME` here for your injected values. If you want a compressed backup file though, will not just use the `COMPRESSION` option? You won't gaun any/much further compression by then putting an already compressed backup in a zip folder. – Thom A Jul 04 '19 at 08:02
  • Thanks Larnu, I don't want compress option. want to make bak file to zip file. – sugy21 Jul 04 '19 at 08:04
  • Then i suggest you use powershell to create the (uncompressed?) backup and zip it, rather than using T-SQL. Seems like an odd idea to reinvent the wheel though. – Thom A Jul 04 '19 at 08:05
  • it only takes two clicks to make a zip file using existing tools 7zip, zip, rar, and many more. if you want to automate as Larnu suggested -you need to write a powershell script. – Yeou Jul 04 '19 at 08:10

1 Answers1

0

Is there a way to use this .Net function in SQL script?

Yes, you can use SQL CLR with C#

see samples Using 7-zip and SharpZipLib here:

also , you can create zip file from SQL without Powershell script: Create zip file from SQL Server

NajiMakhoul
  • 1,623
  • 2
  • 16
  • 30