How do I automate SQL backups on a shared host server?
I use a hosting service that allows me to archive my SQL 2008 database and download the BAK file only through the web interface - and I have access to the database through Management Studio. I can execute the backup command from Management Studio, but I have no rights to the path where the backups are located. Is there a way through SQL or a script to pull out a complete copy of the database - can I create the script needed to recreate the database using code? These are just a few thousand entries.
Thank.
source to share
Let's just leave that here as an answer (will compile your entire database to a .sql file (including data) and then compress it with winrar.)
I created this solution to run once a day from the task scheduler, so it only goes to the daily level. If you want to run this more (for example, once every half hour), you will need to edit "% date: ~ -4.4 %% date: ~ -7.2 %% date: ~ -10.2%" to go to the hour / minute / second level, depending on what you need.
NOTE: Must install SQL Publishing Wizard and Winrar
begin.bat . Wrapper for writing the log file. This is the batch file that will 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 - database backup
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 whatever interval you want and the database backup will be written along with the log file.
source to share
one way is to script the db structure and generate insert statements for the whole database with this SSMS addon http://www.ssmstoolspack.com/
source to share