Backup all DBs of a SQL-Server Instance
The script allows to backup all databases if a SQL-Server Instance. It determine the backup-path by the environment variable DBBackupPath of the operating system. It also determines the SQL-Server instance on which it runs and uses the corresponding path.
1.) Create a Backup-Folder on the Server. The last part of the path must be "Backup"
(e.g. f:\MSSQL2005\Backup)
1a.) If there is a SQL-Server instance installed on the server the instancename must be previous before "Backup" (e.g. f:\MSSQL2005\Instance_Name\Backup
2.) Set the environment variable "DBBackupPath" to this folder
3.) Run the script by scheduler (ISQL/OSQL) or SQL-Query
declare @BackupPath nvarchar(512),
@DB nvarchar(512),
@Status Int,
@SQLCommand nvarchar(1024),
@szVersion char(1),
@EnvVarName nvarchar(256),
@ServerName nvarchar(64),
@InstanceName nvarchar(64)
-- For version 9 on free up xp_cmdshell
select @szVersion = Substring (@@version, CharIndex ('-',@@version) + 2,1)
if @szVersion > '8'
begin
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'xp_cmdshell', 1
reconfigure
end
-- Get the environment variable DBBackupPath
-- The last part should be 'Backup'
set @EnvVarName = 'DBBackupPath'
set noCount on
create table #EnvirnmentVariables (output varchar(1000) NULL)
insert #EnvirnmentVariables exec master..xp_cmdshell 'set'
Select @BackupPath = Substring (output, CharIndex ('=',output) + 1, len(output) - NULLIF(CharIndex ('=',output),-1))
from #EnvirnmentVariables where
upper (@EnvVarName) = upper (Substring (output,1, NULLIF(CharIndex ('=',output)-1,-1)))
drop table #EnvirnmentVariables
select @InstanceName = ''
-- no instances in version 7 and below
if @szVersion > '7'
begin
create table #SQLServers (name varchar(128) NULL, network_name varchar(28) NULL, status varchar(100) NULL, id varchar(4) NULL, collation_name varchar(128) NULL, connect_timeout varchar(15) NULL, query_timeout varchar(13) NULL)
insert #SQLServers exec master..sp_helpserver
Select @InstanceName = upper (Substring (name,NULLIF(CharIndex ('\',name),-1),128))
from #SQLServers where id=0
drop table #SQLServers
if NULLIF(CharIndex ('\',@InstanceName),-1) = 0 select @InstanceName = ''
end
set noCount off
if @szVersion > '8'
begin
exec sp_configure 'xp_cmdshell', 0
reconfigure
end
select @BackupPath = Substring(@BackupPath,1,Len(@BackupPath)-7) + @InstanceName + N'\Backup'
print 'Backup Path:'
print @BackupPath
print 'SQL-Server Major Version:'
print @szVersion
print 'SQL-Server Instance:'
print @InstanceName
-- DB-backup...
DECLARE DBCursor CURSOR FAST_FORWARD FOR
SELECT name, status FROM master..sysdatabases
OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @DB, @Status
WHILE @@FETCH_STATUS <> -1
BEGIN
IF @DB <> 'distribution' and
@DB <> 'tempdb' and
@DB <> 'Northwind' and
@DB <> 'AdventureWorks' and
@DB <> 'pubs'
begin
print 'Backing up database: ' + @DB
IF @Status & 8 = 0
begin
print 'Log...'
SELECT @SQLCommand = N'Backup log [' + @DB + '] to disk = N' + char(39) + @BackupPath + char(92) + @DB + 'DumpLog.bak' + char(39) + ' with init'
EXEC (@SQLCommand)
end
print 'Data...'
SELECT @SQLCommand = N'Backup database [' + @DB + '] to disk = N' + char(39) + @BackupPath + char(92) + @DB + 'DumpDat.bak' + char(39) + ' with init'
EXEC (@SQLCommand)
end
FETCH NEXT FROM DBCursor INTO @DB, @Status
END
DEALLOCATE DBCursor 
