none
automatically taking backup of database on shared hosting having SQL Server 2005 express on my machine ? Plz guide

    Question

  • Hello,

    Pleaes guide me how I can take backup of SQL Server database on sared hosting ? I have SQL Server 2005 express on my machine. Please guide me is there some way it can be done automatically like a client can do it without not  being technical.

    thanks for helping me.

    haansi

    Thursday, April 15, 2010 6:49 AM

Answers

  • <<Cant I do it automatically by my self ?>>

    It depends if your hosting provider had granted you the various level of permissions (in SQL Server and at the OS level) that would be needed. A backup is performed by the BACKUP DATABASE command. This produces a file on the SQL Server machine. You would then need some permissions at OS level to FTP (or similar) that file to your local machine. If you want to automate that, then you need some software from which you do that. Perhaps some AT schedule on your local machine. If you want to automate this on the hosting end, you would need permissions at that end (for instance to create SQL Server Agent jobs), and then some means to get the file from that remote machine to your local machine (conaisering that this would execute on the remove machine).


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Haansi Thursday, April 15, 2010 10:44 AM
    Thursday, April 15, 2010 9:06 AM
    Moderator
  • <<Is it possible to execute backup database command using SQL Server Management Studio that I can take backup of hosting db on local machine>>

    Only if your local machine is accessible from the remote machine (using UNC name, as a network drive), which is highly unlikely. I.e., you really need to work with your hosting provider on this.

    Another option (instead of backup) is to use some tool which script out all table definitions, views, stored procedures etc and also generate INSERT commands for the data in all tables. There are tools on the market for this, but there is always a risk that the tool doesn't include everything or does something wrong.


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Haansi Thursday, April 15, 2010 10:44 AM
    Thursday, April 15, 2010 10:32 AM
    Moderator

All replies

  • You should talk to your hosting provider and see what means they allow and recommend you to use.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, April 15, 2010 7:50 AM
    Moderator
  • You should talk to your hosting provider and see what means they allow and recommend you to use.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Thursday, April 15, 2010 7:51 AM
    Moderator
  • I cant use hosting provider's way.

    Cant I do it automatically by my self ?

    plz guide
    Thursday, April 15, 2010 8:32 AM
  • <<Cant I do it automatically by my self ?>>

    It depends if your hosting provider had granted you the various level of permissions (in SQL Server and at the OS level) that would be needed. A backup is performed by the BACKUP DATABASE command. This produces a file on the SQL Server machine. You would then need some permissions at OS level to FTP (or similar) that file to your local machine. If you want to automate that, then you need some software from which you do that. Perhaps some AT schedule on your local machine. If you want to automate this on the hosting end, you would need permissions at that end (for instance to create SQL Server Agent jobs), and then some means to get the file from that remote machine to your local machine (conaisering that this would execute on the remove machine).


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Haansi Thursday, April 15, 2010 10:44 AM
    Thursday, April 15, 2010 9:06 AM
    Moderator
  • Tirork thanks,

    Is it possible to execute backup database command using SQL Server Management Studio that I can take backup of hosting db on local machine. Hosting do not give special permissions of OS level.

    If automatic scheduling is not required can it be done than ?

     

    plz guide

     

    Thursday, April 15, 2010 10:14 AM
  • <<Is it possible to execute backup database command using SQL Server Management Studio that I can take backup of hosting db on local machine>>

    Only if your local machine is accessible from the remote machine (using UNC name, as a network drive), which is highly unlikely. I.e., you really need to work with your hosting provider on this.

    Another option (instead of backup) is to use some tool which script out all table definitions, views, stored procedures etc and also generate INSERT commands for the data in all tables. There are tools on the market for this, but there is always a risk that the tool doesn't include everything or does something wrong.


    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Marked as answer by Haansi Thursday, April 15, 2010 10:44 AM
    Thursday, April 15, 2010 10:32 AM
    Moderator