Restore to a different server failing
WindyCityGirl
Posts: 20
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,
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
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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
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!