☰

SQL Server 2008 Express-“最佳”备份解决方案？

tag-icon • tag-icon windows backup sql-server-2008 sql
SQL Server 2008 Express-“最佳”备份解决方案？

使用时您会推荐哪些备份解决方案SQL Server 2008 Express? 我对 SQL Server 还很陌生，但由于我来自MySQL背景我想在另一台计算机上设置复制，然后复制该服务器的备份。

但不幸的是，Express Edition 不提供复制功能。

该网站访问量很大，因此不能出现延迟和停机。我还在考虑每天备份两次或类似的事情。

您会推荐什么？我有多台电脑可以使用，但我不知道这是否有用，因为我使用的是 Express 版本。

答案1

SQL Server Express 2008 支持数据库备份。它缺少允许安排备份的 SQL Agent 和用于创建备份任务的维护计划向导。

您可以通过两种不同的方式备份数据库：

  1. 使用Microsoft SQL Server 管理工作室 Express在“任务”下的每个数据库的右键单击菜单上都有“备份”选项。
  2. 使用 T-SQL 手动编写备份脚本。阅读T-SQL BACKUP 命令的 MSDN 文档语法
    类似于：BACKUP DATABASE MyDatabase TO DISK='C:\MyDatabase.bak';

如果要安排备份作业，您必须编写 T-SQL 脚本，然后使用 Windows 任务计划来调用SQL命令按照您感兴趣的每个计划运行脚本：

 sqlcmd -s server_name\sqlexpress -i C:\SqlJobs\backup.sql -o C:\Logs\output.txt

答案2

我用SQL备份和FTP- 奇妙而又简单的产品。

答案3

我写了备份脚本供自己使用，按照帖子中提到的方法安装斯普拉特内：

