December 12, 2011 at 12:25 pm
I need to do a full automatic DB backup of a database on SQL 2005 express server.
What I am looking for is maybe a scheduled task that would execute daily. The script should backup the database to a local drive and give it a name with a date stamp. The next day a new backup with current date stamp (separate backup file). The 3rd day, the backup from 2 days is overwritten. This way you would always have 2 days of backups.
Thank you for your help
December 12, 2011 at 12:38 pm
Since SQL Express doesn't have the SQL Agent service for scheduling jobs, you'll either need to build something that can use the Windows task scheduler (perhaps through the command line, perhaps through a .NET DLL), or you'll need to buy a third-part database backup tool.
I did a quick search for backup software for SQL Express, and found a few pretty easily.
Personally, I'd probably build something by way of a VB.NET DLL and schedule it in the Windows scheduler. I've never done that, but it can't be too hard. Just issuing a few file system commands (dead easy in VB.NET) and then establishing an SQL Server connection and issuing backup commands (again, pretty darn easy). Is that something you'd be comfortable doing? If not, then third-party is going to be cheaper than hiring someone to build something custom for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 12, 2011 at 12:51 pm
In such case i'm using sqlcmd
Create a .bat file like this
sqlcmd -U username -P password -S server -i .\backup.sql
in the same folder save the following as backup.sql
DECLARE @path AS VARCHAR(50),
@FILENAME AS VARCHAR(20)
SET @FILENAME = (SELECT CASE DAY(GETDATE())%2
WHEN 0 THEN 'ODD' --filename for Odd days
ELSE 'EVEN' --filename for Even days
END)
SET @path = 'c:\'+@FILENAME+'.bak' -- your Path
BACKUP DATABASE TEST TO DISK = @path WITH INIT
and run the batch file
done
December 12, 2011 at 1:01 pm
Another way to do this- if you have another SQL Server which is not Express edition, you can schedule maintenance jobs to run from that server.
December 12, 2011 at 2:55 pm
Thanks all for the help..
I Created two folders: "ztest\1_day_ago" and "ztest\2_day_ago". I then have this batch file:
DEL C:\ztest\2_Days_Ago\*.* /q
MOVE C:\ztest\1_Day_Ago\*.* ztest\2_Days_Ago
MOVE C:\ztest\*.* C:\ztest\1_Day_Ago
sqlcmd -S myPC\SQLEXPRESS -i .\backup.sql
The SQL query "backup.sql" looks like this:
BACKUP DATABASE myDB TO DISK = 'C:\ztest\DB.bak' WITH INIT
The batch file will be triggered by a windows schedule to run daily.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply