Post reply

Backup a db from a remote server

  • August 26, 2010 at 2:04 pm

    #225319

    I have a sql 2005 express edition db server with dbs that can not be moved to our main 2005 db server. I'd like to schedule backups but can't, being it's expresss. Is there a way anyone knows of that I can create a backup job on our main database server and backup the databases on the expresss server? Thanks!

    Thanks!

  • August 26, 2010 at 3:28 pm

    #1212575

    Create a job on your main SQL 2005 server with an Operating system(cmdExec) job step and use SQLCMD. For example:

    SQLCMD -E -S SERVER\SQLEXPRESS -Q"BACKUP DATABASE <dbname> TO DISK = 'C:\dbname.bak'"

  • August 26, 2010 at 4:38 pm

    #1212615

    Thanks! That works perfectly! I really appreciate it.

    Thanks!

  • August 31, 2010 at 11:00 pm

    #1214728

    You can check this

    http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express

  • September 2, 2010 at 3:04 am

    #1215457

    Slightly away from your question... you could also write a stored proc that is executed at startup (exec sp_configure 'scan for startup procs', '1' and exec sys.sp_procoption '<sp name>','startup','on') that loops performing a full backup (or any other maintenance operation you care to script) then waits for the next backup date (waitfor time '<whatever>').

    This has the advantage of removing the dependency of the express instance on your main instance.

    Dave.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply