Backing up Database using SSMS 2012 Express

N2IT

I was wondering for small business does it make sense to use SSMS 2012 Express, however it doesn't have agent job with the package.

So instead of using an agent job, using a batch file in task manager scheduler? What are you thoughts on this? The SQL to back up a database is cake, I just need to create the batch file and place it in the appropriate folder. Set the job to run in scheduler and point it to the SQL script.

This is the only work around I could come up with. Thoughts?

The SQL is correct I can manually execute it and it will save a BAK file. However I am having some issues with the batch file, pathetic lol

I am going to go long hang and point to the server/instance. This is with the SQLCMD

Any one do this before?

aaronchristenson

I do this with my Express Edition SQL Servers. This is an example for one server.

The batch files only have this one line

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupCardWizardDatabase"

I create a stored procedure in the master database that does the backup.

Then Windows Task Scheduler calls the batch script using my SQL Service account. The only thing it does not do is delete the old copies.

N2IT

If you run the script manually it seems to copy over the file, is this your experience when saving the file to the network drive?

As far as the SP goes, you just build you SP with the backup command in their and then exec you the SP?

I just want to make sure I am crystal clear. Thanks Aaron!

NotHackingYou

Yes, he puts all the logic for the backup into the SP and his batch script needs only to call the SP from there.

N2IT

This solution works I haven't had a problem with it not working since I implemented. Fairly easy once you go through the journey once.

Thanks again!

aaronchristenson

I tried to paste some of the code from the stored procedure here but this interface does not like it.