Automatically execute mssql query?

Question

I'm trying to make a way to do automatic backups. Since I'm using SQL Server 2008 Express I (apparently) can't use the agent. So I've found a sql script to do the backup. I'm using a bat file linked with windows task scheduler to launch the script every day. Here's the bat:

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S (local)\SQLExpress -i C:\DBbackups\SQLExpressBackups.sql

sql:

BACKUP DATABASE MuOnline TO  DISK = N'C:\DBbackups\Online' 
WITH NOFORMAT, INIT,  NAME = N'Online Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE Ranking TO  DISK = N'C:\DBbackups\Ranking.bak' 
WITH NOFORMAT, INIT,  NAME = N'Ranking Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE MU2003_EVENT_DATA TO  DISK = N'C:\DBbackups\2003_EVENT_DATA.bak' 
WITH NOFORMAT, INIT,  NAME = N'2003_EVENT_DATA', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

BACKUP DATABASE SCFMuTeam TO  DISK = N'C:\DBbackups\SCF.bak' 
WITH NOFORMAT, INIT,  NAME = N'SCF Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

The sql works fine and creates the backups but I have to execute it manually - so when the task scheduler runs the bat file it doesn't execute the sql - is this an error with the bat file or do i need to add to the sql?

Are there any errors or informational messages in the eventlog? — NickyvV
– NickyvV, Commented Jan 17, 2014 at 12:50
I recently did this myself. Make sure the account you're using for task scheduler has database backup rights on the sql server. — Matt Williamson
– Matt Williamson, Commented Jan 17, 2014 at 12:51
Edit - i think I'm not using the task scheduler correctly.. the batch wasn't working so i tried adding it like this i.imgur.com/cU5FTrb.png is this incorrect? — jn025
– jn025, Commented Jan 17, 2014 at 12:58
Use .cmd file instead of .bat (if you're not doing so already :)). In task scheduler, always specify startup working folder (same as cmd file location). In task scheduler, set credentials (your user account and password) and check "allow run with highest privileges" (or how exactly that checkbox is named). Should work. — Arvo
– Arvo, Commented Jan 17, 2014 at 13:35

vasin1987 · Accepted Answer · 2014-01-17 13:10:17Z

1

Your screen is not correct. Put SQLCMD.EXE into program to run and -S (local)\SQLExpress -i C:\DBbackups\SQLExpressBackups.sql as argument

http://technet.microsoft.com/en-us/library/ms162773.aspx

answered Jan 17, 2014 at 13:10

vasin1987

2,0321 gold badge21 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jn025 Over a year ago

Ok, it loads the bat now and i get the dos prompt - it flashes for a few seconds and then shows some text for a split second and closes. The sql doesn't seem to execute as the backups arn't there.

Collectives™ on Stack Overflow

Automatically execute mssql query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related