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.
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.
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.
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.
one way is to script the db structure and generate insert statements for the whole database with this SSMS addin http://www.ssmstoolspack.com/
