This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Suppose I have a Database maintenance plan which runs every 15 minutes to take the Transaction Logs backup of all user defined databases. One of the members of DBA team created a new database in the morning at 09:10 AM and the DB maintenance job started failing. What could be the reason?
This job is failing because the we did not take a full database backup of the newly created database. We need to a full backup of a database to initiate the log chain.
Is it possible to restore a Database backup of SQL Server 2012 to SQL Server 2008 /2008 R2?
No it’s not possible to restore the upper version database backup to lower version.
What is RESTORE LABELONLY option?
It returns a result set containing information about the backup media identified by the given backup device.
T-SQL script for RESTORE LABELONLY.
|
1 |
RESTORE LABELONLY FROM DISK='D:\MyDrive\Adv_Full.bak' |
What is a RESTORE HEADERONLY OPTION?
It is used to check the content of a backup file.
T-SQL Script for RESTORE HEADERONLY.
|
1 2 |
RESTORE HEADERONLY FROM DISK = N'C:\AdventureWorks2012.bak' |
How to remove all backup history from the related system tables?
Using this system stored procedure – sp_delete_backuphistory.
What are the permissions required to perform backup and Restore?
Backup:
sysadmin – fixed server role
db_owner – fixed database role
db_backupoperator – fixed database role
Restore:
Sysadmin – fixed server role
Dbcreator – fixed server role
db_owner – fixed database role
You are taking full database backup, differential database backups, and transaction log backups. If your system is crashed and some of the differential database also crashed, can you recover the database to the current point in time with out differential backups.
Yes,
You can recover to the current point in time, as long as you have all the transaction log backups available and they are all valid. Differential backups do not affect the transaction log backup chain.
What is BACKUP WITH TRUNCATE_ONLY option?
TRUNCATE_ONLY doesn’t back up the transaction log; it simply forces SQL Server to take a checkpoint, which then truncates the log, getting rid of inactive entries and shrinking the size of the log file.
What is the meaning of RetainDays Property?
The RetainDays property specifies the number of days that must elapse before a backup set can be overwritten.
If your database size is 800 GB and daily 6000 transactions are running, what should be your backup plan.
- Weekly Full backup
- Daily Differential backup
- After every 30 min Transaction log backup
What kind of common issues with Log Restores?
- an incomplete series of log backup files.
- a missing full backup database file.
- minimally logged transactions in a log file, when database is operating in the BULK_LOGGED recovery model.
Can I restore a backup onto a different version of SQL Server?
You can restore to a different version of SQL Server, but you can only restore upwards. In other words, you can restore from 2000 to 2005 or from 2005 to 2008R2 or from 2008 to 2012, but you can never restore in the reverse direction.
How to make your database into single user mode?
|
1 |
ALTER DATABASE Database_Name SET SINGLE_USER; |
How to make your database into multi user mode?
|
1 |
ALTER DATABASE Database_Name SET MULTI_USER; |
How to make your database into emergency mode?
|
1 |
ALTER DATABASE Database_Name SET EMERGENCY; |
How you estimate the size of full database backup?
You can estimate the size of a full database backup by using the sp_spaceused system stored procedure.
What is the BACKUP WITH EXPIREDATE option?
The EXPIREDATE option to determine when the SQL Server Database Engine can overwrite the backup.
T-SQL script to take database backup WITH EXPIREDATE.
|
1 2 3 |
BACKUP DATABASE AdventureWorks2012 TO DISK = 'D:\AdventureWorks2012.Bak' WITH EXPIREDATE = '08/08/2016' ; |
We have a production server containing three actively-used databases. The application owner informs us that no more than two hours of data loss
can be tolerated, in the event of corruption or any other disaster. None of the databases are complex structurally, each containing just one data file and one log file and each database operating in the FULL recovery model.
What should be your database backup plan in the above scenario?
- Perform full nightly database backups for every database (plus the system databases).
- Perform log backups on the user databases every 2 hours, on a schedule starting after the full backups are complete and ending before the full backup jobs starts.
We have a production database system that contains three databases with complex data structures. Each database comprises multiple data files split into two filegroups, one read-only and one writable. The read-only file group is updated once per week with newly archived records. The writable file groups have an acceptable data loss of 1 hour. Most database activity on this server will take place during the day and each database operating in the FULL recovery model.
What should be your database backup plan in the above scenario?
- Perform nightly full database backups for all system databases.
- Perform a weekly full file backup of the read-only filegroups on each user database, after the archived data has been loaded.
- Perform nightly full file backups of the writable file groups on each user database.
- Perform hourly log backups for each user database; the log backup schedule should start after the nightly full file backups are complete, and finish one hour before the full file backup processes start again.
