I finally managed to fully automate the backups for my sql server 2005 express edition databases. The product is free, and therefor great. But the limitations seem to be the jobs you can't add, and the scheduling that isn't available.
So, I made this small but handy Stored procedure, which queries all database names, loops through them, and creates a backup file for each of them:
CREATE PROCEDURE bakall AS /*Declare the variables that we will need in the stored proc */ Declare @count Int Declare @LoopCount Int Declare @dbname Varchar(50) Declare @bakpath Varchar(200) SELECT name into #temp FROM sys.databases where name not in ('master', 'tempdb', 'model', 'msdb') select Identity(int,1,1) id, [name] as dbname into #temp2 from #temp set @count = @@rowcount set @LoopCount = 1 while @LoopCount <= @count begin select @dbname = dbname from #temp2 where id = @LoopCount set @LoopCount = @LoopCount + 1 set @bakpath = 'E:\daily bak db\' + @dbname + '.bak' BACKUP DATABASE @dbname TO DISK = @bakpath WITH NOFORMAT, NOINIT, NAME = 'Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 end
Thanks to this blog for pointing out the looping over a recordset.
The real problem was the scheduling, since sql server doesn't seem to support it.
So, I created a coldfusion script to call the procedure in the db:
&lt;cfquery name="q" datasource="#application.dbs#"&gt;
exec bakall
&lt;/cfquery&gt;
Unfortunately, that didn't exactly work out. the user which is set for the datasource "#application.dbs#" only has rights in one database, so errors were thrown while trying to call BACKUP with opther databases.
So I went back to sql server management studio, and added "db_backupoperator" rights for each database on the server for the given user.
Then, finally, it worked :-)
After that, I made sure Cobian backup (which rocks!) is backing up the bak files to an external location, and now, i can finally relax!
Recent Comments