Backing up Database using SSMS 2012 Express

N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
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?

Comments

  • aaronchristensonaaronchristenson Member Posts: 261 ■■■■□□□□□□
    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.
    Aaron
    MCSE Cloud Platform and Infrastructure, MCSA Windows Server 2012, MCSA SQL Server 2012/2014, MCSA Windows 10, MCITP Server Admin, Security+, Virtualization with Windows Server Hyper-V and System Center Specialist
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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!
  • NotHackingYouNotHackingYou Member Posts: 1,460 ■■■■■■■■□□
    Yes, he puts all the logic for the backup into the SP and his batch script needs only to call the SP from there.
    When you go the extra mile, there's no traffic.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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!
  • aaronchristensonaaronchristenson Member Posts: 261 ■■■■□□□□□□
    I tried to paste some of the code from the stored procedure here but this interface does not like it.
    Aaron
    MCSE Cloud Platform and Infrastructure, MCSA Windows Server 2012, MCSA SQL Server 2012/2014, MCSA Windows 10, MCITP Server Admin, Security+, Virtualization with Windows Server Hyper-V and System Center Specialist
Sign In or Register to comment.