May 14, 2010 at 8:36 am
Here is the situation ....we have a job in dev that refreshes one of the databases from the backup of the prod and has been failing for last 2 tries.... the job ran fine before and no changes were made to it and suddenly started failing with the following error:
Executed as user: agent. Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 42000] (Error 2) OLE DB provider "SQLNCLI" for linked server "server1" returned message "Login timeout expired".
When I changed the setting of the prod server to 'Enable Openrowset and OpendataSource' support' in Surface Ara Configuration and re ran the job it ran succesfully , when I disabled the feature again and tried to re ran the job again it ran succesfully again....but failed before i made any changes..
After making the changes in the Surfae Area configuration the job is working fine as of now....
Few Diagnostics performed:
- I had setup a job that would run a select statement from dev server to prod during off hours and the job failed continously for a while and then started completing succesfully for a while and then started failing again.....which is completely weird.....I dont know whats going wrong...
May 14, 2010 at 8:43 am
Hey Trooper,
Sorry I'm a little confused, are you restoring from a prod backup.
Or importing data from a prod database to refresh your dev?
In the code that you use to import (if it is that) do you use the
opendatasource, http://msdn.microsoft.com/en-us/library/ms190312.aspx, or openrowset,http://msdn.microsoft.com/en-us/library/ms179856.aspx , commands?
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 14, 2010 at 8:45 am
We are trying to restore the dev db with a backup of prod...and I use none of those...
May 14, 2010 at 8:55 am
can you post your script?
It seems that instead of a linked server you need a shared folder
in your script you would have the restore be
RESTORE DATABASE [DATABASENAME] FROM DISK = '\\SHAREDSERVER\BACUPFOLDER\BACKUPFILE.BAK'WITH FILE = 1, MOVE N'BACKUPDATAFILE' TO N'D:\MSSQL_DATA\MSSQL\DATA\DATAFILE.MDF', MOVE N'LOGFILE' TO N'D:\MSSQL_DATA\MSSQL\DATA\LOGFILE.LDF', NOUNLOAD, STATS = 10
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 14, 2010 at 9:04 am
Looks like this...
declare @backpath varchar(512)
set @backpath =
(select TOP 1 physical_device_name
from prodserver.msdb.dbo.backupset BS
inner join prodserver.msdb.dbo.backupmediafamily BF
on BS.media_set_id = BF.media_set_id
where database_name = 'dbname'
order by backup_start_date desc)
RESTORE DATABASE devdb
FROM DISK = @backpath
WITH MOVE 'ARM_SWRM_Data' TO 'E:\dev.MDF',
MOVE 'ARM_SWRM_log' TO 'F:\dev.LDF'
May 14, 2010 at 9:12 am
The restore is occuring on the same physical box?
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 14, 2010 at 9:14 am
Save physical box? I am assuming your wuestion was same physical box...and no its not..
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply