Post reply

backup job using sql server agent in sql server 2008

  • November 26, 2010 at 11:15 pm

    #227204

    Hi,

    i have two systems(system A,system B) connected in network and with sql server 2008 installed on both.

    i want to take backup of database in system B, using sql server agent in system A..

    please help me...

  • November 27, 2010 at 3:32 am

    #1255952

    Easiest approach is to use SQLCMD.exe on server A to connect to Server B and then use a backup script in SQLCMD.

    This can be scheduled using a SQL Server Agent Job (CmdExec) job step or using a native Windows Scheduled Tasks to fire a .cmd file.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • November 27, 2010 at 4:33 am

    #1255955

    Hi pradeep,

    can u send me some code please?

  • November 27, 2010 at 4:41 am

    #1255956

    Create a file with the below command and save it as C:\backupcmd.sql

    BACKUP DATABASE [databasename1] TO DISK = '\\thepathofbackupfile\backupfilname1.bak'

    go

    BACKUP DATABASE [databasename2] TO DISK = '\\thepathofbackupfile\backupfilname2.bak'

    go

    BACKUP DATABASE [databasename3] TO DISK = '\\thepathofbackupfile\backupfilename3.bak'

    go

    then create a .cmd file which does the following.

    SQLCMD -S ServerB -E -i "C:\backupcmd.sql"

    You can directly double click on this file or create a SQL Server Agent Job by selecting the Job Step as Operating System(CmdExec)

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • November 28, 2010 at 10:01 pm

    #1256096

    Hi pradeep,

    i did like this but it was not working..

  • November 28, 2010 at 10:06 pm

    #1256099

    What was the error message?

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • November 28, 2010 at 10:12 pm

    #1256100

    Hi

    i got the following error.

    The job failed. The Job was invoked by Schedule 2 (CollectorSchedule_Every_5min). The last step to run was step 1 (stepname).

    Executed as user: server A. Msg 18456, Level 14, State 1, Server server B, Line 1 Login failed for user 'server B\Guest'. Process Exit Code 1. The step failed.

  • November 28, 2010 at 10:20 pm

    #1256103

    goldspoon9 (11/28/2010)

    Executed as user: server A. Msg 18456, Level 14, State 1, Server server B, Line 1 Login failed for user 'server B\Guest'. Process Exit Code 1. The step failed.

    The SQL Server Agent account on Server A does not have access to Server B. You can grant permissions to that account on Server B.

    Alternatively, you can make use of the "SA" or any SQL Authenticated login on Server B complete this task. The job step can be changed as

    SQLCMD -S ServerB -U SA -P Password -i "c:\backupcmd.sql"

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • November 28, 2010 at 10:46 pm

    #1256111

    Thanks alot pradeep..

    its working fine..

  • November 29, 2010 at 12:48 am

    #1256129

    No Problem. Glad it worked!

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • November 29, 2010 at 1:13 am

    #1256135

    Hi,

    Ok! you have taken backup from another SQL Agent but the question coming in mind that what is the purpose of it. What you achieve doing this? or why you required this type of mechanism.

    If you are minimizing your administration effort likes to configure backup on each server. I think you have to check SQL Server 2008 Feature called "Central Management Server and Server Group"

    Ram
    MSSQL DBA

  • November 29, 2010 at 10:03 pm

    #1256580

    Hi pradeep,

    i have another problem..

    i am connected in local network..

    previous problem is backup within the local network..

    but now i have to take backup of public ip system.

    how is it possible?

  • November 29, 2010 at 10:14 pm

    #1256582

    If your question is how to take the backup of a database on a Public Network, it is the same as earlier. In stead of ServerB in the server name you would mention the IP Address/Name of the computer on public network in -S parameter.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • November 30, 2010 at 10:49 pm

    #1257153

    Hi pradeep

    i have try this one.

    but it shows can not open backup device.

    i have given same path as given to local network backup.

    when i have given same path to local backup,it worked fine.

    but for public network backup it shows error..

    why?

  • December 1, 2010 at 1:41 am

    #1257192

    Please ensure that the SQL Server startup account has sufficient privileges on the Public Network share.

    Alternatively, you can take the backup locally to the disk and then copy it to the Public Network share.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

Viewing 15 posts - 1 through 15 (of 29 total)

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