SQL 2005 express - Automatic backups

  • 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

  • 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

  • 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

  • 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.

  • 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