数据库的自动每日备份

Question

我试图在Server 2008 R2中创建数据库的自动/每日备份。我做了一些研究，并了解到，由于我使用的是速成版，它不附带维护计划，我无法创建数据库备份自动化/每日基础。因此，唯一的可能是我必须使用TSQL或创建作业。我对SQL作业不太了解，所以我只剩下T.

有人能解释我如何使用存储过程在that中自动备份数据库吗?或者还有其他选项我必须这样做。

致以问候。

Answer 1

由于您使用的是，所以您必须具有创造性。您没有任何Server本机调度工具，您可以利用任务调度器，并安排每天运行SQLCMD的任务，并将查询文本安排为备份数据库。

我建议创建一个脚本，它可以执行一些字符串操作来为BACKUP DATABASE to生成一个日期唯一的文件名。

您的脚本可能类似于这样的内容：

-- declare the backup filename (with path) without the file extension
declare @DestFile varchar(128) = 'C:\BackupDir\BackupFileName_';

-- get the current date as a nice and sortable string
declare @CurrentDate varchar(64) = '';
set @CurrentDate = replace(convert(varchar(10), getdate(), 111), '/', '');

set @DestFile += @CurrentDate + '.bak';

backup database YourDatabaseName
to disk = @DestFile;
go

将to保存到脚本文件中(例如，C:\YourScriptDir\BackupDatabase.sql)。要使用SQLCMD运行此脚本，可以执行以下操作：

sqlcmd -S YourServerName\YourInstanceName -i C:\YourScriptDir\BackupDatabase.sql

然后，只需安排任务计划程序每天运行。这是一个巨大的意大利面解决方案，但这是你得到的免费版本。

(上述过程将以简单恢复模式为数据库量身定做。如果您是完整的，那么您也需要考虑事务日志备份)

Answer 2

试试我们的SQLBackupAndFTP工具。您可以运行server数据库的预定备份(完整、差异或事务日志)、运行文件/文件夹备份、压缩和加密备份、将备份存储在网络、FTP服务器、Dropbox、Box、亚马逊S3、谷歌驱动器或SkyDrive上，在工作成功失败时给自己发送电子邮件确认。

Answer 3

https://support.microsoft.com/en-us/kb/2019698如何在Server中调度和自动备份Server数据库

上面的链接是所需的。

将此存储过程添加到主数据库：

// Copyright © Microsoft Corporation.  All Rights Reserved.

// This code released under the terms of the 

// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

USE [master]  

GO  

/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/  

SET ANSI_NULLS ON  

GO  

SET QUOTED_IDENTIFIER ON  

GO  



-- =============================================  

-- Author: Microsoft  

-- Create date: 2010-02-06

-- Description: Backup Databases for SQLExpress 

-- Parameter1: databaseName  

-- Parameter2: backupType F=full, D=differential, L=log

-- Parameter3: backup file location

-- ============================================= 



CREATE PROCEDURE [dbo].[sp_BackupDatabases]   

            @databaseName sysname = null, 

            @backupType CHAR(1), 

            @backupLocation nvarchar(200)  

AS  



       SET NOCOUNT ON;  



            DECLARE @DBs TABLE

            (

                  ID int IDENTITY PRIMARY KEY,

                  DBNAME nvarchar(500)

            )



             -- Pick out only databases which are online in case ALL databases are chosen to be backed up

             -- If specific database is chosen to be backed up only pick that out from @DBs

            INSERT INTO @DBs (DBNAME) 

            SELECT Name FROM master.sys.databases 

            where state=0

            AND name=@DatabaseName 

            OR @DatabaseName IS NULL 

            ORDER BY Name



            -- Filter out databases which do not need to backed up

            IF @backupType='F'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')

                  END

            ELSE IF @backupType='D'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE IF @backupType='L'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE

                  BEGIN

                  RETURN

                  END



            -- Declare variables

            DECLARE @BackupName varchar(100)

            DECLARE @BackupFile varchar(100)

            DECLARE @DBNAME varchar(300)

            DECLARE @sqlCommand NVARCHAR(1000)  

        DECLARE @dateTime NVARCHAR(20)

            DECLARE @Loop int                   



            -- Loop through the databases one by one

            SELECT @Loop = min(ID) FROM @DBs



      WHILE @Loop IS NOT NULL

      BEGIN



-- Database Names have to be in [dbname] format since some have - or _ in their name

      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'



-- Set the current date and time n yyyyhhmmss format

      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')   



-- Create backup filename in path\filename.extension format for full,diff and log backups

      IF @backupType = 'F' 

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'D' 

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'L'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'



-- Provide the backup a name for storing in the media

      IF @backupType = 'F'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime

      IF @backupType = 'D'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime

      IF @backupType = 'L'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime



-- Generate the dynamic SQL command to be executed



       IF @backupType = 'F'  

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'

                  END

       IF @backupType = 'D' 

                  BEGIN 

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'         

                  END

       IF @backupType = 'L'  

                  BEGIN

               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'         

                  END



-- Execute the generated SQL command

       EXEC(@sqlCommand)



-- Goto the next database

SELECT @Loop = min(ID) FROM @DBs where ID>@Loop



END

运行脚本(或将其添加到任务调度程序中)：

Example1: Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

sqlcmd -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"  

 Example2: Differential backups of all databases in the local named instance of SQLEXPRESS by using a SQLLogin and its password

// Sqlbackup.bat

sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases  @backupLocation ='D:\SQLBackups', @BackupType=’D’"

 Note: The SQLLogin shouldhave at least the Backup Operator role in SQL Server. 




Example 3: Log backups of all databases in local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"





Example 4: Full backups of the database USERDB in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=’USERDB’, @backupType='F'"

Similarly, you can make a differential backup of USERDB by pasting in 'D' for the @backupType parameter and a log backup of USERDB by pasting in 'L' for the @backupType parameter.