4 methods for backing up databases from SQL Server and 4 methods for SQL Server

4 methods for backing up databases from SQL Server and 4 methods for SQL Server

1. SQL server maintenance plan

Here I will not explain it. This is relatively simple. It is nothing more than dragging two 'backup database' tasks and one 'clear maintenance 'task through sqlserver's own maintenance plan.

Notes:

1) select 'all user databases' for the database to be backed up in a backup task to avoid missing backups if a new database is added one day after a specific database backup is selected.

2) check whether the backup set integrity and compressed backup are selected.

3) do not select the disk root directory as far as possible in the backup path.

2. Back up the database (non-xp_mongoshell) using scripts + jobs ).If there are multiple databases, write a cursor and use dynamic SQL to implement multi-database backup. Here I will provide an idea to be lazy.
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 -- backups that exceed 5 days will be deleted soon
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 = @ 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 using scripts and jobs (using xp_mongoshell ),If you think your server's network, code, and firewall are safe enough, you can enable xp_mongoshell to back up data.

If the xp_cmdshell function is not enabled for the database, use the following script to enable this function. Remember to disable sp_configure after activating it.
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 _ Your shell', 1;
GO
Reconfigure with override;
GO

Below 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 a backup that exceeds one day
SET @ SQL = 'xp _ cmdshell' 'forfiles/p "'+ @ path +'"/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 = ''' + @ PATH + '\' + REPLACE (name ,'. ', '') + @ DDATE + '. bak '''
FROM master.. sysdatabases
Where name not in ('master', 'tempdb', 'model', 'msdb ')
EXEC (@ SQL)

4. Use powershell to call sqlcmd to execute the BACKUP command.

Write the backup Stored Procedure pr_1 under the master database

Create a powershell script, paste the following statement into it, and save it as xx. ps1: Execute the backup at a scheduled time through Windows Task Scheduling (for example, the statement for deleting backups after expiration can also be implemented through powershel, if you have no environment at home, you can write your thoughts without writing them 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 a backup method that suits your business needs.