Batch to backup database

Question

User66371569 posted
Hi i have sql server express and mangament studio

i want simple batch to backup my database

i want name comes like this
Mm-09-03-2019
and want my batch stored in D:\ driver

How can i do that

Answer 1

User475983607 posted

Hi i have sql server express and mangament studio

i want simple batch to backup my database

i want name comes like this
Mm-09-03-2019
and want my batch stored in D:\ driver

How can i do that

Pretty simple.  Use osql or cmdsql to build a command line .bat file.  Then schedule the task to run through Windows Task scheduler or the SQL agent.  

https://docs.microsoft.com/en-us/sql/tools/osql-utility?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017

Simply lookup bat command for naming files and working with dates.  There are tons of online resources.

Answer 2

User-893317190 posted

Hi thepast,

If you want to back up your database using ssms,you could right click the database in your ssms -> choose tasks -> choose generate scripts .

Then in the  pop-up wizard, choose Choose Objects, click next , then you could choose where to export the script.

You could also specify the vision of your  script (whether sqlserver2008 ,sqlserver2017...)

In the save script wizard, please click  Advanced , choose script for server version , and then choose your remove server's version.

Or you could choose to  backup when choosing task.

https://support.clio.com/hc/en-us/articles/360000635193-How-to-Back-Up-an-SQL-database-using-Microsoft-SQL-Server-Management-Studio-Express

Best regards,

Ackerly Xu

Answer 3

User66371569 posted

not through ssms    I want it   batch file to schedule it   automatic

Answer 4

User-893317190 posted

Hi thepast,

I'm sorry , I misunderstood what you mean.

Just as mgebhard  has said , you should use .bat and windows task schedule.

For how to use taskschedule, you could refer to

https://www.digitalcitizen.life/how-create-task-basic-task-wizard

About how to write the backup t-sql, please refer to

https://database.guide/how-to-backup-a-sql-server-database-using-t-sql/

You could also refer to the link below 

https://stackoverflow.com/questions/7201061/how-to-create-jobs-in-sql-server-express-edition

Best regards,

Ackerly Xu