如何使用 SQL Server Express 安排每日备份？

发布于 2024-07-13 01:00:01 字数 199 浏览 10 评论 0原文

我正在运行一个小型 Web 应用程序，以 SQL Server Express (2005) 作为后端。我可以使用 SQL 脚本创建备份，但是我想每天安排此操作。作为额外的选项（应该有），我想只保留最后的 X 个备份（显然是出于节省空间的原因）有什么指示吗？

[编辑] SQL Server 代理在 SQL Server Express 中不可用...

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谜兔 2024-07-20 01:00:01

Eduardo Molteni 给出了一个很好的答案：

SQL Server 自动备份

使用 Windows 计划任务：
在批处理文件中

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S 
(local)\SQLExpress -i D:\dbbackups\SQLExpressBackups.sql

在 SQLExpressBackups.sql

BACKUP DATABASE MyDataBase1 TO  DISK = N'D:\DBbackups\MyDataBase1.bak' 
WITH NOFORMAT, INIT,  NAME = N'MyDataBase1 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE MyDataBase2 TO  DISK = N'D:\DBbackups\MyDataBase2.bak' 
WITH NOFORMAT, INIT,  NAME = N'MyDataBase2 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

Eduardo Molteni had a great answer:

SQL Server Automated Backups

Using Windows Scheduled Tasks:
In the batch file

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S 
(local)\SQLExpress -i D:\dbbackups\SQLExpressBackups.sql

In SQLExpressBackups.sql

BACKUP DATABASE MyDataBase1 TO  DISK = N'D:\DBbackups\MyDataBase1.bak' 
WITH NOFORMAT, INIT,  NAME = N'MyDataBase1 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE MyDataBase2 TO  DISK = N'D:\DBbackups\MyDataBase2.bak' 
WITH NOFORMAT, INIT,  NAME = N'MyDataBase2 Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

回复收藏 0 原文

一抹苦笑 2024-07-20 01:00:01

您无法在 SQL Server Express 中使用 SQL Server 代理。
我之前完成的方法是创建一个 SQL 脚本，然后每天将其作为计划任务运行，您可以有多个计划任务来适应您的备份计划/保留。我在计划任务中使用的命令是：

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -i"c:\path\to\sqlbackupScript.sql"

回复收藏 0 原文

羁〃客ぐ 2024-07-20 01:00:01

只需使用此脚本即可动态备份服务器上的所有数据库。然后根据文章创建批处理文件。创建两个批处理文件很有用，一个用于完整备份，一个用于差异备份。然后在任务计划程序中创建两个任务，一项用于完整任务，一项用于差异任务。

-- // 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

批处理文件可以如下所示：

sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation='c:\Dropbox\backup\DB\', @backupType='F'"  >> c:\Dropbox\backup\DB\full.log 2>&1

这种方法的优点

sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation='c:\Dropbox\backup\DB\', @backupType='D'"  >> c:\Dropbox\backup\DB\diff.log 2>&1

是，如果添加新数据库或删除数据库，您不需要更改任何内容，甚至不需要在脚本中列出数据库。 JohnB 的回答对于具有一个数据库的服务器来说更好/更简单，这种方法更适合多数据库服务器。

Just use this script to dynamically backup all databases on the server. Then create batch file according to the article. It is usefull to create two batch files, one for full backup a and one for diff backup. Then Create two tasks in Task Scheduler, one for full and one for diff.

-- // 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

And batch file can look like this:

sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation='c:\Dropbox\backup\DB\', @backupType='F'"  >> c:\Dropbox\backup\DB\full.log 2>&1

and

sqlcmd -S localhost\myDB -Q "EXEC sp_BackupDatabases @backupLocation='c:\Dropbox\backup\DB\', @backupType='D'"  >> c:\Dropbox\backup\DB\diff.log 2>&1

The advantage of this method is that you don't need to change anything if you add new database or delete a database, you don't even need to list the databases in the script. Answer from JohnB is better/simpler for server with one database, this approach is more suitable for multi database servers.

回复收藏 0 原文

走野 2024-07-20 01:00:01

MSSQLTips 的人员有一些非常有用的文章，与此最相关的一篇是“自动化 SQL Server 2005 Express 备份和删除旧备份文件”

基本方法是使用 Windows 任务计划程序设置两个任务。其中一项任务运行一个 TSQL 脚本，为所有 MSSQL 数据库（TEMPDB 除外）生成单独的备份文件，并将数据库名称和文件名中的日期/时间戳保存到指定目录中。第二个任务运行一个 VBScript 脚本，该脚本遍历该目录并删除超过 3 天的所有扩展名为 .BAK 的文件。

这两个脚本都需要针对您的环境进行少量编辑（路径、保留这些数据库转储的时间），但非常接近即插即用。

请注意，如果您不小心使用这些文件或目录权限，则可能会产生安全隐患，因为它们是纯文本文件，需要以某种级别的权限运行。不要马虎。

回复收藏 0 原文

在巴黎塔顶看东京樱花 2024-07-20 01:00:01

我们使用了以下组合：

Cobian Backup 进行调度/维护
< a href="http://www.codeplex.com/ExpressMaint" rel="nofollow noreferrer">ExpressMaint 用于备份

这两个都是免费的。该过程是编写 ExpressMaint 脚本以将备份作为 Cobian“备份之前”事件。我通常让它覆盖以前的备份文件。然后，Cobian 从中取出 zip/7zip 并将其存档到备份文件夹中。在 Cobian 中，您可以指定要保留的完整副本的数量、进行多个备份周期等。

ExpressMaint 命令语法示例：

expressmaint -S HOST\SQLEXPRESS -D ALL_USER -T DB -R logpath -RU WEEKS -RV 1 -B backuppath -BU HOURS -BV 3

We have used the combination of:

Cobian Backup for scheduling/maintenance
ExpressMaint for backup

Both of these are free. The process is to script ExpressMaint to take a backup as a Cobian "before Backup" event. I usually let this overwrite the previous backup file. Cobian then takes a zip/7zip out of this and archives these to the backup folder. In Cobian you can specify the number of full copies to keep, make multiple backup cycles etc.

ExpressMaint command syntax example:

expressmaint -S HOST\SQLEXPRESS -D ALL_USER -T DB -R logpath -RU WEEKS -RV 1 -B backuppath -BU HOURS -BV 3

回复收藏 0 原文

倚栏听风 2024-07-20 01:00:01

您可以在服务器对象中创建备份设备，比方说

BDT测试

，然后创建一个包含以下命令的批处理文件，

sqlcmd -S 192.168.1.25 -E -Q "BACKUP DATABASE dbtest TO BDTEST"

让我们说出名称

备份.bat

然后你可以调用

备份.bat

根据您的方便在任务计划程序中

You can create a backup device in server object, let us say

BDTEST

and then create a batch file contain following command

sqlcmd -S 192.168.1.25 -E -Q "BACKUP DATABASE dbtest TO BDTEST"

let us say with name

backup.bat

then you can call

backup.bat

in task scheduler according to your convenience

回复收藏 0 原文

~没有更多了~

关于作者

阳光下慵懒的猫

暂无简介

0 文章

0 评论

25 人气

关注发私信

友情链接

文江博客

如何使用 SQL Server Express 安排每日备份？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（6）

Eduardo Molteni 给出了一个很好的答案：

Eduardo Molteni had a great answer:

关于作者

相关话题

热门标签

推荐作者

爱人如己

萧瑟寒风

云雾

倒带

浮世清欢

撩起发的微风

友情链接

如何使用 SQL Server Express 安排每日备份？

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（6）

Eduardo Molteni 给出了一个很好的答案：

Eduardo Molteni had a great answer:

关于作者

相关话题

热门标签

推荐作者

爱人如己

萧瑟寒风

云雾

倒带

浮世清欢

撩起发的微风

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。