Friday, April 6, 2012

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.



8 comments:

  1. Thanks for this, but I fail to get it working, when backing up is this executing on the remote server hence your example D:\ backup location is on the remote server ? I receive the following error.


    Msg 3013, Level 16, State 1, Line 1
    BACKUP DATABASE is terminating abnormally.
    Msg 3201, Level 16, State 1, Line 1
    Cannot open backup device 'D:\My Data\My Databases\AutomatedBackups\test123.co.nz\test123_full.bak'. Operating system error 3(The system cannot find the path specified.).

    I have tried creating file in that location locally, but still the error persists.

    Thanks for any help.

    Matt

    ReplyDelete
    Replies
    1. Hey Matt,
      Yes, you are running at remote server. You need to use their folders. They will not give you direct access to external folders from database. You can use their regular backup folder and write to that. Your default permissions will work fine. You can see latest backup directory from latest backups. If you right click and try to backup, it will show some directory in there.

      Delete
  2. Omer:

    Is this still working? I tried to create linked server by changing the server from S06.WINHOST.COM to the one listed under control panel Database Server, replaced @rmtuser=N'DB_YOUR_DATABASE_USER' with the Database User in control plan and then the password to Database Password listed in control panel. When I execute I get:

    Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 29
    User does not have permission to perform this action.
    Msg 15247, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 25
    User does not have permission to perform this action.
    Msg 15247, Level 16, State 1, Procedure sp_serveroption, Line 27
    User does not have permission to perform this action.

    Thanks

    Jimmy

    ReplyDelete
  3. OK, got the linked server working, but still cannot find the right path to backup as Matt was having a problem with. I did a manual backup and see my latest backup directory its like this E:\web\XXXXXXXX\App_Data. Where are you right clicking to find the directory name?

    ReplyDelete
  4. Hey Jimmy,
    I updated that. You can right click over your db and select backup. It will show the latest backup file. You can use that folder, because you will have access to that.

    ReplyDelete
  5. I have found the remote backup folder to be...
    d:\dbbackup\userdb\DB_xxxx\xxxx.bak
    and I have backed successfully to that using the script above, but the problem is I do not see this file in my /app_data/ folder so cannot pickup from FTP ?

    Any ideas?

    ReplyDelete
  6. Hi Omer, I too can run the script but I cannot access the folder to download via ftp. I get the same problem as MJLamb above. How do you download the backup? Many thanks, Phil

    ReplyDelete
  7. Thank you for sharing about winhost Control Panel. I use Plesk Control Panel on hostforlife.eu and it is very easy to backup your database. Just simply go to your CP--> backup manager--> database backup repository--> backup. Then, we can download the .bak file. :)

    ReplyDelete

Hey!
Let me know what you think?