Several ways to backup SQL Server

1. Using SQL Server's maintenance plan

In this case I will not give a demonstration, this is relatively simple, is simply through SQL Server's own maintenance plan to drag out 2 a ' backup database ' task and a ' Clean maintenance ' task.

Points to note:

1) There are backup tasks in the Select the Backup library as far as possible to choose the "All user database" This, so as not to select a specific database backup one day after the new database was added but forgot to check the result of the loss of backup.

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

3) Backup path try not to select the root directory of the disk.

2. Back up the database (non-xp_cmdshell) by script + job. If it is more than one library to write a cursor with a dynamic SQL to achieve multi-library backup, I am here to provide a thought lazy will not tidy up.

　　

DECLARE @filename VARCHAR (500)
DECLARE @date DATETIME
DECLARE @OLD_DATE DATETIME
SET @date =getdate ()
SET @OLD_DATE =getdate ()-5--Backup of more than 5 days 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 scripting + jobs (with xp_cmdshell), if you think your server's network, code, and firewalls are safe enough to turn on xp_cmdshell.

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

　　

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 Below is the backup script

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 "' [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. Invoke sqlcmd with PowerShell to execute the backup command.

Write a stored procedure for the backup below the Master library pr_1

Then create a PowerShell script, Paste the bottom statement into and save to XX.PS1, in the Task Scheduler through Windows to perform a backup on time (I just give an example expired delete the BACKUP statement can also be implemented through Powershel, at home without the environment on the casual writing a way of thinking did not write all of you forgive).

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

-------------------------------------------------------------------------------------------------

The above is a common method of backup, you can according to their own business scenarios to choose the appropriate backup method.

Several ways to backup SQL Server