Pages

Thursday, September 24, 2015

SQLServer Database Backup

=============================================
Emergency Operation - Truncate Transaction Log
=============================================
In case the TX Log has grown in size, until it occupies 100% of the disk, it is needed to Truncate the log file.
This is an emergency operation, as the truncated data is not being backed up.
It is a (VERY) good idea to perform a full backup, once the TX Log Truncate operation has finished.


Steps:
1. Move the database Recovery Model to Simple

    SQL Server Management Studio -> Server -> Databases -> Select Database -> Properties -> Options -> Recovery Model -> Change from Full to Simple

use jiradbdev
GO


ALTER DATABASE jiradbdev SET RECOVERY FULL;
GO

2. Truncate/Shrink the TX Log
Only for SQL Server 2008 and older
BACKUP LOG jiradbdev WITH TRUNCATE_ONLY



For SQL Server 2012 and newer
Shrink the Transactions Log to 1Gb

DBCC SHRINKFILE(N'jiradbdev_log',1000)
GO



3.

Change back, the Recovery Model to Full
ALTER DATABASE jiradbdev SET RECOVERY FULL;
GO


=============================================
Why the Transactions Log is growing big?
=============================================
Common causes:

- Open Transactions (i.e. Uncommited Transactions)
   To check current status of open transactions run DBCC OPENTRAN

- Very Big Transactions
   Log records in the transaction log files are truncated on a transaction-by-transaction basis.
   If the transaction scope is large, that transaction and any transactions started after it are not removed from the transaction log unless it is completed.
   This can result in large log files.

- Index Rebuild
  When Running DBCC DBREINDEX and CREATE INDEX

-Client applications do not process all results
   If you issue a query to SQL Server and you do not handle the results immediately, you may be holding locks and reducing concurrency on the server. 
   SQL Server is reading data in Pages, with shared lock on each page. 
   The shared lock is held until the client requests all of the data.

- In the Replication Setup
   The transaction log size of the publisher database can expand if you are using replication.
   Transactions that affect the objects that are replicated are marked as "For Replication."
   These transactions, such as uncommitted transactions, are not deleted after checkpoint or after you back up the transaction log until the log-reader task copies the transactions to the distribution database and unmarks them.
=============================================
Keep The Transaction Log size under control
=============================================
Some Theory

The oldest log record (referred to as the MinLSN record) that is still required for a successful database wide rollback, or is still required by another activity or operation in the database marks the start of the active log.

The MinLSN log record could be the one marking the start of the oldest open transaction, or the oldest log record still required for a log backup, or the oldest log record still required by another database process, such as database backup, mirroring or transactional replication.

This explains why a long-running uncommitted transaction or an application leaving "orphaned transactions" in the database can keep large areas of the log "active" and so prevent log truncation.

For a FULL and BULK_LOGGED recovery model database, only a log backup will result in truncation of the log (i.e. enable reuse of space in the log). 

When a log backup occurs, SQL Server can reuse the space in any portions of the log that are marked as "inactive". 

To free up space claimed by TX Log to the host OS, need to run DBCC SHRINKFILE command.

Reference
Managing the SQL Server Transaction Log
Why is my transaction log full?


Actual commands to execute:

1. Truncate the TX Log

BACKUP LOG my_database WITH TRUNCATE_ONLY


3. Shrink the Transactions Log to 2Gb

DBCC SHRINKFILE(my_database_log,2000)




=============================================
Create a database backup 
=============================================
From SQL Server Management Studio:
Databases -> Select DB -> Right Click -> Tasks -> Back Up -> Backup Dialog Window


In Backup Dialog Window Select:
Backup type = Full.Backup component, = Database.
Copy Only Backup = check.
This is useful if the backup is out of sequence for regular backups
Per Microsoft Documentation: Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.

Destination = Select the path/name.bak 
   There might be a limit to backup to NFS, In this case backup to local disk, then move to another destination.

In T-SQL:
BACKUP DATABASE [mydb] TO  DISK = N'D;\Backups\mydb_01.bak' WITH NOFORMAT, NOINIT,  NAME = N'mydb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


=============================================
Change backup destination
=============================================
Open up SQL Server Management Studio.
In the Object Explorer pane, right-click your database instance and click Properties.
On the left, select the Database Settings page.
Change the Backup entry to the desired backup folder, then click OK.

Restart the SQL Server service.



