Generate script

• gary1

  SSC-Insane

  Points: 21152

  More actions

  January 4, 2018 at 10:59 am

  #409919

  HI,
  We have 100 databases and need to take tail back up. I'm trying to generate sql script for all 100 databases. Can you please advise?

  BACKUP LOG DB1 TO DISK = N'Z:\DR\Taillog_Backup\DB1.trn'
  WITH NORECOVERY 
  GO

  I'm just getting name but not sure how to include the path 

  SELECT 'Backup log '''+NAME+'''' FROM sys.databases

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 4, 2018 at 11:45 am

  #1974512

  gary1 - Thursday, January 4, 2018 10:59 AM

  HI,
  We have 100 databases and need to take tail back up. I'm trying to generate sql script for all 100 databases. Can you please advise?

  BACKUP LOG DB1 TO DISK = N'Z:\DR\Taillog_Backup\DB1.trn'
  WITH NORECOVERY 
  GO

  I'm just getting name but not sure how to include the path 

  SELECT 'Backup log '''+NAME+'''' FROM sys.databases

  You just need to keep building the string.
  
SELECT 'BACKUP LOG ' + QUOTENAME(name) + ' TO DISK = N''Z:\DR\Taillog_Backup\' + name + '.trn''
WITH NORECOVERY
GO'
FROM sys.databases


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• gary1

  SSC-Insane

  Points: 21152

  More actions

  January 4, 2018 at 3:12 pm

  #1974564

  Luis Cazares - Thursday, January 4, 2018 11:45 AM

  gary1 - Thursday, January 4, 2018 10:59 AM

  HI,
  We have 100 databases and need to take tail back up. I'm trying to generate sql script for all 100 databases. Can you please advise?

  BACKUP LOG DB1 TO DISK = N'Z:\DR\Taillog_Backup\DB1.trn'
  WITH NORECOVERY 
  GO

  I'm just getting name but not sure how to include the path 

  SELECT 'Backup log '''+NAME+'''' FROM sys.databases

  You just need to keep building the string.
  
SELECT 'BACKUP LOG ' + QUOTENAME(name) + ' TO DISK = N''Z:\DR\Taillog_Backup\' + name + '.trn''
WITH NORECOVERY
GO'
FROM sys.databases


  Thanks so much.
  How to put the GO statement in next line?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 4, 2018 at 3:41 pm

  #1974567

  I'd add a where clause to the query to filter out any databases that happen to be using the simple recovery model.

• gary1

  SSC-Insane

  Points: 21152

  More actions

  January 4, 2018 at 3:52 pm

  #1974570

  Lynn Pettis - Thursday, January 4, 2018 3:41 PM

  I'd add a where clause to the query to filter out any databases that happen to be using the simple recovery model.

  I used this:

  SELECT 'BACKUP LOG ' + QUOTENAME(primary_database) + ' TO DISK = N''Z:\DR\Taillog_Backup\' + primary_database + '.trn''
  WITH NORECOVERY
  GO'
  FROM msdb.dbo.log_shipping_primary_databases

• HappyGeek

  SSCoach

  Points: 18729

  More actions

  January 4, 2018 at 11:21 pm

  #1974612

  Lynn Pettis - Thursday, January 4, 2018 3:41 PM

  I'd add a where clause to the query to filter out any databases that happen to be using the simple recovery model.

  +1 and also a clause filtering for ONLINE.

  ...