Backup All Databases

,

This script will back up all databases (other than those listed to not be included) to a given disk location. It can also be modified to back up transaction logs, or to use a dump device instead of a disk location.

declare @IDENT INT, @sql varchar(1000), @DBNAME VARCHAR(200)

select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
while @IDENT is not null
begin
	SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT
/*Change disk location here as required*/
	SELECT @SQL = 'BACKUP DATABASE '+@DBNAME+' TO DISK = ''I:\MSSQL\BACKUP\'+@DBNAME+'.BAK''WITH INIT'
PRINT @SQL
	EXEC (@SQL)
	select @IDENT=min(DBID) from SYSDATABASES WHERE [DBID] > 0 and DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
end

Rate

4 (1)

Share

Share

Rate

4 (1)