Call for a database backup for a database on another (linked) server

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  July 8, 2015 at 10:27 am

  #309682

  I have a sproc that will generate a dynamic call (and this must be done in a stored procedure),... Anyway the dynamic call I am having problems with is that I need

  to dynamically create a statement to backup a database. It works as long as the database is on the same server / instance as the stored procedure. I want to be able to from server/instance A create a command that does a backup of a database that is on server B. I do not need help with the dynamic part (I don't think) I just can't figure out the syntax for a database backup where the database is on another server.

  Trying something like [ServerName].[DatabaseName] does not work

  Any help on this?

  <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

• Michael L John

  One Orange Chip

  Points: 27065

  More actions

  July 8, 2015 at 10:37 am

  #1811048

  Run the proc on the linked server???

  EXEC serverame.databasename.schema.proc

  Or, create a job on the linked server and start it like this:

  EXEX servername.msdb.dbo.sp_start_job @job_name = 'My job'

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 8, 2015 at 10:38 am

  #1811051

  Have you tried this:

  EXEC (@SQLCmd) AT <your_linked_server_name>

  Where @SQLCmd is your dynamic backup command?

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  July 8, 2015 at 11:22 am

  #1811065

  The job idea might work. Running the proc from the server with the DB to be backed up wont.

  Thanks for the idea.

  <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  July 8, 2015 at 11:23 am

  #1811066

  I have not tried this. Trying now 🙂

  <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  July 8, 2015 at 11:30 am

  #1811071

  🙂 Thank you so much.... My acid test worked so now I just need to modify my actual code. Here is what I did for testing, just manually typed it out and I got my result.

  DECLARE @sql nvarchar(4000)

  SET @sql = 'BACKUP DATABASE AuditTest2 TO DISK = ''\\clients.advance.local\Data\WIP\AuditTEst2.bak'''

  PRINT cast(@SQL as varchar(4000))

  EXEC (@SQL) AT adasdb

  <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  July 8, 2015 at 11:49 am

  #1811082

  Well Close my no cigar. I am trying to parameter the destination server name and I can not get that to execute.

  Here is what I am playing with:

  DECLARE @BackupStatement as nvarchar(4000)

  ,@SourceDBName as varchar(100) = 'AuditTest2'

  ,@SourceServerName as varchar(100) = 'adasdb'

  ,@Destination as varchar(500) = '\\clients.advance.local\Data\WIP\test' + '80' + '.bak'

  PRINT cast(@Destination as varchar(4000))

  SET @BackupStatement = 'BACKUP DATABASE ' + @SourceDBName + ' TO DISK = ''' + @Destination + ''''

  SET @BackupStatement = @BackupStatement + ' AT ' + @SourceServerName

  exec ( @BackupStatement)

  <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

• WayneS

  SSC Guru

  Points: 95461

  More actions

  July 8, 2015 at 11:55 am

  #1811087

  Jeffery Williams (7/8/2015)

  ---

  Well Close my no cigar. I am trying to parameter the destination server name and I can not get that to execute.

  Here is what I am playing with:

  DECLARE @BackupStatement as nvarchar(4000)

  ,@SourceDBName as varchar(100) = 'AuditTest2'

  ,@SourceServerName as varchar(100) = 'adasdb'

  ,@Destination as varchar(500) = '\\clients.advance.local\Data\WIP\test' + '80' + '.bak'

  PRINT cast(@Destination as varchar(4000))

  SET @BackupStatement = 'BACKUP DATABASE ' + @SourceDBName + ' TO DISK = ''' + @Destination + ''''

  SET @BackupStatement = @BackupStatement + ' AT ' + @SourceServerName

  exec ( @BackupStatement)

  It looks like you're close...

  EXEC (@SQL) AT adasdb

  is not the same as:

  EXEC (@SQL AT adasdb)

  you might need to do EXEC (EXEC (@SQL) + ' AT adasdb') - if you can even nest EXEC's like that (I've never tried)

  is not the same as

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 8, 2015 at 12:02 pm

  #1811092

  WayneS (7/8/2015)

  ---

  Jeffery Williams (7/8/2015)

  ---

  Well Close my no cigar. I am trying to parameter the destination server name and I can not get that to execute.

  Here is what I am playing with:

  DECLARE @BackupStatement as nvarchar(4000)

  ,@SourceDBName as varchar(100) = 'AuditTest2'

  ,@SourceServerName as varchar(100) = 'adasdb'

  ,@Destination as varchar(500) = '\\clients.advance.local\Data\WIP\test' + '80' + '.bak'

  PRINT cast(@Destination as varchar(4000))

  SET @BackupStatement = 'BACKUP DATABASE ' + @SourceDBName + ' TO DISK = ''' + @Destination + ''''

  SET @BackupStatement = @BackupStatement + ' AT ' + @SourceServerName

  exec ( @BackupStatement)

  It looks like you're close...

  EXEC (@SQL) AT adasdb

  is not the same as:

  EXEC (@SQL AT adasdb)

  you might need to do EXEC (EXEC (@SQL) + ' AT adasdb') - if you can even nest EXEC's like that (I've never tried)

  is not the same as

  This?

  DECLARE @BackupStatement as nvarchar(4000)

  ,@SQLCmd nvarchar(4000)

  ,@SourceDBName as varchar(100) = 'AuditTest2'

  ,@SourceServerName as varchar(100) = 'adasdb'

  ,@Destination as varchar(500) = '\\clients.advance.local\Data\WIP\test' + '80' + '.bak'

  PRINT cast(@Destination as varchar(4000))

  SET @BackupStatement = 'BACKUP DATABASE ' + @SourceDBName + ' TO DISK = ''' + @Destination + ''''

  SET @SQLCmd = N'EXEC (' + @BackupStatement + N') AT ' + @SourceServerName + N'';

  print @SQLCmd;

  exec (@SQLCmd)

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  July 8, 2015 at 12:05 pm

  #1811094

  Little closer maybe but still the following error:

  \\clients.advance.local\Data\WIP\test80.bak

  EXEC (BACKUP DATABASE AuditTest2 TO DISK = '\\clients.advance.local\Data\WIP\test80.bak') AT adasdb

  Msg 156, Level 15, State 1, Line 1

  Incorrect syntax near the keyword 'BACKUP'.

  Msg 102, Level 15, State 1, Line 1

  Incorrect syntax near ')'.

  <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  July 8, 2015 at 12:20 pm

  #1811103

  Still plugging away at this. I know it is close but still can't get it to work.

  If anyone has any other ideas I am all ears.. well all ears and two hands.

  <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 8, 2015 at 12:46 pm

  #1811108

  How about trying this:

  DECLARE @BackupStatement as nvarchar(4000)

  ,@SQLCmd nvarchar(4000)

  ,@SQL nvarchar(4000)

  ,@SourceDBName as varchar(100) = 'AuditTest2'

  ,@SourceServerName as varchar(100) = 'adasdb'

  ,@Destination as varchar(500) = '\\clients.advance.local\Data\WIP\test' + '80' + '.bak'

  SET @sql = N'BACKUP DATABASE ' + @SourceDBName + N' TO DISK = ''\\clients.advance.local\Data\WIP\AuditTEst2.bak''';

  PRINT cast(@SQL as nvarchar(4000))

  set @SQLCmd = N'EXEC(@SQLCmd1) AT ' + @SourceServerName;

  print @SQLCmd;

  exec sys.sp_executesql @SQLCmd, N'@SQLCmd1 nvarchar(4000)', @SQLCmd1 = @sql;

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  July 8, 2015 at 12:47 pm

  #1811109

  Just keeping you helpful folks in the loop.

  I was just thinking. I could use Dynamic SQL that creates a stored procedure that contains the backup command as a static statement. THEN execute that stored procedure, then drop it when done.

  Sound pretty messed up but I think it will work.

  Anyone have any input as to why I would want to avoid this move?

  <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  July 8, 2015 at 12:50 pm

  #1811110

  High level..

  I pasted into SSMS and hit F5, it worked. Now let me look at the code and see if I can apply it to what I am doing. 🙂

  <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  July 8, 2015 at 12:52 pm

  #1811111

  You Lynn are a genius!

  Thank you so much for your help.

  <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh