September 4, 2019 at 4:01 pm
Thanks for the assist and i appreciate any help that is given. Trying to do some automation of a backup file from one server to another but not that simple so let me explain.
Server A is where i want the backup restored...Server B is where the backup history is stored and Server C is the actual production box. Server C backup history is NOT in Server C msdb as its in the msdb of Server B. I want to send the code from server A to run and be linked to server B for my needs and ran on server A. IF i run the code on server B it works and restores to a different location with no issues but when i run from anywhere else does not work if trying as link server. Code below as is the error and please note that the user running this has 'sa' privs on all servers across the board.
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @cmd NVARCHAR(500)
DECLARE @backupPathData NVARCHAR(500)
DECLARE @backupPathLogs NVARCHAR(500)
DECLARE @backupFilePathLatestFull NVARCHAR(500)
-- 2 - Initialize variables --made only changes here
SET @dbName = 'bbDatabase'
SET @backupPathData = 'C:\SQL-Files\SQL-RS-DBDATA\'
SET @backupPathLogs = 'C:\SQL-Files\SQL-RS-DBLOGS\';
-- 3 - get file from the msdb database to have the proper file name
Select * From OPENQUERY([192.168.x.xx],'DECLARE @backupFilePathLatestFull NVARCHAR(500)
Set @backupFilePathLatestFull =
(SELECT TOP 1
msdb.dbo.backupmediafamily.physical_device_name
FROM
msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
AND database_name = ''bbDatabase''
AND backupset.type = ''D''
ORDER BY
msdb.dbo.backupset.backup_finish_date DESC)')
PRINT @backupFilePathLatestFull
When i run this the error i get back is below
Msg 7357, Level 16, State 2, Line 18
Cannot process the object "DECLARE @backupFilePathLatestFull NVARCHAR(500)
Set @backupFilePathLatestFull =
(SELECT TOP 1
msdb.dbo.backupmediafamily.physical_device_name
FROM
msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
AND database_name = 'bbDatabase'
AND backupset.type = 'D'
ORDER BY
msdb.dbo.backupset.backup_finish_date DESC)". The OLE DB provider "SQLNCLI11" for linked server "192.168.x.xx" indicates that either the object has no columns or the current user does not have permissions on that object.
DHeath
September 4, 2019 at 7:37 pm
I hate that error...it's from when the driver can't figure out what the shape of result set is going to be. Often you can bypass that by including SET FMTONLY OFF. I played with your query and it didn't work. Another option though is to create a stored procedure on server B to get the last backup and call that in your OPENQUERY.
Sue
September 4, 2019 at 8:56 pm
Thank you for the reply...much appreciated...so i am thinking i can run my query as a stored proc on server Production....and have it return a command in @cmd... Then have the query from Server B pick up that returned value and then run the @cmd locally where it should work. I have modified my code a bit in the below script that return everything perfectly as it should but not sure how to pass variables between serves..
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @cmd NVARCHAR(500)
DECLARE @backupPathData NVARCHAR(500)
DECLARE @backupPathLogs NVARCHAR(500)
DECLARE @backupFilePathLatestFull NVARCHAR(500)
-- 2 - Initialize variables --made only changes here
SET @dbName = 'bbDatabase'
SET @backupPathData = 'C:\SQL-Files\SQL-RS-DBDATA\'
SET @backupPathLogs = 'C:\SQL-Files\SQL-RS-DBLOGS\'
-- 3 - get file from the msdb database to have the proper file name
SET @backupFilePathLatestFull =
(SELECT TOP 1
msdb.dbo.backupmediafamily.physical_device_name
FROM
msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE
(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
AND database_name = 'bbDatabase'
AND backupset.type = 'D'
ORDER BY
msdb.dbo.backupset.backup_finish_date DESC)
--PRINT @backupFilePathLatestFull
-- 4 - Takes latest full backup from relay and restore accordingly to named server.
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = N''' + @backupFilePathLatestFull + '''' +
' WITH FILE = 1, ' + 'MOVE N''' + @dbname + '_Data'' ' + 'TO N''' + @backupPathData + @dbname +'_data.mdf'' ,' +
' MOVE N''' + @dbname +'_Log'' ' + 'TO N''' + @backupPathLogs + @dbname +'_Log.ldf'',' +
' NOUNLOAD, REPLACE, STATS = 5'
PRINT @cmd
EXEC @cmd --execute latest backup restore.
****How do i pass @cmd from one server to another OR can that be done?
DHeath
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply