database SQL Server 自动备份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/493886/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Automated Backups
提问by blu
What are the recommendations of software products for creating automated backups of SQL Server 2008 databases?
对创建 SQL Server 2008 数据库自动备份的软件产品有哪些建议？
The backup should happen without taking the database offline/detatching.
备份应该在不使数据库脱机/分离的情况下发生。
采纳答案by cmsjr
I would recommend just creating a maintenance plan in SQL Server to handle the backups, it can be configured to backup to a specified location at specified times, without taking the databases offline, and will handle your incremental backup cleanup.
我建议只在 SQL Server 中创建一个维护计划来处理备份，它可以配置为在指定时间备份到指定位置，而无需使数据库脱机，并将处理您的增量备份清理。
回答by Eduardo Molteni
If you are using SQL Server Express, you won't find a UI to run periodic backups.
In this case you have to run a batch using Windows Scheduled Tasks or something similar.
如果您使用SQL Server Express，您将找不到运行定期备份的 UI。
在这种情况下，您必须使用 Windows 计划任务或类似的东西运行批处理。
Don't forget to use a user with enough privileges to access SQL Server.
不要忘记使用具有足够权限的用户来访问 SQL Server。
In the batch file
在批处理文件中
"C:\Program Files\Microsoft SQL Server0\Tools\Binn\SQLCMD.EXE" -S
(local)\SQLExpress -i D:\dbbackups\SQLExpressBackups.sql
In 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
回答by akc42
I struggled with this for a while because it wasn't obvious how to work a regime that produced files with different names so that one run didn't over write the other. In the end it created the following Windows batch file
我为此苦苦挣扎了一段时间，因为不清楚如何使用产生具有不同名称的文件的机制，以便一次运行不会覆盖另一个。最后它创建了以下 Windows 批处理文件
:: Daily Backup of SQLSERVER databases
:: AKC 30 Apr 2011
::
:: Set environment variables
SET SQLCMDPASSWORD=xxxxxx
SET BACKUPDIR=C:\backups\db\
SET SCRIPTDIR=D:\Public\DB\batch_scripts\
:: Issue backup commands from a sql script
SQLCMD -U a_backup -S SERVER\SQLEXPRESS -i %SCRIPTDIR%daily_backup.sql
:: Tidy Up Old Backup Files (keep for 5 days)
FORFILES /P %BACKUPDIR% /S /M "*.bak" /D -5 /C "cmd /c del @path"
where a_backup is my sqlserver login with backup privileges. The corresponding sql is
其中 a_backup 是我具有备份权限的 sqlserver 登录名。对应的sql是
DECLARE @thistime nvarchar(25);
DECLARE @filename nvarchar(255);
SET @thistime = CONVERT(nvarchar,GETDATE(),126);
SET @filename = "$(BACKUPDIR)" + N'PASL' + SUBSTRING(@thistime,1,10) + N'_DB.bak';
BACKUP DATABASE DB_live
TO DISK = @FILENAME
WITH INIT;
GO
The discovery of "FORFILES" command to purge older files was the key finding for me.
发现清除旧文件的“FORFILES”命令是我的主要发现。
The transaction logs equivalents are
事务日志等价物是
:: Transaction Log Backups of SQLSERVER databases
:: AKC 30 Apr 2011
:: Run at reasonably spread out times of the day
:: Set environment variables
SET SQLCMDPASSWORD=xxxxxx
SET BACKUPDIR=C:\backups\db\
SET SCRIPTDIR=D:\Public\DB\batch_scripts\
:: Issue backup commands from a sql script
SQLCMD -U a_backup -S SERVER\SQLEXPRESS -i %SCRIPTDIR%tlog_backup.sql
with sql file
带有 sql 文件
DECLARE @thistime nvarchar(25);
DECLARE @filename nvarchar(255);
SET @thistime = CONVERT(nvarchar,GETDATE(),126);
SET @filename = "$(BACKUPDIR)" + N'PASL' + SUBSTRING(@thistime,1,10) + SUBSTRING(@thistime,11,3) + N'_LOG.bak';
BACKUP LOG DB_live
TO DISK = @FILENAME
WITH INIT;
GO
I should note that the database files are on my D: drive, which is why I took the backups onto the C: drive.
我应该注意到数据库文件在我的 D: 驱动器上，这就是我将备份放在 C: 驱动器上的原因。
The Daily backup is entered as a job in the Windows Task Scheduler to run daily at 4:00am. The transaction log backup is set to run daily at 8:00am with a repeat every 4 hours finishing after 13 hours (causing it to run at 8am Midday, 4pm and 8pm every day)
每日备份在 Windows 任务计划程序中作为作业输入，每天凌晨 4:00 运行。事务日志备份设置为每天上午 8:00 运行，13 小时后每 4 小时重复一次（使其在每天上午 8 点、下午 4 点和晚上 8 点运行）
