How to show backup or restore progress to user in a progressbar?

You can show backup or restore progress to user in a progress bar if you had installed 

sp_who2k5 

into your master database. You have to run the below command:

sp_who2k5 1, 1

The result will include all active transaction. Search the backup in the requestCommand field. The aptly named percentComplete field will give you the progress of the backup. 

Backup file information

Below script shows you all database backup files. 

It shows you Database Name, backup done by which user, backup file location, backup start date time and end date time and backup file size.

SELECT database_name, user_name as ExecutedBy, physical_device_name,
backup_start_date, backup_finish_date, backup_size
FROM msdb..backupset bckset
INNER JOIN msdb..backupmediafamily bckfmly
ON bckset.media_set_id = bckfmly.media_set_id

Script to backup all database in a Server

Use below script to take backup of all Database in a SQL Server:



Set Nocount on
Declare @Database_Name varchar(100)
Declare @Server_Name varchar(100)
declare @d varchar(30)


Declare Cur_DB Cursor For
        -- ALL USER DATABASES
        Select name from master.dbo.sysdatabases
        where dbid > 4 


Declare @osql varchar(1000)


select @Server_Name = @@Server_Name
select @d = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), GETDATE(), 120), '-', ''), ':', ''), ' ', '_')


Open Cur_DB
Fetch Next from Cur_DB into @Database_Name
While @@Fetch_status=0
    Begin
        Set @osql='EXEC master.dbo.xp_cmdshell '+''''+'sqlcmd -E -S '+@Server_Name+' -Q"BACKUP DATABASE ['+@Database_Name+'] TO disk = ''''\\filer\SQL_BACKUPS_SHARE\myserver\UserDBs\'+@Database_Name+'_' + @d + '.bak'''' " -o"C:\SQLLogs\Agent Jobs\Backup User Databases - '+@Database_Name+'.log"'+''''
        EXEC (@osql) --Execute the osql statement
        Fetch Next from Cur_DB into @Database_Name
    End
Close Cur_DB


Deallocate Cur_DB

Error during Backup

Sometime during backup below error comes.

Error Number: 18272

Error Message: I/O error on backup or restore restart-checkpoint file '%1'. Operating system error %2. The statement is proceeding but is non-restartable.

This generally happens when SQL Server is not able to find the default backup directory. SQL Server needs to create a checkpoint file when a BACKUP or RESTORE operation happens. This happens when the RESTART option is specified. WITH RESTART tells SQL Server to start from the point where the operation was last aborted. When SQL Server searches for the file and its not available then this error is thrown.

How to avoid this error:

1. Check the default backup directory for the SQL Server and see if exists, if not create it.
2. Verify if the backup directory has enough space in it.
3. Verify if SQL Server service account has permissions to read and write into that directory

Find Estimated time for Backup and Restore

Use below script to find Estimated time for Backup and Restore:


SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [PERCENT Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GETDATE()),20) AS [ETA COMPLETION TIME],
CONVERT(NUMERIC(6,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0) AS [ETA MIN],
CONVERT(NUMERIC(6,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(100),(SELECT SUBSTRING(TEXT,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Incremental Backup

An Incremental Backup is nothing but a Differential Backup.  In sql server if anything modified after taking the full backup it will be recorded in page as 1(indication symbol) in differential change map(DCM). Those ones are never changed until u take the full backup again. So last differential backup enough for restore because it's having the all the increments since the last full backup.


An incremental backup contains all the changes since the last incremental backup. Incremental backups are additive - you have to restore all of them to get back to where you started. SQL Server does not support true incremental backups (I mean just pages which have changed since the last incremental). Incremental (transaction) backup is good up to the last incremental or full backup. 


Process to do a Differential Backup..


1. Right click on you DB in SSMS
2. click on "Tasks" then -> "Backup"
3. In the Backup Type select 1) Full or 2)Differential.
4. Configure all other settings like file location etc....


You can also do this in SSIS using a Backup Database Task Step.

How to take backup of data if the system got corrupted?

You may require emergency data backup in following situations

1. The OS is corrupt or system is not booting
2. The OS is running but FusionRetail 6 is not starting up
3. Microsoft SQL Server Management Studio Express is not present.
4. You do not know how to use SQL Server Management Studio Express to take backup.

Please use the following steps to take emergency backup of data in case of emergency
First we have to stop the SQL Server if that is running. For stopping the SQL use the following step.

• Go to Start > Run > Type net stop mssql$

• Copy the data directory of the database. (Usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data)
• Make sure that the data files with extension .MDF and .LDF is present

** Once You have copied the above files to another computer or any safe place…

• After Installing SQL Server, Use "Attach Database" option in Management Studio to attach this MDF file.