1

I am using SQL Server 2008 (Express Edition). I want to create a job which will delete all data from the all the table(>50) in the DB everyday at night 1:00. Instead of Deleteting i decide to restore the DB from the Script.

It would have been easy by using SQL Server Agent, But this is limitation in SQL Server Express.

I figured out that we can create jobs "manually" by creating batch files and SQL script files, and running them via Windows Task Scheduler.

I have no clue what i have to write in bat file and sql file.This is my first time where i am working so deeply in SQL configuration. Can someone help please?

Name of the script which i need to restore is test.sql.

If any one has different approach , please share.

Thanks Prat

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
GameBuilder
  • 1,169
  • 4
  • 31
  • 62
  • 1
    Possible duplicate of [Executing a stored procedure using Windows task Scheduler](http://stackoverflow.com/questions/14321023/executing-a-stored-procedure-using-windows-task-scheduler) – Lukasz Szozda Dec 10 '15 at 15:57
  • First write code, check with `sqlcmd.exe` and if it works, schedule it with Task Scheduler. Another way is to use PowerShell, create scheduled task and use SMO to interact with database – Lukasz Szozda Dec 10 '15 at 15:58
  • Why is this tagged for MySQL? – Richard St-Cyr Dec 10 '15 at 16:01
  • @RichardSt-Cyr http://meta.stackoverflow.com/questions/304143/can-we-stop-suggesting-both-sql-server-and-mysql-in-the-suggested-tags – Lukasz Szozda Dec 10 '15 at 16:03
  • 1
    @lad2025: Thanks for the info – Richard St-Cyr Dec 10 '15 at 16:06
  • @lad2025 : I am sure my question is duplicate one. I am trying to figure out the answer which will help me to understand things very easily. I dont have time to read lot of thing for the moment. This is my first and last time i am going to do this job. With the help of the answer posted by you experts helped me to know lot of things. Tat is why i conclude that i have to write batch and sql to make this job. But i dont know what exactly i have to write in that file. If you can please help.Thanks – GameBuilder Dec 10 '15 at 16:08

1 Answers1

3

Your batch files needs to look like this. Change the path to your .sql file and also put in the sql server info. You can read more about sqlcmd HERE. Also note the case on the switches -S and -i as it does matter.

sqlcmd -S <ComputerName>\<InstanceName> -i C:\test.sql
SQLChao
  • 7,709
  • 1
  • 17
  • 32