SQL Express – Backup Plan

SQL express doesn’t have the luxury of SQL maintenance plans, but you can still write a SQL script to dump a database to a particular location and run it via task scheduler on a daily basis.

Create SQL query and save it to a particular location… (NightlyBackup.sql)

DECLARE @pathName NVARCHAR(512)

SET @pathName = ‘D:\SQL Backup\database_‘ + Convert(varchar(8), GETDATE(), 112) + ‘.bak’

BACKUP DATABASE [database] TO DISK = @pathName WITH NOFORMAT, NOINIT, NAME = N’db_backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

Create cmd / bat and save to similar location to above… (Backup DB to disk.cmd)

sqlcmd -S servername\INSTANCENAME -U sqluser -P sqluserpassword -i “NightlyBackup.sql”

forfiles /p “C:\SQLBackupLocation” /m *.bak /s /d -2 /c “cmd /c del @file : date >= 7 days >NUL”

Create windows task schedule event to run above at 5:30pm everyday. 

114 Replies to “SQL Express – Backup Plan”

  1. My husband and i have been absolutely peaceful that Jordan could round up his reports through your precious recommendations he got using your web pages. It is now and again perplexing just to continually be offering tricks that a number of people have been trying to sell. Therefore we do understand we have got you to appreciate for that. The main illustrations you have made, the straightforward site navigation, the relationships you assist to instill – it is all unbelievable, and it’s helping our son and our family imagine that the situation is brilliant, which is certainly very serious. Many thanks for all the pieces!

  2. I like the valuable information you provide in your articles. I will bookmark your weblog and check again here regularly. I am quite sure I will learn plenty of new stuff right here! Good luck for the next!

  3. I have been exploring for a little bit for any high quality articles or blog posts in this kind of house . Exploring in Yahoo I at last stumbled upon this web site. Studying this info So i¡¦m happy to show that I have a very good uncanny feeling I found out just what I needed. I so much indubitably will make sure to don¡¦t disregard this website and give it a look on a relentless basis.

  4. Thanks for some other informative website. Where else may just I get that type of information written in such an ideal approach? I have a venture that I am just now running on, and I have been on the look out for such information.

  5. I do not even know how I ended up here, but I thought this post was good. I do not know who you are but certainly you are going to a famous blogger if you are not already ;) Cheers!

  6. I and my guys were actually checking out the excellent ideas on your web site and then suddenly I got a horrible suspicion I never expressed respect to the website owner for those tips. Those young boys came totally glad to see them and have in effect truly been taking advantage of them. Appreciation for genuinely well helpful and for obtaining this sort of remarkable subjects most people are really wanting to be aware of. Our own sincere apologies for not expressing appreciation to earlier.

Leave a Reply

Your email address will not be published.