November 30, 2009 at 7:24 pm
Hello,
I'm testing the set up of transaction log shipping in SQL 2005, I did all the steps (except the monitor) set up via the wizard as describe in Microsoft site. Once the set up was done, I started/executed the job, but it error out complaining that it can not find the database's backup file. My questions are:
1. I thought that by choosing the backup/restore option, SQL will do a backup of the db on the primary server at this instance (which it did) and then copied it over to the secondary server and then do the restore automatically ? It did not. My job error out with message that it can not find the backup file. The network path or folder on the secondary server has shared property and SQL agent has admin access on both primary and secondary server.
2. Since my transaction log shipping job failed, do I have to restarted the whole set up all over again ? or is there anywhere that I can retrieve the job again so I don't have to re-do from the beginning ?
3. Is it possible to replicate a copy of database from SQL 2008 database and transaction log shipping on a SQL 2005 server ? I was asked that if it's possible that one of the application running SQL 2008 can have their warm dr set up on another server that is SQL 2005 ?
TIA
T.
December 1, 2009 at 12:21 am
newdb (11/30/2009)
Hello,I'm testing the set up of transaction log shipping in SQL 2005, I did all the steps (except the monitor) set up via the wizard as describe in Microsoft site. Once the set up was done, I started/executed the job, but it error out complaining that it can not find the database's backup file. My questions are:
1. I thought that by choosing the backup/restore option, SQL will do a backup of the db on the primary server at this instance (which it did) and then copied it over to the secondary server and then do the restore automatically ? It did not. My job error out with message that it can not find the backup file. The network path or folder on the secondary server has shared property and SQL agent has admin access on both primary and secondary server.
2. Since my transaction log shipping job failed, do I have to restarted the whole set up all over again ? or is there anywhere that I can retrieve the job again so I don't have to re-do from the beginning ?
3. Is it possible to replicate a copy of database from SQL 2008 database and transaction log shipping on a SQL 2005 server ? I was asked that if it's possible that one of the application running SQL 2008 can have their warm dr set up on another server that is SQL 2005 ?
TIA
T.
Hi,
Please send me the error details.
Logshipping from SQL 2005 instance to Sql 2008 [/url]
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 1, 2009 at 12:26 am
December 1, 2009 at 1:06 am
SQL server agent service account of the secondary server, should have both read access on the folder where your backup files are stored. The agent account of the primary server should have write access on the folder where you are saving the backup files.
Before proceeding further, please verify that required permissions are already set on the respective folder.
Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly
December 1, 2009 at 6:40 am
The permission for SQL Agent on both servers have Read/Write access to the folder on both servers.
December 1, 2009 at 6:42 am
Muthukkumaran,
I can not find the error anywhere. I even ran these commands
use msdb
Select * From dbo.log_shipping_monitor_error_detail
and it's not showing any error. I looked in the SQL Agent error log on both servers and can't find the error.
December 1, 2009 at 6:46 am
make it easy on yourself. Do the initial full backup and restore manually, then start the log shipping with an already initialised database.
make sure you do the restore with norecovery
---------------------------------------------------------------------
December 1, 2009 at 2:13 pm
Hi George,
I know that would be the easiest solution, but I would like to know why it does not work, when it said it would.
December 1, 2009 at 5:04 pm
what was the operating system error code? Post your actual error.
---------------------------------------------------------------------
December 1, 2009 at 6:04 pm
Cannot open backup device '\\datadev01\d$\DBA\Thitesting\DBA.bak'. Operating system error 2(The system cannot find the file specified.).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)
December 1, 2009 at 6:06 pm
Sorry George,
I click the post too quickly. The error I got when I ran the job is:
Cannot open backup device '\\datadev01\d$\DBA\Thitesting\DBA.bak'. Operating system error 2(The system cannot find the file specified.).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)
December 1, 2009 at 8:44 pm
That's an OS error indicating that it cannot get to the path, or it does not have permission to access the file.
Is that a path that is local to the machine? Or is it remote on another server?
December 2, 2009 at 12:53 am
newdb (12/1/2009)
Muthukkumaran,I can not find the error anywhere. I even ran these commands
use msdb
Select * From dbo.log_shipping_monitor_error_detail
and it's not showing any error. I looked in the SQL Agent error log on both servers and can't find the error.
Cannot open backup device '\\datadev01\d$\DBA\Thitesting\DBA.bak'. Operating system error 2(The system cannot find the file specified.).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)
Please check what Steve told ?
Also see the link
RESTORE DATABASE is terminating abnormally
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 2, 2009 at 12:57 am
Hi,
Please run & verify the following in master
Exec Xp_cmdshell 'dir \\datadev01\d$\DBA\Thitesting\DBA.bak'
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 2, 2009 at 2:29 am
newdb (12/1/2009)
Sorry George,I click the post too quickly. The error I got when I ran the job is:
Cannot open backup device '\\datadev01\d$\DBA\Thitesting\DBA.bak'. Operating system error 2(The system cannot find the file specified.).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)
as it says within the error the system cannot find that file, which means its not there, thats an invalid path or some way or another (typo probably) so thats why it failed.
It's not a permissions issue, that would return Operating system error 5(access denied)
the real lesson here is that the best way to initialise log shipping is to do the full restore manually rather than use the wizard to do it for you.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply