CODEWITHSUNDEEP
sqlsql-serverbackup
SkunkSpinner
SkunkSpinner

Reputation: 11636

How to automate SQL backup on a shared hosted server?

I'm using a hosting service which allows me to backup my SQL 2008 database and download the BAK file via a web interface only--and I have access to the database via Management Studio. I can execute the backup command from Management Studio,but I don't have rights to the path where the backups are located. Is there any way via SQL or script to pull down a full copy of the database--can I generate the script required to recreate the database using code? It's only a few thousand records.

Thanks.

Upvotes: 1

Views: 2417

Answers (5)

anthonyvscode
anthonyvscode

Reputation: 995

Just going to leave this here as an answer (Scripts your entire database to a .sql file (including data), then compresses it with winrar.)

I created this solution to be run once a day from task scheduler, which is why it only goes down to a daily level. If you wanted to run this more (for eg, once every half an hour) you will need to edit the "%date:~-4,4%%date:~-7,2%%date:~-10,2%" fields to go down to an hour/minute/seconds level, depending on whats necessary for you.

NOTE: SQL Publishing wizard & Winrar need to be installed

begin.bat - Wrapper to keep a record of the Log file. This is the batch file to be called by the Task Scheduler

::Run dbbackup.bat and append all output to log.txt

md C:\[directory]\%date:~-4,4%%date:~-7,2%%date:~-10,2%

"dbbackup.bat" >> "C:\[Directory]\%date:~-4,4%%date:~-7,2%%date:~-10,2%\log.txt"

dbbackup.bat - Backup the database

echo off
cls
echo %date% %time%
echo ***************************************************************************
echo ** Script all objects in databases and save them in 'yyyymmdd' folder  **
echo ***************************************************************************
cd C:\[directory]\%date:~-4,4%%date:~-7,2%%date:~-10,2%
"C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\sqlpubwiz.exe" script -C "[ConnectionString]" [dbname]_%date:~-4,4%%date:~-7,2%%date:~-10,2%.sql
echo ***************************************************************************
echo ** RAR compress all .sql script files                                    **
echo ***************************************************************************
"C:\Program Files\WinRAR\WinRAR.exe" -ibck a [dbname]_%date:~-4,4%%date:~-7,2%%date:~-10,2%.rar [dbname]_%date:~-4,4%%date:~-7,2%%date:~-10,2%.sql
echo WinRAR has completed execution
echo ***************************************************************************
echo ** Delete all .sql script files                                          **
echo ***************************************************************************
del *.sql
echo .SQL files deleted

Just create a scheduled task to run begin.bat to run at any interval you require, and a database backup will be scripted along with a log file.

Upvotes: 2

Ruslan
Ruslan

Reputation:

Free SqlBackupAndFTP allows to do remote backups using scripting

Upvotes: 2

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

one way is to script the db structure and generate insert statements for the whole database with this SSMS addin http://www.ssmstoolspack.com/

Upvotes: 0

Sam
Sam

Reputation: 7678

If a host does not provide an easy way to get offsite backups of your database(automated - and tell you how to do it), you should find another.

Upvotes: 1

mson
mson

Reputation: 7824

If you have access via Management Studio, why don't you copy the hosted db on to your local machine (Tasks > Copy DB)?

Once it's been restored to the local system, you can do whatever you want.

Upvotes: 2

Related Questions