Agent Jobs

Backup All Databases To Share Location

Here’s a quick script that will backup all databases to a shared location on the network. The database backup files will be titled like this: MyDatabaseName.bak There is some extra logic to replace apostrophe’s in the database name in case you have any. I don’t know why you would have them, but I just found a box that does, so I included that in there. Additionally; this will not backup the system databases. Only user databases are backed up.

use master;
set nocount on
declare @backup_all_databases varchar(max)
set @backup_all_databases = ''
select @backup_all_databases = @backup_all_databases +
'backup database [' + name + '] to disk = ''\\MyShareName\' + replace(name, '', '''') + '.bak'' with format;' + char(10)
from sys.databases where name not in ('tempdb') 
order by name asc 
exec (@backup_all_databases)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s