How to backup all databases from a MSSQL Server to a specific location

The below SQL will provide the backup database instructions needed. It is working on MSSQL 2000 – > 2008

Modify the @PATH variable as needed.

declare @PATH VARCHAR(1000)

SELECT @PATH='\\server_name\database_backup\'

DECLARE @SRVNAME VARCHAR(100)

SELECT @SRVNAME=COALESCe(@@SERVERNAME, 'server_not_defined')

select 'backup database ' + name + ' to disk=''' + @path + @SRVNAME + '_' + name + '.bak'' ' from master..sysdatabases where name not in ('master','tempdb','model','msdb','pubs','northwind')

Both comments and pings are currently closed.

Comments are closed.