Microsoft SQL Server 2008 External Backup

Question

Hi,

I would like to save my backups from my SQL 2008 server to another server location. We have 2 servers:

• Deployment server
• File Server

The problem is that the deployment server doesn't have much space. And we keep 10 days backups of our databases. Therefore we need to store our backups on an external "file server". The problem is that SQL doesn't permit this. I've tried to run the SQL 2008 service with an account that has admin rights on both pc's (domain account), but this still doesn't work.

Any thoughts on this one. Otherwise we'll have to put an external harddisk on a rack server and that's kinda silly no?

Kind regards, Sem

EDIT:

I've found a way to make it work. You have to share the folder on the server. Then grant the Development Server (the PC itself) write permissions. This will make external backups possible with SQL server. Don't know if it's safe though, I find it kinda strange to give a computer rights on a folder.

Answer 1

Hello,

you might use a scheduler to move backups after a certain amount of time after the backup started with a batch file.

If I remember correctly there's a hack to enable the sql server to back up on remote storage, but I don't think a hack is the way to go.

Surely the best possibility may be to use an external backup tool which supports the use of agents. They control when the backup starts and take care of the files to move around.

Sascha

Answer 2

Hi,

You could create a nice and tidy little SQL Server Integration Services (SSIS) package to both carry out the backup and then move the data to your alternative file store.

Interestingly enough, the maintenance plans within SQL Server actually use SSIS components. These same components are available to use within the Business Intelligence Design Studio (BIDS).

I hope this is clear but let me know if you require further assistance.

Cheers, John

Answer 3

My experiences older versions of MSSQL, so there may be things in SQL2008 which help you better.

We are very tight on disk space on some of our old servers. These are machines at our ISP and their restore-from-tape lead time is not good - 24 hours is not uncommon :( so we need to keep a decent online backup history.

We take full backup on Sunday, differential backup each other night, and TLog backups every 10 minutes.

We force Tlog backups every minute during table/index defrag and statistics update - this is because these are the most TLog-intensive tasks that we run, and they were previously responsibly for determining the size of the standing Tlog file; since this change we've been able to run the TLog standing size about 60% smaller than before.

Worth watching the size of Diff backups though - if it turns out that by the Wednesday backup your DIFF backup is getting close to the size of the Full backup you might as well run a Full backup twice a week and have smaller Diff backups for the next day or two.

When you delete your Full or Diff backup files (to recover the disk space) make sure you delete the TLog backups that are earlier. But consider your recovery path - would you like to be able to restore last Sunday's Full backup and all Tlogs since, or are you happy that for moment-in-time restore you can only go back as far as last night's DIFF backup? (i.e. to go back further you can only restore to FULL / DIFF backup, and not to point-in-time) If the later you can delete all earlier Tlog backups as soon as you have made the DIFF backup.

(Obviously, regardless of that, you need to get the backups on to tape etc. and to your copy-server, you just don't have to be dependant on tape recovery to make your Restore, but you are losing granularity of restore with time)

We may recover the last Full (or the Last Full plus Monday's DIFF) to a "temporary database" to check out something, and then drop that DB, but if we really REALLY had to restore to "last Monday 15-minutes-past-10 AM" we would live with having to get backups back from tape or off the copy-server.

All our backup files are in an NTFS directory with Compress set. (You can probably make compressed backups direct from SQL2008??, the servers we have which are disk-starved are running SQL2000). I have read that this is frowned upon, but never seen good reasoning why, and we've never had a problem with it - touch wood!

Answer 4

You can use 3rd party tools like SqlBackupAndFTP

Answer 5

There are serveral ways of doing this already described, but this one is based on my open source project, SQL Server Compressed Backup. It is a command line tool for backing up SQL Server databases, and it can write to anywhere the NT User running it can write to. Just schedule it in Scheduled Tasks running with a user with appropriate permissions.

An example of backing up to a share on another server would be:

msbp.exe backup "db(database=model)" "gzip" "local(path=\\server\share\path\model.full.bak.gz)"

All the BACKUP command options that make sense for backing up to files are available: log, differential, copy_only, checksum, and more (tape drive options are not available for instance).