Create automatic full backup

Todas las respuestas

• viernes, 29 de octubre de 2010 18:32

   

   
  

  0

  Inicie sesión para votar

  
  Hi,

  You can use a Job to check if there is any changes in Database objects or if there's a new Database. If true then you can run the Backup from the Job you created. Also you can use create a Maintenance Plan.

  As you may know SQL Server Agent  is a Microsoft Windows service that executes scheduled administrative tasks, so you can use it to perform that kind of jobs.

   

  ---

  Willy Taveras.-

  • Marcado como respuesta Alex Feng (SQL)Microsoft Community Contributor, Moderator lunes, 08 de noviembre de 2010 9:57
  •  
  • Responder
  • Citar

   
• viernes, 29 de octubre de 2010 21:02

   

   
  

  0

  Inicie sesión para votar

  Op 29-10-2010 18:16, DBAWizard schreef:

  Our maintenance plans do a full backup on Sunday, Diff backups nightly and log backups every 4 hours.  However, if the users bring a new database online during the week, it will fail every backup until the full backup is taken.
  
  Is there any way to detect the creation of a new database and force a "benchmark" full backup to be taken?  I should say any simple way; I'm quite sure I'll wind up writing some sort of proc to run under the Agent and look for new arrivals, but I wanted to see if anyone else has a simpler solution.
  
  Thanks in advance!

  Hi DBAWizard,

  You could write a DDL trigger to fire on database creation and initiate a backup. This should not be done directly, though. The trigger runs synchronously, and you don't want the CREATE DATABASE command to stall until the backup is finished.

  You could use Service Broker. The trigger would send a message on a queue; the queue would auto-activate (this starts a procedure in the background, that the trigger will not wait for) the database backup.

  Another possibility would be to have the trigger create an Agent job for the full backup and schedule it for a single execution. You'll need to refer to Books Online to find the stored procedures to create an Agent job programmatically. And I'm not sure if such a job would delete itself after execution, if not you'll end up with loads of auto-generated jobs in Agent.

  ---

  Hugo Kornelis
  SQL Server MVP

  • Marcado como respuesta Alex Feng (SQL)Microsoft Community Contributor, Moderator lunes, 08 de noviembre de 2010 9:57
  •  
  • Responder
  • Citar

   
• martes, 10 de mayo de 2011 13:32

   

   
  

  0

  Inicie sesión para votar

  I have one question. What happens when, for some reason, queue time to activate message is passed? Some of the reasons could be server is not active, or something like that. Then message queue never starts. Is this right?

  This could be big problem. How could it be solved?

  Thanks in advance...

  • Responder
  • Citar

   
• jueves, 12 de mayo de 2011 12:04

   

   
  

  0

  Inicie sesión para votar

  it might help you..Change backup type as per your need...Ie full,diff, and transaction.

   

  SET

  NOCOUNT ON

  declare

  @count int, @max int, @day varchar(12), @BackupPath varchar(1000), @dbname varchar(55

  )

  declare

  @device varchar(255), @Vch_FileName varchar(1255

  )

   

  set

  @BackupPath='D:\'

  ------SEt backu path

  if

  right(@BackupPath,1) <> '\'

  select

  @BackupPath = @BackupPath+

  '\'

   

  set

  @day= convert(char(8), getdate(), 112)+''+ Replace(convert(char(12), getdate(), 14),':','')

   

  DECLARE

  @databases

  TABLE

  (

  ID

  INT IDENTITY ( 1, 1 ) 

   

  , DatabaseName SYSNAME 

  )

  INSERT

  INTO

  @databases

   

  SELECT name

   

  FROM master.dbo.sysdatabases

   

  WHERE name not IN ( 'master', 'msdb','model','tempdb') 

  DECLARE

  @id

  TINYINT

  SELECT

  @id = MIN ( ID ) FROM @databases

  WHILE

  @id IS NOT NULL

  BEGIN

   

  DECLARE @databaseName SYSNAME 

   

  SELECT @databaseName = DatabaseName FROM @databases WHERE ID = @id

   

  DECLARE @backupFileName VARCHAR(512) 

   

  SELECT @backupFileName = @BackupPath+replace (@@servername, '\', '$')+'_'+@databaseName+'_Full_' + @day +'.BAK' 

   

   

   

   

  exec('BACKUP DATABASE ['+@databaseName+'] TO DISK = '''+@backupFileName+''' with compression') 

   

   

  DELETE FROM @databases WHERE ID = @id 

  SELECT

  @id = MIN ( ID ) FROM @databases

  END

   

   

  ---

  Regards Madan Agrawal Please vote if you find my post valuable for you.
  • Responder
  • Citar