Database Full Backup Script – Sql Server

Following will run full backup of of specified database to specified folder. This script will run copy_only backup with compression. It also includes setting for BUFFERCOUNT and MAXTRANSFERSIZE. 

Backup file will be in “databaseName_backup_yyyy_mm_dd_hhminss.bak” format.


DECLARE @backupfile varchar(100),
@db_name varchar(100), @backup_folder varchar(500);
SET @db_name = 'databaseName';
SET @backup_folder = 'backupLocation';
SET @backupfile = N'' + @backup_folder + @db_name + '_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar(50), getdate(), 20), ' ', '_'), '-', '_'), ':', '') + N'.bak';

DECLARE @backup_cmd nvarchar(4000)
SET @backup_cmd = 'BACKUP DATABASE ' + @db_name + ' TO DISK=''' + @backupfile + ''' WITH COPY_ONLY, FORMAT, INIT,MEDIANAME = N''sql_jobs'',
NAME = N''sql_jobs-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS=5, CHECKSUM, BUFFERCOUNT=100, MAXTRANSFERSIZE=2194304'

EXEC [sp_executesql] @backup_cmd

Get row count of table – SQL Server

There are times when as a DBA or developer you want to get the count of a table. To get the current count you can simply do:
SELECT COUNT(*) FROM [table_name]

Which works fine, when tables are small or medium size, but for large table (10 millions rows or above) this can take a while to return. If the count is for getting a “close enough” number, then sys.partitions can be used for getting this count quickly. sys.partitions keeps track of indexes for each table and rows contain within each index. The row count may be little off, depending on when the query is executed (pending commits etc), but for a “good enough” number count using sys.partitions is a faster option.

SELECT OBJECT_NAME(object_id), rows FROM sys.partitions WHERE index_id = 1

OBJECT_NAME will return the table name and index_id = 1 will return single row for tables with multiple indexes.

Where are the backup files? – SQL Server

In the previous post we explored backupset table in msdb, which holds data about backups, backup times etc. So now that we have good backups, where are the backup files? We can query backupmediafamily table for this information. physical_device_name column holds the full path (if saved on disk else the media name) to backup file.

SELECT * FROM msdb.dbo.backupmediafamily

We can also join backupmediafamily to backupset on media_set_id column to get information about relevant backup files.

SELECT physical_device_name, bs.backup_start_date, bs.backup_finish_date FROM msdb.dbo.backupmediafamily bf
INNER JOIN msdb.dbo.backupset bs ON bf.media_set_id = bs.media_set_id
WHERE 1=1
AND bf.media_set_id = 18518

Get information about backups – SQL Server

backupset table in msdb can be used to get information about database backups. This table holds information about of type of backup being done, recovery_model, backup_size, backup_start_date, backup_finish_date etc.

Full backups are of type ‘D’ (Database Backup), log backups are type ‘L’ (Log Backup) and incremental are of type ‘I’ (Incremental Backup).

To get full backup details sorted by backup finish time:
SELECT * FROM msdb.dbo.backupset
WHERE 1=1
AND type = 'D'
ORDER BY backup_finish_date DESC

Results can be filtered based on database by including database name like this:
SELECT * FROM msdb.dbo.backupset
WHERE 1=1
AND type = 'D'
AND database_name = ''
ORDER BY backup_finish_date DESC

Get Status of SQL Agent Jobs – SQL Server

There are couple of tables (sysjobs, sysjobhistory) in msdb database which hold information about SQL Agent jobs.
sysjobs table contains: name, enabled, notify information, date_created… etc
sysjobhistory table contains:run_status, run_date, run_time, server… etc

Script below will return jobName, message, enabled, run_status, run_date. Run_status: 0=Failed, 1=Succeeded, 2=Retry, Canceled=3, Steps within jobs=4

SELECT DISTINCT SERVERPROPERTY('ServerName') AS [ServerName],
j.name AS jobName, jh.message, j.enabled, jh.run_status, jh.run_date FROM msdb.dbo.sysjobs j
INNER join msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE 1=1
AND enabled = 1

List of backup details – Oracle Database

To get list of full backup from Oracle database run the script below, which uses the rman_backup_job_details system view, this list all backups in database with archivelog mode on and backup size of at least 1 Terabyte.

SELECT * FROM v$rman_backup_job_details
WHERE input_type = 'DB INCR'
AND output_bytes_display LIKE '%T'
ORDER BY session_key DESC;

Find out time left in backup/restore – SQL Server

Sometimes you want to find out how soon the backup or restore will finish. We can use sys.dm_exec_requests table to find out that information. sys.dm_exec_requests displays all request waiting to be executed or are currently executing. Cross joining the sys.dm_exec_sql_text table will give the query text in the queue. The time shown in estimated_completion_time are estimates hence estimated in the name, but relatively accurate and gives the user a general idea of how long the backup or restore will take.

Query about full backup:

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in (‘BACKUP DATABASE’)


Query about log backup:

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in (‘BACKUP LOG’)


Query about restore job:

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in (‘RESTORE DATABASE’)

Automate index reorganize – SQL Server

Script below checks for indexes with fragmentation between 5 and 30 percent and with pagecount of > 5000, then inserts those records into temp table and uses it to reorganize each index. Script is very basic and can be improved upon based on user needs.


DECLARE @reorg_table TABLE(
table_name NVARCHAR(35),
index_name NVARCHAR(255))

INSERT INTO @reorg_table
SELECT OBJECT_NAME(ip.object_id) AS table_name, si.name AS index_name FROM sys.indexes si
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ip ON si.object_id=ip.object_idand si.index_id=ip.index_id
WHERE 1=1
AND ip.avg_fragmentation_in_percent BETWEEN 5 AND 30
AND ip.page_count > 5000
ORDER BY OBJECT_NAME(ip.object_id), ip.page_count

DECLARE @table_name NVARCHAR(35),
@index_name NVARCHAR(255),
@reorg_stmnt NVARCHAR(250)

WHILE (SELECT COUNT(*) FROM @reorg_table) > 0

BEGIN

SELECT @table_name = table_name, @index_name = index_name FROM @reorg_table

PRINT @table_name

PRINT @index_name

EXECUTE ('ALTER INDEX ' + @index_name + ' ON ' + @table_name + ' REORGANIZE WITH (LOB_COMPACTION = ON )')

DELETE FROM @reorg_table

INSERT INTO @reorg_table

SELECT OBJECT_NAME(ip.object_id) AS table_name, si.name AS index_name FROM sys.indexes si
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ip ON si.object_id=ip.object_idand si.index_id=ip.index_id
WHERE 1=1
AND ip.avg_fragmentation_in_percent > 5
AND ip.page_count > 5000
ORDER BY OBJECT_NAME(ip.object_id), ip.page_count

END

Fragmentation and rebuild/reorganize of indexes – SQL Server

Fragmentation will happen in database, that’s not news. Why it happens is beyond scope of this post, but for a good explanation look at Paul Randall’s excellent post about fragmentation here.

Microsoft recommends that if fragmentation < 5% or with pages < 1000 then do nothing. According to Paul Randall he made these up, read here. If fragmentation is >5% and <30% than reorganize the indexes, else rebuild.

ALTER INDEX ‘name_index’ ON ‘table_name’ REORGANIZE WITH (LOB_COMPACTION = ON)

During REORGANIZE table is available, hence an online operation.

ALTER INDEX ‘name_index’ ON ‘table_name’ REBUILD WITH (LOB_COMPACTION = ON)

Rebuild normally is offline operation meaning the table will not be available, unless run on enterprise version with ONLINE option.