How to back up your database located at hosting site

If you are hosting sql server at one of the hosting sites, you may not have functional interface at their site management tools to create automstated backups.

Winhost hosting has great tools, but they dont have support to setup automated backup.

I figure out a way to do that.

First, you need to create a linked server to your database located at their servers. Of course, you need sql server studio to do that. You need to open the query connection to YOUR LOCAL DATABASE. Linked server will be at YOUR machine. We will use this linked server to schedule tasks.

   1:  --create linked server

   2:  /****** Object:  LinkedServer [S06.WINHOST.COM]    Script Date: 04/06/2012 13:52:26 ******/

   3:  EXEC master.dbo.sp_addlinkedserver @server = N'S06.WINHOST.COM', @srvproduct=N'SQL Server'

   4:   /* For security reasons the linked server remote logins password is changed with ######## */

   5:  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'S06.WINHOST.COM',@useself=N'False',@locallogin=NULL,@rmtuser=N'DB_YOUR_DATABASE_USER',@rmtpassword='########'

   6:   

   7:  GO

   8:   

   9:  EXEC master.dbo.sp_serveroption @server=N'S06.WINHOST.COM', @optname=N'collation compatible', @optvalue=N'false'

  10:  GO

  11:   

  12:  EXEC master.dbo.sp_serveroption @server=N'S06.WINHOST.COM', @optname=N'data access', @optvalue=N'true'

  13:  GO

  14:   

  15:  EXEC master.dbo.sp_serveroption @server=N'S06.WINHOST.COM', @optname=N'dist', @optvalue=N'false'

  16:  GO

  17:   

  18:  EXEC master.dbo.sp_serveroption @server=N'S06.WINHOST.COM', @optname=N'pub', @optvalue=N'false'

  19:  GO

  20:   

  21:  EXEC master.dbo.sp_serveroption @server=N'S06.WINHOST.COM', @optname=N'rpc', @optvalue=N'true'

  22:  GO

  23:   

  24:  EXEC master.dbo.sp_serveroption @server=N'S06.WINHOST.COM', @optname=N'rpc out', @optvalue=N'true'

  25:  GO

  26:   

  27:  EXEC master.dbo.sp_serveroption @server=N'S06.WINHOST.COM', @optname=N'sub', @optvalue=N'false'

  28:  GO

  29:   

  30:  EXEC master.dbo.sp_serveroption @server=N'S06.WINHOST.COM', @optname=N'connect timeout', @optvalue=N'0'

  31:  GO

  32:   

  33:  EXEC master.dbo.sp_serveroption @server=N'S06.WINHOST.COM', @optname=N'collation name', @optvalue=null

  34:  GO

  35:   

  36:  EXEC master.dbo.sp_serveroption @server=N'S06.WINHOST.COM', @optname=N'lazy schema validation', @optvalue=N'false'

  37:  GO

  38:   

  39:  EXEC master.dbo.sp_serveroption @server=N'S06.WINHOST.COM', @optname=N'query timeout', @optvalue=N'0'

  40:  GO

  41:   

  42:  EXEC master.dbo.sp_serveroption @server=N'S06.WINHOST.COM', @optname=N'use remote collation', @optvalue=N'true'

  43:  GO

  44:   

  45:  EXEC master.dbo.sp_serveroption @server=N'S06.WINHOST.COM', @optname=N'remote proc transaction promotion', @optvalue=N'true'

  46:  GO

Create SQL job in your local db server and enter this command:

   1:  --create your own sql job for linked server with following command at YOUR MACHINE

   2:  declare @syntax nvarchar(2000)

   3:  set @syntax = 'BACKUP DATABASE [DB_YOUR_DATABASE_NAME] TO  DISK = N''D:\DBBackup\userdb\DB_YOUR_DATABASE_NAME\DB_YOUR_DATABASE_NAME_new_4_6_2012_12_55_full.bak'' WITH FORMAT, INIT,  NAME = N''DB_YOUR_DATABASE_NAME-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10';

   4:   

   5:  execute [S06.winhost.com].master.dbo.sp_executesql @syntax

If you want to restore:
You can call this command when you are connected to your remote server. You are saving backup to their folder and using Linked server to automate backups without paying them.

   1:  --==========================RESTORE syntax================== 

   2:  USE master;

   3:  GO

   4:  --SINGLe user

   5:  ALTER DATABASE [DB_YOUR_DATABASE_NAME]

   6:  SET SINGLE_USER

   7:  WITH ROLLBACK IMMEDIATE;

   8:  GO

   9:  ALTER DATABASE [DB_YOUR_DATABASE_NAME]

  10:  SET READ_ONLY;

  11:  GO

  12:   

  13:  RESTORE DATABASE [DB_YOUR_DATABASE_NAME] FROM  DISK = N'D:\DBBackup\userdb\DB_YOUR_DATABASE_NAME\DB_YOUR_DATABASE_NAME_new_4_6_2012_12_55_full.bak' WITH  FILE = 1,  MOVE N'DB_YOUR_DATABASE_NAME_data' TO N'E:\SQLData\DB_YOUR_DATABASE_NAME.mdf',  MOVE N'DB_YOUR_DATABASE_NAME_log' TO N'F:\SQLLog\DB_YOUR_DATABASE_NAME.ldf',  NOUNLOAD,  STATS = 10

  14:  GO

  15:  --restore to multi user

  16:  ALTER DATABASE [DB_YOUR_DATABASE_NAME]

  17:  SET MULTI_USER;

  18:  GO

You can schedule your backups at your machine and take copies at their server.

How to get the directory from their usual backup directory:
Login to remote database and right click your db.

Backup properites window will show you the remote folder that your hosting is using for backups. I don't think you can write somewhere else. You need to use their folder to create your own backups.