This article is half-done without your Comment! *** Please share your thoughts via Comment ***
How to turn off logging of all successful backups in your SQL Server error logs?
Using trace flag 3226.
|
1 |
DBCC TRACEON (3226,-1) |
T-SQL script to take a database backup.
|
1 |
BACKUP DATABASE AdventureWorks2012 TO DISK='e:\advwork.bak' |
Backup to multiple files using T-SQL.
|
1 2 3 4 |
BACKUP DATABASE AdventureWorks2012 TO DISK='e:\advwork.bak' ,DISK='d:\advwork.bak' ,DISK='f:\advwork.bak' |
T-SQL script to restore a database.
|
1 |
RESTORE DATABASE AdventureWorks2012 FROM DISK='e:\advwork.bak' |
T-SQL script to set RECOVERY FULL model.
|
1 |
ALTER DATABASE dbrnd_DB SET RECOVERY FULL; |
T-SQL script to take Database Log backup.
|
1 |
BACKUP LOG dbrnd_DB TO DISK = 'd:\dbrnd\dbrnd_tran01.trn' WITH retaindays = 3 |
T-SQL script to restore a multiple transaction log backups.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
RESTORE DATABASE AdventureWorks2012 FROM AdventureWorks2012_1 WITH NORECOVERY; GO RESTORE LOG AdventureWorks2012 FROM AdventureWorks2012_log WITH FILE = 1, NORECOVERY; GO RESTORE LOG AdventureWorks2012 FROM AdventureWorks2012_log WITH FILE = 2, WITH NORECOVERY; GO RESTORE LOG AdventureWorks2012 FROM AdventureWorks2012_log WITH FILE = 3, WITH NORECOVERY; GO RESTORE DATABASE AdventureWorks2012 WITH RECOVERY; GO |
Consider a situation where database backup size is 80 GB and you have three different disks and each has 30 GB free space.
Now the question is, can you split your database backups?
Yes, it is possible. We can split the backup files into different places and the same can be restored.
|
1 2 3 4 5 6 7 8 9 |
BACKUP DATABASE AdventureWorks TO DISK = ‘D:\AdventureWorks1.bak’, DISK = ‘E:\AdventureWorks2.bak’, DISK = ‘F:\AdventureWorks3.bak’ RESTORE DATABASE [AdventureWorks] FROM DISK = ’D:\Backup\MultiFile\AdventureWorks1.bak’, DISK = ’E:\AdventureWorks2.bak’, DISK = ’F:\AdventureWorks3.bak’ |
We have configured every Sunday 11pm FULL backup. Every 11pm differential backups and every 1h.r transaction log backups.
The Database was failed at 11.30 pm on Friday. Then what are the database recovery steps?
- Take Tail log backup to get 11-11.30pm transactions on Friday.
- Restore last Sunday full backup with NO_RECOVERY.
- Restore Friday 11pm differential backup with NO_RECOVERY.
- Restore tail log backup with RECOVERY.
What is File or File Group backup?
Using the file or filegroup backup you can backup an entire filegroup or files within the filegroup. These backups are essential when the database size is so large that backups must be done in parts because it takes too long to backup the entire database.
T-SQL Script to take File Group backup.
|
1 2 |
BACKUP DATABASE AdventureWorks2012 FILEGROUP='PRIMARY', FILEGROUP ='Secondary' TO DISK ='D:\AdventureWorks2012_FileGroup.bak' |
What is Mirrored backup?
Mirrored database backups can be used to create multiple copies of the database backups on different locations.
T-SQL Script to take Mirrored backup.
|
1 2 3 |
BACKUP DATABASE AdventureWorks2012 TO DISK = ‘C:\AdventureWorks2012.bak’ MIRROR TO DISK = ‘D:\AdventureWorks2012_mirror.bak’ |
What is a log chain?
A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time or after the recovery model is switched from simple recovery to full or bulk-logged recovery.
Is it possible in any situation when differential backup grows more than the Full backup?
Yes, it is possible in case when you do not take Full backup of the database for months and change in the databases grow more than the size of the Full backup.
Is it mandatory to take a Full backup if we switch the recovery model of a database?
Yes, It is mandatory to take a Full backup of the database after switching the recovery model of the database to initiate the log chain. Otherwise Diff. or Transaction logs will fail.
What is the below error?
Msg 3023, Level 16, State 2, Line 1
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
This error occurs when we try to run a backup, shrink, or alter database command in SQL Server in parallel.
Can you take differential backup of Master Database?
No, differential backup of master database is not allowed.
Can you take backup of a database which is in emergency mode?
We can’t take the database backup if the database is in emergency mode.
SQL Server has native backup. Why pay money for a tool to do it?
SQL Server backups work extremely well, but you’re going to have to do some work to get them set up and even more to get them automated.
A good third party product will make this automation process very simple.
For example, some tools more efficient at compressing backups, saving even more disk space and time during your backup processes.
