SQL Server 2008 - How to mount backup files and migrate to a remote server

Question

I have a corporate version of SQL Server 2008. I carry backups, manually zip files, and then manually copy to a remote server.

I need to automate this with batch files. Copying files from server to server is easy, but how do I automate backup archiving first?

The complete process I need is:

• Start backup at night
• Lock backup to reduce size (with unique zip file name)
• Move the zip file to a remote server that is configured as a network drive on the database server.

I admit that the compression part threw me away. Any advice would be greatly appreciated.

Thanks in advance.

Answer 1

You can back up databases using SQLBackupAndFTP . This is a simple user interface tool with the ability to execute and schedule backup tasks (full, diff and log tran backups). It simply compresses the backups with the built-in archiver or 7-zip and sends them to a local folder or to a NAS or FTP drive or to the cloud (Dropbox, Google Drive, Amazon S3). There is also a support forum .

Answer 2

You could (and should!) Research service plans most accurately.

This allows you to automate features such as

• ( SQL Server 2008!)

, , , - - .

Answer 3

, script:

• 7-Zip
• :
  "C:\Program Files\7-Zip\7z.exe" a -t7z MyBackups.7z [ Zip]

, /: yyyymmddhhMMss-backup.7z

Answer 4

zip, . , Winzip, zip, . Winzip (http://www.winzip.com/prodpagecl.htm), winzip, .

cygwin tar.gz .

Answer 5

ZIP , RAR. ZIP , . T-SQL, ZIP xp_cmdshell.

, Red Gate Backup, .

Answer 6

2008, ​​Powershell. psh script, . , .

. cmdshell, / .

Answer 7

, , db SMO .gz .

Answer 8

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

:

1 - " Ole"

2 - script, :

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

3.1 - " " , " " = . ( )

3.2 - " T-SQL Statement" script, , 3.1:).

Answer 9

SQL Backup Master, ( ) . FTP, Dropbox, Amazon S3 Google Drive. .

Answer 10

. , SQL Server, . EMS SQL Backup, , FTP .

Answer 11

Backup Assistant. .

You need to set a schedule for this program. (like every hour). Then you need to configure the paths of the backup files and the ftp credentials in Appsettings.json.

{   "AppSettings": {
    "BackupPaths": [
      {
        "LocalPath": "C:\\Users\\Sinan\\Desktop\\FtpBackup\\TestDb",
        "RemotePath": "TestDb"
      },
      {
        "LocalPath": "C:\\Users\\Sinan\\Desktop\\FtpBackup\\TestHangFireDb",
        "RemotePath": "TestHangFireDb"
      },
      {
        "LocalPath": "C:\\Users\\Sinan\\Desktop\\FtpBackup\\TestLogDb",
        "RemotePath": "TestLogDb"
      }
    ],
    "BackupFileExtensions": [ ".bak" ],
    "DeleteFilesAfterSend": true,
    "ZipFilesBeforeSend": true,
    "DeleteZipFilesAfterSend": false,
    "WriteLog": true,
    "Providers": {
      "FtpServer": {
        "Enabled": true,
        "Host": "ftphost",
        "Port": "21",
        "Username": "ftpusername",
        "Password": "ftppassword"
      }
    }   } }