SQL Server 2008 R2 T-SQL backup script for Linked Server access

Question

I am trying to do a Full Copy Only database backup from a linked server to drive E on another servers SAN. Not finding any help. The linked server SAPRS02 works and changed the RPC options but still doesn't work.

Any help is appreciated.

JJ

DECLARE @DateStr nvarchar(500), @ArchivePath nvarchar(500),
        @FileName nvarchar(500), @DestFileName nvarchar(500),
        @dbName nvarchar(255), @SvrName nvarchar(200),
        @RADBACKUP nvarchar(MAX)

SET @dbName = 'MYDB' 
SET @ArchivePath = 'E:\SQLBackups\MyDIR\'
SET @SvrName = REPLACE(@@SERVERNAME,'\','-')
SET @DateStr = CONVERT(NVARCHAR(20),GETDATE(),120)
SET @DateStr =  REPLACE(REPLACE(REPLACE(REPLACE(@DateStr,'',''),':',''),'-','_'),' ','')
SET @FileName = @SvrName + '_' + @DBName + '_CopyOnly_backup_' + @DateStr + '.bak'
SET @DestFileName = @ArchivePath + @FileName


SET @RADBACKUP = 'BACKUP DATABASE @dbName
TO DISK = @DestFileName
WITH COMPRESSION
COPY_ONLY, 
CHECKSUM, COMPRESSION,
NAME = @FileName, SKIP, STATS = 10'

--SELECT * FROM @RADBACKUP

EXECUTE SAPRS02.master.dbo.sp_executesql @RADBACKUP

ERROR is:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@dbName".

Msg 137, Level 15, State 2, Line 1 Must declare the scalar variable "@dbName". — user5495350
– user5495350, Commented Oct 21, 2016 at 19:45
When you execute the string in @RADBACKUP, there is no scope on any of the variables in there. Either concatenate them as strings or use the parameter feature of sp_execute_sql. By concatenate as strings, I mean do this: SET @RADBACKUP = 'BACKUP DATABASE ' + @dbName + ' ' + — Nick.Mc
– Nick.Mc, Commented Oct 22, 2016 at 4:43

Sean Pearce · Accepted Answer · 2016-10-24 07:16:09Z

1

SET @RADBACKUP = 'BACKUP DATABASE ' + QUOTENAME(@dbName) + '
TO DISK = ''' + @DestFileName + '''
WITH COMPRESSION
COPY_ONLY, 
CHECKSUM, COMPRESSION,
NAME = ''' + @FileName + ''', SKIP, STATS = 10';

answered Oct 24, 2016 at 7:16

Sean Pearce

1,1705 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user5495350 Over a year ago

Now I get this. Thanks

user5495350 Over a year ago

Incorrec6 syntax near 'Copy_Only'.

user5495350 Over a year ago

I was just missing a comma. Now, for some reason it trying to put the backup on H: drive. I don't have a H drive. Hmm. Still researching.

user5495350 Over a year ago

Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. Msg 262, Level 14, State 1, Line 1 BACKUP DATABASE permission denied in database 'RAD'.

user5495350 Over a year ago

I worked around by loaded the data in a temp table and them using SSMS, exported to the other server, but still need to create a SSIS package to perform this on timely manner, at call.

Collectives™ on Stack Overflow

SQL Server 2008 R2 T-SQL backup script for Linked Server access

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related