Database Backup And Restore

Tables:- msdb.dbo.backupset , msdb.dbo.backupmediafamily
Stored Procedures:-

DMVs :-

DMFs:-

BACKUP DATABASE AdventureWorks 
TO DISK = 'C:\DBBackups\AdventureWorks.BAK'
WITH NOFORMAT, NOINIT, 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 25 

/* Verify the Database Backup File Once the Backup has completed successfully */

RESTORE VERIFYONLY
FROM DISK = 'C:\DBBackups\AdventureWorks.BAK'
GO

SQL Server Copy Only Backup-2005

The backup of database as a single file without the breakup of Backup chain or without knowing the complexity of Production server backup strategy.

No LSN genereated.

Backup Database TEST to Disk=’D:\TEST_FULL.bak’ WITH COPY_ONLY 

DATABASE Backup in terms of Percent_Complete

select percent_complete,* from sys.dm_exec_requests where command='BACKUP DATABASE'

 To Find out the database restore information

Use msdbGOSelect restore_date, destination_database_name, user_name  from restorehistory

 Where restore_date = date and time of restorationDestination_database_name = name of destination database on which restore performUsername = Name of user who perform the restore operation 

Database Details

SELECT
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / (1000000*1028) AS numeric(18,2)) AS VARCHAR(14)) + ' ' 

+ 'GB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
--CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
--CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE --s.database_name = DB_NAME() -- Remove this line for all the database
backup_start_date>CONVERT(DATE, GETDATE()) and s.type='D"
ORDER BY backup_start_date DESC --, backup_finish_date

Verified Backup

RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK
GO
RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK WITH FILE = 2
GO

Backup Status

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','RESTORE DATABASE')

RESTORE IN HA

HA is very similar to mirroring and uses similar technology, just not nearly as finicky. You will want to treat your HA DBs similarly as well.

Code would be similar to the following:

--on primary

ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE AdventureWorks2012;

--on primary

RESTORE DATABASE AdventureWorks2012

   FROM AdventureWorksBackups

   WITH NORECOVERY, 

      MOVE 'AdventureWorks2012_Data' TO 

'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf', 

      MOVE 'AdventureWorks2012_Log' 

TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.

RESTORE LOG AdventureWorks2012

   FROM AdventureWorksBackups

   WITH RECOVERY;

--on secondary

RESTORE DATABASE AdventureWorks2012

   FROM AdventureWorksBackups

   WITH NORECOVERY, 

      MOVE 'AdventureWorks2012_Data' TO 

'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf', 

      MOVE 'AdventureWorks2012_Log' 

TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';

RESTORE LOG AdventureWorks2012

   FROM AdventureWorksBackups

   WITH NORECOVERY;

--on primary

ALTER AVAILABILITY GROUP MyAG ADD DATABASE AdventureWorks2012;

--on secondary

ALTER DATABASE AdventureWorks2012 SET HADR AVAILABILITY GROUP = MyAG;