All DB 백업

백업

전체 DB를 백업하는 간단한 스크립트.

저장 프로시저로 만들어서 Job 으로 이용해도 됨.

 

Simple script to backup all databases

Problem
Sometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks.  One of these tasks may be the need to backup all databases on your server.   This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance of SQL Server.  You could use Enterprise Manager to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.

Solution
With the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one.  This is a very straight forward process and you only need a handful of commands to do this.

Here is the script that will allow you to backup each database within your instance of SQL Server.  You will need to change the @path to the appropriate backup directory and each backup file will take on the name of "DBnameYYYDDMM.BAK".

1.  DECLARE @name VARCHAR(50) -- database name 
   DECLARE @path VARCHAR(256) -- path for backup files 
   DECLARE @fileName VARCHAR(256) -- filename for backup 
   DECLARE @fileDate VARCHAR(20) -- used for file name
   
   SET @path = 'C:\Backup\' 
   
   SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
   
   DECLARE db_cursor CURSOR FOR 
   SELECT name 
   FROM master.dbo.sysdatabases 
   WHERE name NOT IN ('master','model','msdb','tempdb') 
   
   OPEN db_cursor  
   FETCH NEXT FROM db_cursor INTO @name  
   
   WHILE @@FETCH_STATUS = 0  
   BEGIN  
          SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
          BACKUP DATABASE @name TO DISK = @fileName 
   
          FETCH NEXT FROM db_cursor INTO @name  
   END  
   
   CLOSE db_cursor  
   DEALLOCATE db_cursor

 

In this script we are bypassing the system databases, but these could easily be included as well.  You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases.  Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

 

Automating Transaction Log Backups for All Databases

Problem
Maintenance plans are a great thing, but sometimes the end results are not what you expect.  The whole idea behind maintenance plans is to simplify repetitive maintenance tasks without you having to write any additional code.  For the most part maintenance plans work without a problem, but every once in awhile things do not go as planned. Two of the biggest uses of maintenance plans are issuing full backups and transaction log backups.  What other approaches are there to issue transaction log backups for all databases without using a maintenance plan?

Solution
With the use of T-SQL you can generate your transaction log backups and with the use of cursors you can cursor through all of your databases to back them up one by one. With the use of the DATABASEPROPERTYEX function we can also just address databases that are either in the FULL or BULK_LOGGED recovery model since you can not issue transaction log backups against databases in the SIMPLE recovery mode.

Here is the script that will allow you to backup the transaction log for each database within your instance of SQL Server that is either in the FULL or BULK_LOGGED recovery model.

You will need to change the @path to the appropriate backup directory and each backup file will take on the name of "DBname_YYYDDMM_HHMMSS.TRN".

1. DECLARE @name VARCHAR(50) -- database name  
   DECLARE @path VARCHAR(256) -- path for backup files  
   DECLARE @fileName VARCHAR(256) -- filename for backup  
   DECLARE @fileDate VARCHAR(20) -- used for file name 
   
   SET @path = 'C:\Backup\'  
   
   SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
      + '_' 
      + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
   
   DECLARE db_cursor CURSOR FOR  
   SELECT name 
   FROM master.dbo.sysdatabases 
   WHERE name NOT IN ('master','model','msdb','tempdb') 
      AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED')
   
   OPEN db_cursor   
   FETCH NEXT FROM db_cursor INTO @name   
   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   
          SET @fileName = @path + @name + '_' + @fileDate + '.TRN'  
          BACKUP LOG @name TO DISK = @fileName  
   
          FETCH NEXT FROM db_cursor INTO @name   
   END   
   
   CLOSE db_cursor   
   DEALLOCATE db_cursor  

 

In this script we are bypassing the system databases, but these could easily be included as well. You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases. Any way you choose to use it, this script gives you the starting point to simply backup all of your databases. 

이 글은 스프링노트에서 작성되었습니다.