Home / server / Questions / 78374
Accepted
CC.
Asked: 2009-10-27 07:58:36 +0800 CST

SQL Server 2005 backup from non-domain computer?

  • 772

We need to make a backup of a MS SQL Server 2005 database. The computer on which the database resides is in it's own workgroup and is not on our domain. It also doesn't appear the vendor will support us adding the PC to the domain. So we need to find a way to get the SQL backup to save on to our domain.

I'm not a SQL guru, but I can see that the SQL maintenance plans we run for other databases let us specify where we wish to place the backup. But the databases, users and shares that we use are all on our domain. It seems our hang-up is that the SQL maintenance plan has to run as the local admin on the non-domain PC, but then I don't have a good way of giving that user rights to our network share.

The only other option I can come up with is a hidden share on our domain with Everyone R/W access, and that makes my skin crawl.

I'm sure we could script a file copy after the SQL maintenance plan runs locally. We would just embed credentials in the script. But we think it would be better if the Maintenance Plan could take care of this itself--less moving parts, in my mind.

I feel like I'm being thick on a Monday, and this should be simpler than I'm making it. What am I missing?

Thanks for your help! CC

sql-server

2 Answers

  1. Best Answer

    If you want to stick with using the Maintenance Plan, even though most DBA's I know hate them with a passion. You can make the copy script a step in the maintenance plan. This way the backup & copy remains synchronous. Follow these steps:

    1. Create your copy script using your scripting language of choice. This is simple enough to do with a dos batch.
    2. Create a SQL Server Agent job create a job & add a step with type: Operating System (CmdExec) and specify the path of the batch file to run, eg. X:\mssql\backup\copy_db.cmd. Do not create a schedule.
    3. Modify the maintenance plan & add the Execute SQL Server Agent Job Task. Do this by dragging this task from the toolbox to the design window.
    4. Get the Execute SQL Server Agent Job Task to run the job you just created.
    5. Select the existing Backup Database Task and then drag the green arrow & connect it to the Agent Job task you just created. This will ensure that the copy job is started when a backup has successfully completed.
    • 2

  2. You're going to have to store credentials somewhere, since the computer hosting the database isn't a member of the domain (and therefore can't just use its computer account).

    Personally, I'd write a script to run on the SQL Server computer as a "Scheduled Task" that would fire off the backup using SQLCMD. After the backup completes, I'd copy it to a remote machine using credentials embedded in that script. Trying to catch the backup after the maintenance plan runs is problematic, to me, because the maintenance plan is asynchronous to the script copying the database. I'd keep the maintenance plan around, but either remove the backup step or just not use the backup it creates. Doing the backup and copy in a single script is nice because it's synchronous.

    • 1