On a customers site we moved an application database from the central SQL server to the same server that also hosts this application back-end. The application vendor suggested this step due to performance issues we are experiencing during the data import process.
The customer did not have enough SQL licenses to support a second full SQL installation, so to make this work we have installed the free SQL 2005 Express version. This works fine and the application performance during the import process and during working hours has improved. A down side for using the free version of SQL server is that Database backups can’t be performed from the SQL management suite.
With some help from the all mighty Google I wrote this little SQL script to make the backup work anyway.
DECLARE @BackupDBFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50) DECLARE @FileName varchar(30), @MediaName varchar(30), @BackupDir nvarchar(200) SET @BackupDir = 'D:\Backuped_SQL_DB\' --Add a list of all databases not to be included in the backup DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> 'tempdb' AND name <> 'model' AND name <> 'Northwind' AND name <> 'master' AND name <> 'msdb' OPEN Database_Cursor FETCH next FROM Database_CURSOR INTO @DB WHILE @@fetch_status = 0 BEGIN SET @FileName = @DB + '( Daily BACKUP )' SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112) SET @BackupDBFile = @BackupDir + + @DB + '_' + 'Full' + '_' + CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak' SET @Description = 'Normal' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = 'master' BEGIN SET @BackupDBFile = @BackupDir + @DB + '_' + 'Full' + '_' + CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak' SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' END ELSE BEGIN SET @BackupDBFile = @BackupDir + @DB + '_' + 'Full' + '_' + CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak' --SET some more pretty stuff for sql server. SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.' END BACKUP DATABASE @DB TO DISK = @BackupDBFile WITH NAME = @FileName, DESCRIPTION = @Description , MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description , STATS = 10 FETCH next FROM Database_CURSOR INTO @DB END CLOSE Database_Cursor DEALLOCATE Database_Cursor
The next thing you need to do is schedule this script to make the backup on regular intervals. I used the following code to execute the SQL script with the Windows scheduler.
This works fine! The backup files are copied to the central database server so they are included in the full backup of the network. The next problem you’re facing is that the D drive of your server is filling up with backup files. To fix this problem I wrote a cleanup script to preserve only the two most recent backup files and delete the older files.
ECHO ONREM Delete SQL Backup files older than 2 days FORFILES /p D:Backuped_SQL_DB /s /m *.* /d -2 /c "CMD /C del /Q @FILE
Now we have an backup from the application database and we saved a little money, plus I had a lot off geeky fun creating these scripts..
Was once an enthusiastic PepperByte employee but is now working at Ivanti. His blogs are still valuable to us and we hope to you too.