Backup ALL your SQL Server 2005 databases using ONE script
I wanted to backup all my databases... I had loads, creating a step for each db was getting tedious, so I wrote this script.
DECLARE @DBName varchar(255) DECLARE @DATABASES_Fetch int DECLARE DATABASES_CURSOR CURSOR FOR select DATABASE_NAME = db_name(s_mf.database_id) from sys.master_files s_mf where -- ONLINE s_mf.state = 0 -- Only look at databases to which we have access and has_dbaccess(db_name(s_mf.database_id)) = 1 -- Not master, tempdb or model and db_name(s_mf.database_id) not in ('Master','tempdb','model') group by s_mf.database_id order by 1 OPEN DATABASES_CURSOR FETCH NEXT FROM DATABASES_CURSOR INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN declare @DBFileName varchar(256) set @DBFileName = datename(dw, getdate()) + ' - ' + replace(replace(@DBName,':','_'),'\','_') exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = N''c:\db backup\' + @DBFileName + ''' WITH NOFORMAT, INIT, NAME = N''' + @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100') FETCH NEXT FROM DATABASES_CURSOR INTO @DBName END CLOSE DATABASES_CURSOR DEALLOCATE DATABASES_CURSOR
Paul is the COO of kwiboo ltd and has more than 20 years IT consultancy experience. He has consulted for a number of blue chip companies and has been exposed to the folowing sectors: Utilities, Telecommunications, Insurance, Media, Investment Banking, Leisure, Legal, CRM, Pharmaceuticals, Interactive Gaming, Mobile Communications, Online Services.
Paul is the COO and co-founder of kwiboo (http://www.kwiboo.com/) and is also the creator of GeekZilla.