Restore to a different server failing

WindyCityGirl

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,

petey

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.

WindyCityGirl

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.

WindyCityGirl

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

petey

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.

WindyCityGirl

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

WindyCityGirl

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!