What are the challenges you face when working across database platforms? Take the survey
Options

Restore to a different server failing

WindyCityGirlWindyCityGirl Posts: 20
edited June 30, 2015 6:29PM in SQL Backup Previous Versions
Hello,

I have a created a restore job to restore a production server to a staging server. The jobs are failing, but i cant find a log file or a reason why. I also can not send email for the restores but I can for the backups, the check box is grayed out.

The backup tool is installed on both servers and licenses are activated
I set all the jobs up from my production server. the backups are running - restores are not
All the backup files are on the production server.
Is there something I need to do to identify the server the files are on?
Do these need to be linked servers?

Please help,

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Try creating the restore job on the staging server. If the files are located on the production server, ensure that the SQL Backup Agent service startup account on the staging server has rights to access those files on the production server.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Thank you Peter - I will try that and let you know. Also do you know where the log files of what happened might be? I don't have a folder with the name found in the documentation.
  • Options
    Hello Peter,

    I am not sure we will be able to add cross server permissions to the production server. We do however use VisualCron which can copy the backfiles from one server to another. So we will us that to get the moved.

    I have copied the backup files manually to the staging server.
    I have run a restore of all the databases buy choosing restore and they run.
    I still can't seem to get the jobs to run.

    I have setup the SQL Backup Agent Service to use a local system account - Is that the part I am missing? Do we need an "Account" and if so what does it need? Do I need to use SQL Agent Account?

    Thank you
  • Options
    peteypetey Posts: 2,358 New member
    The default folder where the logs are stored is C:Documents and SettingsAll UsersApplication DataRed GateSQL BackupLog<instance name> on Windows 2003 and older, and C:ProgramDataRed GateSQL BackupLog<instance name> on Windows Vista and newer.

    The SQL Backup Agent service startup account needs to be an account that has the SQL Server sysadmin fixed server role assigned to it. It also needs permissions to read from the folders containing the backup files. It also needs permissions to read the SQL Backup registry settings, but that's usually not a problem.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Peter -

    I am part way there - some of the back-ups are running. Some are not. The ones that are not are those with 1 MDF and many NDF Files. I can not get the NDF Files to restore to their correct file name they restore as the DatabaseName_1, DatabaseName_2, DatabaseName_3 if i use a scheduled job. If i do a Restore (2nd item on the toolbar) I can get it to work if i map each NDF individually Restore Option "Individual Files to specified locations" That works, but if i set up a scheduled job to do the same it does not work.

    Kim
  • Options
    Peter,

    The issue with the NDF files was the "LOGICAL NAME". They needed the word FILE in front of the names I gave them. Little did I know that SQL would name the logical files something other than the file names I gave them. I found the following post on SQL SERVER Central which helped me figure this out.

    http://www.sqlservercentral.com/Forums/ ... 391-1.aspx


    SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName
    FROM sys.master_files AS mf


    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    Thank you for all your help Peter!
Sign In or Register to comment.