dbrennecke
asked on
How to automatically compress backup files and move to a remote location
Is there a simple way to have my SQL 2005 backup files compressed and moved to a remote network location?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There is no simple way for SQL 2005.
In a command line script, you could use SQLCMD to backup the database and then WinZip http://www.winzip.com/index.htm or WinRAR http://www.rarlab.com/ to compress the file and move it to a network drive.
We use a backup compression tool built for SQL Server. There are several on the market such as:
o Idera's SQLsafe http://www.idera.com/Products/SQLsafe/
o Quest's SQL LiteSpeed http://www.quest.com/litespeed-for-sql-server/
o Red-Gate's SQL Backup
In a command line script, you could use SQLCMD to backup the database and then WinZip http://www.winzip.com/index.htm or WinRAR http://www.rarlab.com/ to compress the file and move it to a network drive.
We use a backup compression tool built for SQL Server. There are several on the market such as:
o Idera's SQLsafe http://www.idera.com/Products/SQLsafe/
o Quest's SQL LiteSpeed http://www.quest.com/litespeed-for-sql-server/
o Red-Gate's SQL Backup
ASKER
Taking a look at RedGate... does anyone have reliable and field tested experience with it?
I do, I love it.
Yes its a nice tool.
I've recently done exactly this to set up an automated backup job with compression.
Install 7-ZIP on the SQL server (freeware archive utility, heaps better than winzip and has a command line version as well as GUI).
I then set up a SQL Agent job that uses a management plan to back up all current databses to E:\MSSQL\BACKUP.
Then the next step in the job is to run a DOS batch file.
The batch file looks like this:
--------------------------
e:
cd E:\MSSQL\BACKUP\
del /q *.zip
for %%f in (*.bak) do "c:\program files\7-zip\7z.exe" a E:\MSSQL\BACKUP\%%f.zip %%f
del /q *.bak
--------------------------
Using 7-ZIP's command line (7z.exe) and the DOS "for" command, the fourth line above basically takes every *.BAK file in that directory, and zips it up - an individual .ZIP file for every .BAK file. It then deletes the .BAK files... but you could easily modify this to then move those ZIP files to another location - using copy, or FTP, whatever.
cheers
dave
Install 7-ZIP on the SQL server (freeware archive utility, heaps better than winzip and has a command line version as well as GUI).
I then set up a SQL Agent job that uses a management plan to back up all current databses to E:\MSSQL\BACKUP.
Then the next step in the job is to run a DOS batch file.
The batch file looks like this:
--------------------------
e:
cd E:\MSSQL\BACKUP\
del /q *.zip
for %%f in (*.bak) do "c:\program files\7-zip\7z.exe" a E:\MSSQL\BACKUP\%%f.zip %%f
del /q *.bak
--------------------------
Using 7-ZIP's command line (7z.exe) and the DOS "for" command, the fourth line above basically takes every *.BAK file in that directory, and zips it up - an individual .ZIP file for every .BAK file. It then deletes the .BAK files... but you could easily modify this to then move those ZIP files to another location - using copy, or FTP, whatever.
cheers
dave
however in sql server2008 you can use the compress command with the backup to compress the backup file upto 10% of the orignal size.
now back to your question
you can use third party tools to compress your backup file. then create a batch file and use it with windows scheduler to move the file over the network.