none
how to automate the backup of database in sql server express

    Question

  • We have a server in which we have installed Sql Server express .

    and want to automate the backup of databases.

    because in sql server express there is no maintenance plan.

    how i can do it

    Tuesday, November 20, 2012 11:56 AM

All replies

  • Automating Database maintenance in SQL 2005 Express Edition Part I
    http://www.sqldbatips.com/showarticle.asp?ID=27

    Automating Database maintenance in SQL 2005 Express Edition Part II
    http://www.sqldbatips.com/showarticle.asp?ID=29

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Tuesday, November 20, 2012 12:14 PM
  • How to schedule and automate backups of SQL Server databases in SQL Server Express
    http://support.microsoft.com/kb/2019698

    Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Tuesday, November 20, 2012 1:42 PM
  • I am trying after make SP in master database .

    place this command in notepad and make a batch file. and try to run in command prompt then want to check it is creating at D drive or not.

    but it is showing this message command prompt.

    • Proposed as answer by vr.babu Thursday, November 29, 2012 12:42 PM
    Wednesday, November 21, 2012 8:32 AM
  • Should be -E (Windows Authentication)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Wednesday, November 21, 2012 8:53 AM
  • Hi John,

    Have you tried the following codes?

    sqlcmd –E –S .\EXPRESS –Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='F'"

    If there is any new findings, please let us know. For more detail information about sqlcmd Utility, please refer to the following link:

    sqlcmd Utility:
    http://msdn.microsoft.com/en-us/library/ms162773.aspx



    Allen Li
    TechNet Community Support


    Wednesday, November 28, 2012 1:31 AM
  • I make the strored procedure in master database.

    and then paste this sqlcmd command in txt file and make batch file  .

    first try to run this by double clicking this batch file . but is not taking backup in D:\sqlbackups.

    tell me what i need to do.

    Thursday, November 29, 2012 7:30 AM
  • John

    Do you get the error?

    Have you went thru the article I posted above? That is explained in details how to do that...


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Thursday, November 29, 2012 7:59 AM
  • You can use an utility, called SQLBackupAndFTP, read the following post for more information: http://blog.sqlauthority.com/2012/11/26/sql-server-sends-backups-to-a-network-folder-ftp-server-dropbox-google-drive-or-amazon-s3/

    SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

    Thursday, November 29, 2012 8:12 AM
  • Hi, first, please run the following command with SQL Server Management Studio and check whether the stored procedure works

    EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='F'

    If it works, then please try the following command with “Command Prompt” rather than double clicking the BAT file.

    sqlcmd –E –S .\EXPRESS –Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='F'"

    Additionally, if sp_BackupDatabases doesn’t belongs to master database, please include its database name with command, for example:

    sqlcmd –E –S .\EXPRESS –Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='F'" –d DBNameHere




    Allen Li
    TechNet Community Support

    Friday, November 30, 2012 6:15 AM