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;