SQL Server Backup Database 4 ways to introduce _mssql

1. Using SQL Server's maintenance plan

I'm not going to show you the screenshot here, this is simpler than using SQL Server's own maintenance plan to drag out 2 One ' backup database ' task and one ' Cleanup maintenance ' task.

Points to note:

1 have backup tasks inside Select the backup of the library as far as possible "all user database", so as to avoid selecting a specific database backup one day after the addition of a new database but forgot to check the selection caused the loss of backup.

2) option to verify that backup set integrity and compressed backups are selected as much as possible.

3 The backup path as far as possible not to select the disk root directory.

2. Back up the database (not xp_cmdshell) by scripting + job. if it is more than one library to write a cursor with dynamic SQL to achieve a multiple library backup, I here to provide a way of thinking lazy will not tidy up.

Copy Code code as follows:

DECLARE @filename VARCHAR (500)
DECLARE @date DATETIME
DECLARE @OLD_DATE DATETIME
SET @date =getdate ()
SET @OLD_DATE =getdate ()-5--more than 5 days of backup is about to be deleted
SET @FILENAME = ' e:\ storage location \ Database name-' +cast (DATEPART (YYYY, @DATE) as VARCHAR) + '-' +cast (DATEPART (MM, @DATE) as VARCHAR (10) ) + '-' +cast (DATEPART (DD, @DATE) as VARCHAR (10)) + '. BAK '
BACKUP database [DB name] to DISK = @filename with COMPRESSION
EXECUTE master.dbo.xp_delete_file 0,n ' e:\ storage location ', N ' bak ', @OLD_DATE, 1

Go

3. Back up the database by script + job (with xp_cmdshell),If you feel that your server's network, code, and firewall are secure enough to turn on the xp_cmdshell way to back up.

If the database does not open the function of xp_cmdshell need to use the following script to open this function, after the opening remember to close the sp_configure.

Copy Code code 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 bottom is a backup script

Copy Code code 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 for more than 1 days
SET @SQL = ' xp_cmdshell ' forfiles/p ' + @path + ' "/d-0/M *.bak/c" cmd/c echo deleting @file .... && del/f @fil E "'
EXEC (@SQL)

SET @SQL = '
SELECT @SQL = @SQL + '
BACKUP DATABASE [' +name+ '] to DISK = ' + @PATH + ' +replace ' (NAME, '. ', ') + @DDATE + '. Bak '
From Master.. sysdatabases
WHERE NAME isn't in (' master ', ' tempdb ', ' model ', ' msdb ')
EXEC (@SQL)

4. Invoke sqlcmd with PowerShell to perform the backup command.

Write a backup stored procedure below the Master library pr_1

Then create the PowerShell script, Paste the bottom statement in and save it as a xx.ps1, and perform a backup on a scheduled schedule through Windows Task Scheduler (I'm just going to give you an example. Delete a backup statement can also be implemented through Powershel, at home without the environment to write a way of thinking did not write all of you forgive).

Copy Code code as follows:

$dbname = ' Test '
Write-host "------" $dbname
& cmd/c "Sqlcmd-u sa-p 123456-s 127.0.0.1-q '" "Pr_1 '" "

These are the common methods of backup, and you can choose your own backup mode according to your business scenario.