The script below provides a very simple procedure to backup all databases except for the specified ones on a given SQL server instance. By design it is built with the idea of a simple recovery model been used. You can wrap it up as a procedure and execute it on SQL Express. In databases using the simple recovery model, you may restore full or differential backups only. It is not possible to restore a database backed up with simple recovery mode to a given point in time, you may only restore it to the exact time when a full or differential backup occurred. So if that is an acceptable recovery model then this script will be useful. When it executes it will create a full backup of the databases overriding any given files of the same name. The retention variable allows a simple but very effective method for defining how many backups to keep. The default Retention given in the script is day of week allowing retention of 7 days.
DECLARE @Database varchar(128)
DECLARE @cmd nvarchar(1024)
DECLARE @BackupLocation varchar(128)
DECLARE @RetentionVariable varchar(32)
-- backup path eg c:\backups\
Select @BackupLocation = 'C:\'
-- the Retention Variable to use default to day of week ie keeping 7 days backups in the backup location
Select @RetentionVariable = DATENAME(dw, getdate())
--Select @RetentionVariable = DATENAME(d, getdate()) -- to keep 1-31 backups depending on the month
--Select @RetentionVariable = DATENAME(dy, getdate()) -- to keep 364-365 backups depending on the year
--Select @RetentionVariable = "" -- to keep only a single backup
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('model','tempdb','distrbution') -- <-- these are the databases not to backup.
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'BACKUP DATABASE ' + @Database + '
TO DISK = N'''+@BackupLocation +'' + @Database + '-' + @RetentionVariable + '.BAK'' WITH NOFORMAT, INIT,
NAME = N''' + @Database + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
--print @cmd
EXEC (@cmd)
SET @cmd = 'declare @backupSetId as int
select @backupSetId = position
from msdb..backupset
where database_name=N'''+ @Database +'''
and backup_set_id=(select max(backup_set_id)
from msdb..backupset
where database_name=N'''+ @Database+ ''')
if @backupSetId is null
begin
raiserror(N''Verify failed. Backup information for database '''''+@Database+''''' not found.'', 16, 1)
end
RESTORE VERIFYONLY
FROM DISK = N''' + @BackupLocation + @Database+ '-' + @RetentionVariable + '.BAK''
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND'
-- print @cmd
EXEC (@cmd)
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor