SQL Server 2008 - 如何压缩备份文件并移动到远程服务器

I have the non-enterprise edition of SQL Server 2008. I do nightly backups, manually zip the files and then manually copy to a remote server.

我有非企业版的SQL Server 2008.我每晚进行备份,手动压缩文件,然后手动复制到远程服务器。

I need to automate this using batch files. Copying files from server to server is easy but how do I automate the zipping of the backup first?

我需要使用批处理文件自动执行此操作。将文件从服务器复制到服务器很简单,但如何首先自动完成备份的压缩?

The full process I need is:

我需要的全部流程是:

Run the backup nightly

每晚运行备份

Zip the backup to reduce size (with a unique zip filename)

压缩备份以减小大小(使用唯一的zip文件名)

Move the zip file to a remote server which is setup as a network drive on the database server

将zip文件移动到远程服务器,该服务器在数据库服务器上设置为网络驱动器

I confess the compression part has thrown me off. Any advice would be very much welcomed.

我承认压缩部分让我失望了。任何建议都会受到欢迎。

Thanks in advance.

提前致谢。

10 个解决方案

#1

You can backup databases with SQLBackupAndFTP software. It's a simple UI tool with ability to execute and schedule backup jobs (full, diff and log tran backups). It just compresses backups with embedded archiver or 7-zip and send to a local folder or to a NAS drive or FTP or a to a cloud (Dropbox, Google Drive, Amazon S3). Also there is support forum.

您可以使用SQLBackupAndFTP软件备份数据库。它是一个简单的UI工具,能够执行和安排备份作业(完整备份,差异备份和日志备份)。它只是使用嵌入式归档程序或7-zip压缩备份,并发送到本地文件夹或NAS驱动器或FTP或云(Dropbox,Google Drive,Amazon S3)。还有支持论坛。

#2

You could (and should!) most definitely investigate the SQL Server maintenance plans.

您可以(并且应该!)最明确地调查SQL Server维护计划。

These allow you to automate things like

这些允许您自动执行类似的操作

checking for database consistency

检查数据库一致性

rebuild indexes as needed

根据需要重建索引

do database and log backups (definitely use the SQL Server 2008 backup compression!!)

做数据库和日志备份(绝对使用SQL Server 2008备份压缩!!)

I'm not sure if they have built-in support for zipping and copying to a remote server, but you could definitely automate the backup part with a maintenance plan, and the rest with a command file of some sort.

我不确定他们是否内置支持压缩和复制到远程服务器,但你绝对可以使用维护计划自动化备份部分,其余部分使用某种命令文件。

#3

If you are just stuck on how to compress from a batch script:

如果您只是坚持如何从批处理脚本压缩:

Install 7-Zip
Run from the command line:
"C:\Program Files\7-Zip\7z.exe" a -t7z MyBackups.7z [Files To Zip]

从命令行运行:“C:\ Program Files \ 7-Zip \ 7z.exe”a -t7z MyBackups.7z [Files To Zip]

To get a unique filename, I usually embed the date/time: yyyymmddhhMMss-backup.7z

要获得唯一的文件名,我通常会嵌入日期/时间:yyyymmddhhMMss-backup.7z

#4

you do not specify the zip utility that you are using. There are many, but I tend to use Winzip as that is the main zip tool used at work. Winzip has a command line interface ( http://www.winzip.com/prodpagecl.htm ) that is a free addin to winzip that can be called from a command line.

您没有指定您正在使用的zip实用程序。有很多,但我倾向于使用Winzip,因为这是工作中使用的主要zip工具。 Winzip有一个命令行界面(http://www.winzip.com/prodpagecl.htm),它是winzip的免费插件,可以从命令行调用。

Another alternative would be to use cygwin and tar.gz via the command line.

另一种方法是通过命令行使用cygwin和tar.gz。

#5

You can ZIP stuff from the command line, for example with RAR. Just add the ZIP commands to wherever you do the copying. If that's in T-SQL, you can execute a ZIP command using xp_cmdshell.

您可以从命令行压缩填充内容,例如使用RAR。只需将ZIP命令添加到复制的任何位置即可。如果是在T-SQL中,则可以使用xp_cmdshell执行ZIP命令。

For a luxury option, check out Red Gate Backup, it makes this process fairly painless.

对于豪华选项,请查看红门备份,它使这个过程相当轻松。

#6

Since you've got 2008, you've got Powershell installed. I would suggest looking at a psh script executed after a successful backup to compress and copy over the wire. This would most likely be a second job step after your backup.

自从你有2008年以来,你已经安装了Powershell。我建议查看成功备份后执行的psh脚本,以压缩和复制电线。这很可能是备份后的第二个工作步骤。

You can also go old-school and write a batch file to do the compress and copy. Then invoke that as a cmdshell job step, again after your backup job/step.

您也可以去老派并编写批处理文件来进行压缩和复制。然后在备份作业/步骤之后再次将其作为cmdshell作业步骤调用。

#7

if you are a programmer you can make an application that get your db backup by SMO and zip this file to .gz file by available libraries.

如果您是程序员,则可以创建一个通过SMO获取数据库备份的应用程序,并通过可用库将此文件压缩为.gz文件。

#8

try this link: [http://www.sqlhub.com/2009/05/copy-files-with-sql-server-from-one.html][1]

试试这个链接:[http://www.sqlhub.com/2009/05/copy-files-with-sql-server-from-one.html][1]

in short:

1 - you must enable "Ole Automation Procedures"

1 - 您必须启用“Ole Automation Procedures”

2 - modify & run this script to test:

2 - 修改并运行此脚本以测试:

DECLARE @FsObjId        INTEGER
DECLARE @Source         VARCHAR(4096)
DECLARE @Destination    VARCHAR(4096)
SET @Source = 'C:\ritesh'
SET @Destination= 'D:\ritesh'
--creare OLE Automation instance
EXEC sp_OACreate 'Scripting.FileSystemObject', @FsObjId OUTPUT
--call method of OLE Automation
EXEC sp_OAMethod @FsObjId, 'CopyFolder', NULL, @Source, @Destination
--once you finish copy, destroy object
EXEC sp_OADestroy @FsObjId

3 - create a maintenance plan:

3 - 制定维护计划:

3.1 - add a "Back Up Database Task" and make sure to choose "Set backup compression" = Compress backup. (this will create your backups in a compressed format)

3.1 - 添加“备份数据库任务”并确保选择“设置备份压缩”=压缩备份。 (这将以压缩格式创建备份)

3.2 - add an "Execute T-SQL Statement Task" with the above script that'll move your files that'll execute after the 3.1 task :).

3.2 - 使用上面的脚本添加“执行T-SQL语句任务”,该脚本将移动您在3.1任务之后执行的文件:)。

#9

Try SQL Backup Master, which can zip backups and move them to a network (or local) folder. Can also move zipped backup files to FTP, Dropbox, Amazon S3, or Google Drive. Basic edition is free.

试试SQL Backup Master,它可以压缩备份并将它们移动到网络(或本地)文件夹。还可以将压缩的备份文件移动到FTP,Dropbox,Amazon S3或Google Drive。基础版是免费的。

#10

Zipping the file after backup takes significant time. Backup programs which use Virtual Device Interface of SQL Server solve your task and decrease overall process time. Try EMS SQL Backup which also allows sending compressed backups to network locations, FTP or clouds.

备份后压缩文件需要很长时间。使用SQL Server虚拟设备接口的备份程序可以解决您的任务并减少整个处理时间。尝试EMS SQL备份,它还允许将压缩备份发送到网络位置,FTP或云。

#1

#2