----- Version: 2.0 - 2009-12-12 - SQL 2000 Compatible
----- Pham Kim Ngan ([email protected])
----- Usage:
-- Copy 7za.exe (http://www.7-zip.org/download.html - Command Line Version) to @CFG_BACKUP_PATH
-- Modify @CFG_BACKUP_PATH = <Backup Store Path> - no long filename/directory please
-- Modify @CFG_DAYS_DELETE = Days to keep backups
-- Enable 'xp_cmdshell' (SQL 2005/EXPRESS or higher)

----- Configuration Variables
DECLARE @CFG_BACKUP_PATH NVARCHAR(256)
DECLARE @CFG_DAYS_DELETE INT

SET @CFG_BACKUP_PATH = 'C:\DatabaseBackup'
SET @CFG_DAYS_DELETE = 30

DECLARE @Today DATETIME
DECLARE @TodayName CHAR(8)
SET @Today = GETDATE()
SET @TodayName = CONVERT(CHAR(8), @Today, 112)

DECLARE @id INT
DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @cmd VARCHAR(256)

----- Create Temporarity Directory
DECLARE @TempDir VARCHAR(256)
SET @TempDir = @CFG_BACKUP_PATH + '\' + CONVERT(VARCHAR(256), NEWID())
SET @cmd = 'md ' + @TempDir
EXEC xp_cmdshell @cmd, no_output

----- List of current databases, only 'ONLINE' databases to be backup
DECLARE @dbList TABLE
    (
      dbno INT IDENTITY,
      dbname NVARCHAR(256)
    )

INSERT  INTO @dbList ( dbname )
        SELECT  name
        FROM    master.dbo.sysdatabases
        WHERE   ( name NOT IN ( 'tempdb' ) )
                AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'


------ Starting backup, one by one
SELECT  @id = dbno,
        @name = dbname
FROM    @dbList
WHERE   dbno = 1
WHILE @@ROWCOUNT = 1
    BEGIN
        PRINT N'++ Backup: ' + @name
        SET @path = @TempDir + '\' + @name + '.bak'

        BACKUP DATABASE @name TO DISK = @path

        SELECT  @id = dbno,
                @name = dbname
        FROM    @dbList
        WHERE   dbno = @id + 1
    END

PRINT N'++ Compressing: ' + @TempDir

----- Delete output file if existed
SET @cmd = 'del /f /q ' + @CFG_BACKUP_PATH + '\' + @TodayName + '.ZIP'
EXEC xp_cmdshell @cmd, no_output

DECLARE @Count INT
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
----- Compress, -mx1 = Set Compression Ratio to 1 (very low)
SET @cmd = @CFG_BACKUP_PATH + '\7za.exe a -bd -y -tzip -mx1 '
SET @cmd = @cmd + @CFG_BACKUP_PATH + '\' + @TodayName + '.ZIP ' + @TempDir + '\*.bak"'
EXEC xp_cmdshell @cmd, no_output

SET @Count = DATEDIFF(second, @StartTime, GETDATE())
PRINT N'++ Compression Time: ' + CONVERT(VARCHAR, @Count) + ' seconds'
SET @Count = DATEDIFF(second, @Today, GETDATE())
PRINT N'++ Total Execution Time: ' + CONVERT(VARCHAR, @Count) + ' seconds'

---- Delete temporarity directory
SET @cmd = 'rd /s /q ' + @TempDir
EXEC xp_cmdshell @cmd, no_output

---- Delete previous backup versions
DECLARE @OlderDateName CHAR(8)
SET @OlderDateName = CONVERT(CHAR(8), @Today - @CFG_DAYS_DELETE, 112)

----- List all .ZIP files
CREATE TABLE #delList
    (
      subdirectory VARCHAR(256),
      depth INT,
      [file] BIT
    )
INSERT  INTO #delList
        EXEC xp_dirtree @CFG_BACKUP_PATH, 1, 1
DELETE  #delList
WHERE   RIGHT(subdirectory, 4) <> '.ZIP'

SELECT  @Count = COUNT(1)
FROM    #delList
PRINT N'++ Number of Backups: ' + CONVERT(NVARCHAR, @Count)

SELECT TOP 1
        @name = subdirectory
FROM    #delList
WHERE   LEN(subdirectory) = 12
        AND RIGHT(subdirectory, 4) = '.ZIP'
        AND REPLACE(subdirectory, '.ZIP', '') < @OlderDateName

WHILE ( @@ROWCOUNT = 1 ) 
    BEGIN
        PRINT N'++ Delete Older Backup: ' + @name
        SET @cmd = 'del /f /q ' + @CFG_BACKUP_PATH + '\' + @name
        EXEC xp_cmdshell @cmd, no_output

        DELETE  #delList
        WHERE   subdirectory = @name

        SELECT TOP 1
                @name = subdirectory
        FROM    #delList
        WHERE   LEN(subdirectory) = 12
                AND RIGHT(subdirectory, 4) = '.ZIP'
                AND REPLACE(subdirectory, '.ZIP', '') < @OlderDateName
    END

DROP TABLE #delList

PRINT N'++ Done.'
PRINT ''
PRINT ''
PRINT ''

答案4

基于UndertheFold 的帖子我谷歌了一下，找到了 ExpressMaint 的详细信息。我之前从未见过，所以我很高兴找到了它。

仅供参考，该网页是，http://expressmaint.codeplex.com/

然后，我使用找到的一个示例来创建这个批处理文件，并计划每天晚上运行该文件。

c:\expressmaint\expressmaint -S (local)\SQLExpress -D ALL_USER -T DB -R E:\backups\sqlexpress\backupreports -RU WEEKS -RV 1 -B E:\backups\sqlexpress -BU DAYS -BV 4 -V -C

这将进行备份，并将每个备份 ( -BU) 保留四天，这样如果有损坏，您就可以获得历史记录。日志 ( -RU) 保留一周。

我只用了几个星期，但对它非常满意，因为它是一种无需干预的方法。我在本地将备份放在第二个磁盘上，然后使用丛林磁盘进行异地备份亚马逊 EC2云储存。

相关内容