Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
how to automate the backup of database in sql server express

Proposed Answer how to automate the backup of database in sql server express

  • Tuesday, November 20, 2012 11:56 AM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Tuesday, November 20, 2012 12:14 PM
     
     
    Sign In to Vote
    2
    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 1:42 PM
     
     
    Sign In to Vote
    1
    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.

     
  • Wednesday, November 21, 2012 8:32 AM
     
     Proposed Answer
    Sign In to Vote
    0

    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:53 AM
     
     
    Sign In to Vote
    0
    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 28, 2012 1:31 AM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    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


     
  • Thursday, November 29, 2012 7:30 AM
     
     
    Sign In to Vote
    0

    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:59 AM
     
     
    Sign In to Vote
    0

    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 8:12 AM
     
     
    Sign In to Vote
    0
    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 )

     
  • Friday, November 30, 2012 6:15 AM
    Moderator
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    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