Four ways of SQL server backup database

Time:2021-5-5

1. Use SQL server for maintenance plan

I won’t show you the screenshot here. This is relatively simple. It’s nothing more than dragging two “backup database” tasks and one “clear maintenance” task through SQL Server’s own maintenance plan.

Points to be noted:

1) In the backup task, select “all user databases” as far as possible, so as not to add a new database one day after selecting a specific database for backup, but forget to check it, resulting in the loss of backup.

2) Option to verify the integrity of the backup set and compress the backup as much as possible.

3) Try not to choose the root of the disk as the backup path.

2. Backup database (non XP) by script + job_ cmdshell)。If there are multiple libraries, you can write a cursor by yourself and use dynamic SQL to realize the backup of multiple libraries. I will provide an idea here. If you are lazy, you won’t be able to organize.

Copy codeThe code is as follows:
  DECLARE @filename VARCHAR(500)
  DECLARE @date DATETIME
  DECLARE @OLD_DATE DATETIME
  SET @date=GETDATE()
  SET @OLD_ Date = getdate () – 5 — backup more than 5 days will be deleted
Set @ filename =’e: \ “storage location \” database name – ‘+ cast (datepart)( YYYY,@DATE ) AS VARCHAR(10))+’-‘+CAST(DATEPART( MM,@DATE ) AS VARCHAR(10))+’-‘+CAST(DATEPART( DD,@DATE ) AS VARCHAR(10))+’.BAK’
Backup database [database name] to disk = @ file name with compression
  EXECUTE master.dbo.xp_ delete_ File 0, n’e: ‘storage location’, n’bak ‘ ,@OLD_ DATE,1

  GO

3. Backup the database by script + job (using XP)_ cmdshell),If you think your server’s network, code and firewall are secure enough, you can open XP_ Cmdshell.

If XP is not turned on in the database_ The function of cmdshell needs to use the following script to enable this function. Remember to close SP after opening_ configure。

Copy codeThe code is as follows:
  USE Master
  GO
  EXEC sp_configure ‘show advanced options’, 1;
  GO
  RECONFIGURE WITH OVERRIDE;
  GO
  EXEC sp_configure ‘xp_cmdshell’,1;
  GO
  RECONFIGURE WITH OVERRIDE;
  GO

The following is the backup script

Copy codeThe code is as follows:
  
DECLARE @DBNAME VARCHAR(128)
DECLARE @PATH VARCHAR(50)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DDATE VARCHAR(8)

SET @PATH = ‘E:\BackUp’
SET @DDATE = convert(char(8),getdate(),112)

–Delete backups longer than 1 day
SET @SQL =’xp_cmdshell ” forfiles /p “‘[email protected]+'” /d -0 /m *.bak /c “cmd /c echo deleting @file…. && del /f @file””’
EXEC (@SQL)

SET @SQL = ”
SELECT @SQL = @SQL + ‘
BACKUP DATABASE [‘+NAME+’] TO DISK = ”’[email protected]+’\’+REPLACE(name,’.’,”)[email protected]+’.bak ”’
FROM master..sysdatabases
WHERE NAME NOT IN (‘master’,’tempdb’,’model’,’msdb’)
EXEC (@SQL)

4. Use the PowerShell to call sqlcmd to execute the backup command.

Write the backup stored procedure PR under the master library_ one

Then create a PowerShell script, paste the following statement into it and save it as xx.ps1, and execute the backup regularly through the task scheduling of windows (for example, the statement of deleting the backup after expiration can also be realized through PowerShell. If you don’t have an environment at home, you can write an idea casually. Sorry for not writing it all).

Copy codeThe code is as follows:
$dbname = ‘test’
write-host “——“$dbname
& cmd /c “sqlcmd -U sa -P 123456  -S 127.0.0.1 -Q `” pr_1 ‘$dbname’`” “

The above are several common backup methods. You can choose your own backup method according to your own business scenario.