Rod at work (3/11/2014)
sreekanth bandarla (3/10/2014)
Rod at work (3/10/2014)
sreekanth bandarla (3/7/2014)
BTW, here is the KB article on how you can do that...sreekanth, I went to the link you provided. I've gotten the SQL script that's on the page, put it into its own .sql file and ran it in my SQL Express, putting the new SP into the master database. Then I created a .bat file, appropriate for my situation, and scheduled it into my Task Scheduler. I let it run at noon, my time. I then checked the status of the run; it ran fine. However, when I went to look to see if the .bak file was where I specified it should be, it wasn't. Is it because my recovery model is specified as Simple?
Nope! Recovery model has nothing to do with backups to fail in your case. Use the below code to create your batch file and let me know how this goes. It will create output file called backupjob_log.txt for your review.change your paths as needed. I tested this successfully just now on one of my Instances, ran just fine.
sqlcmd -S .\YourServer -E -Q "EXEC master..sp_BackupDatabases @backupLocation='C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\', @backupType='F'" -o "C:\Program Files\Microsoft SQL Server\MSSQL10.SREE2008\MSSQL\Backup\sch\Backupjob_Log.txt"
Here's what I've got in my batch file:
sqlcmd -U Superacct -P xxxx -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='C:\SQLBackups\', @backupType='F'"
In comparing your code and mine, the thing that looks different is you've specified "master..sp_BackupDatabases" whereas I've specified just "sp_BackupDatabases". Should I qualify that it start in the master DB?
Since I've deployed in master database on my test instance, I am using "master..proc". That's just my practice. Yes, You can give it a try. BTW, did you copied and pasted the sqlcmd command from the KB article or did you typed the sqlcmd command manually in a new file? The reason why am asking this is, There is a hidden char which is being copied over if we simply copy and paste the code from article!!! (Error Message: Sqlcmd: 'ûE': Unexpected argument). It's not visible in Notepad(but any hexeditor should expose it). So.....make sure you type this manually in a new text file and change the extension to cmd and give it a shot.