=============================================
SQL Serer simple Backup script
=============================================
Inside SQL Server Ajent, define a new job with several steps:

master_db_backup
BACKUP DATABASE [master] TO  DISK = N'C:\SQL_SERVER_BACKUP\FULL\master_db.bak' WITH NOFORMAT, NOINIT,  NAME = N'master-Full Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

master_db_jiradbdev
BACKUP DATABASE [jiradbdev] TO  DISK = N'C:\SQL_SERVER_BACKUP\FULL\jiradbdev_db.bak' WITH NOFORMAT, NOINIT,  NAME = N'msdb-Full Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

In addition, define a new schefuled task to move backups from default location and delete old backups.

move_to_history.bat
ECHO OFF
REM =========================================
REM Keep up to N backups per database
REM =========================================

SET WORK_DIR=C:\SQL_SERVER_BACKUP
SET FULL_BACKUP_DIR=%WORK_DIR%\FULL
SET LOG_BACKUP_DIR=%WORK_DIR%\LOG


cd %WORK_DIR%\FULL

REM ===================
REM master
REM ===================
MOVE %FULL_BACKUP_DIR%\master_db.bak_03 %FULL_BACKUP_DIR%\master_db.bak_04
MOVE %FULL_BACKUP_DIR%\master_db.bak_02 %FULL_BACKUP_DIR%\master_db.bak_03
MOVE %FULL_BACKUP_DIR%\master_db.bak_01 %FULL_BACKUP_DIR%\master_db.bak_02
MOVE %FULL_BACKUP_DIR%\master_db.bak %FULL_BACKUP_DIR%\master_db.bak_01

REM ===================
REM model
REM ===================
MOVE %FULL_BACKUP_DIR%\model_db.bak_03 %FULL_BACKUP_DIR%\model_db.bak_04
MOVE %FULL_BACKUP_DIR%\model_db.bak_02 %FULL_BACKUP_DIR%\model_db.bak_03
MOVE %FULL_BACKUP_DIR%\model_db.bak_01 %FULL_BACKUP_DIR%\model_db.bak_02
MOVE %FULL_BACKUP_DIR%\model_db.bak %FULL_BACKUP_DIR%\model_db.bak_01

REM ===================
REM msdb
REM ===================
MOVE %FULL_BACKUP_DIR%\msdb_db.bak_03 %FULL_BACKUP_DIR%\msdb_db.bak_04
MOVE %FULL_BACKUP_DIR%\msdb_db.bak_02 %FULL_BACKUP_DIR%\msdb_db.bak_03
MOVE %FULL_BACKUP_DIR%\msdb_db.bak_01 %FULL_BACKUP_DIR%\msdb_db.bak_02
MOVE %FULL_BACKUP_DIR%\msdb_db.bak %FULL_BACKUP_DIR%\msdb_db.bak_01


REM ===================
REM jiradbdev
REM ===================
MOVE %FULL_BACKUP_DIR%\jiradbdev_db.bak_03 %FULL_BACKUP_DIR%\jiradbdev_db.bak_04
MOVE %FULL_BACKUP_DIR%\jiradbdev_db.bak_02 %FULL_BACKUP_DIR%\jiradbdev_db.bak_03
MOVE %FULL_BACKUP_DIR%\jiradbdev_db.bak_01 %FULL_BACKUP_DIR%\jiradbdev_db.bak_02

MOVE %FULL_BACKUP_DIR%\jiradbdev_db.bak %FULL_BACKUP_DIR%\jiradbdev_db.bak_01



--Backup Logs

BEGIN
  DECLARE  @backup_file        varchar(1000); 
  DECLARE  @BACKUP_LOG_FILDER  varchar(30) = 'C:\SQL_SERVER_BACKUP\LOG\';
  DECLARE  @DB_NAME            varchar(100) = 'jiraprod_new';
  DECLARE  @LOG_NAME            varchar(100) = 'jiraprod_new_log';

  SELECT @backup_file =   @BACKUP_LOG_FILDER + 'LOG_'+@DB_NAME+'_'+(FORMAT ( GETDATE(), 'yyyyMMdd_HHmm'))+'.trn';
  --PRINT @backup_file;  
  BACKUP LOG @DB_NAME TO DISK = @backup_file WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 25;
  USE jiraprod_new;
  DBCC SHRINKFILE (@LOG_NAME, 1000);

END;

No comments:

Post a Comment