SQL Server Backup and Restore Ques & Ans

SQL Server DB Backup and Restore Questions & Answers

Q. How many types of Database Recovery Model in SQL Server? How they impact on database backup?
A: There are three types of Recovery Model in SQL Server:
1. Simple:Committed transactions are removed from the log when the check point process occurs.
2. Full:Committed transactions are only removed when the transaction log backup completed.
3. Bulk-Logged:Committed transactions are only removed when the transaction log backup process occurs.

Q. How to check that when the database backup was done and at which location?
A: There are multiple ways to check when the database backup was done:
1. Check Database Properties, 

2. Get the backup & location details from MSDB database under tables: msdb.dbo.backupset & msdb.dbo.backupmediafamily.
---------------------------------------------------------------------------------
--Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
3. Check for the Successful backup entry in SQL Error log

Q. How can you verify database backup media\file is valid?
A: Using RESTORE VERIFYONLY Command, we can validate the backup file.
Syntax: RESTORE VERIFYONLY FROM DISK = 'Path'

Q. What are the post restore steps do you perform?
A: Here are the following steps:
1. Create required the logins and users
2. Sync the orphaned users.
3. Validate the data is accurate by running dbcc commands
4. Notify the App team\user community
5. If required, delete the sensitive data i.e. SSN’s, credit card information, customer names, personal information, etc.
6. Change database properties i.e. DB compatibility, recovery model, read-only, etc.

Q. Have you faced any issue while restoring a database?
A: Here are the few common reasons:
1.Insufficient permission to restore
2.Insufficient Space on the data\log drive.
3.Database is in Use. Unable to get Exclusive lock on the database.
4.Syntax error like run the command WITH MOVE option.
5.Version Issue, Restoring from higher version database backup to lower version. example: Restore from SQL 2008 R2 to SQL2005 not possible.

Q. What are the permissions required to perform backup and Restore?
A: The user must be a member of either of the below roles:
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

Q. How can we prevent all successful SQL Server backup entries writing to the SQL Server Error Log by a single trace flag?
A: Yes – Can be achieved by enabling the trace flag 3226.

Q. How many types of database backups in SQL Server?
A: There are three main backup in SQL Server:
1. Full Backup
2. Diffrential Backup
3. T-log Backup
Few other types:
Copy Only Backup: will not interrupting the LSN’s Sequence.
File Backup: Backup only particular File Backup
FileGroup Backup Backup only particular FileGroup Backup
For more details Refer Backup Restore Post

Q. What is Point in time recovery in SQL Server? Have you ever performed?
A: Point in time restore is possible only in FULL recovery model.
Here are steps to restore backups:
1. Take a tail log backup of the database which needs to be restored.
2. Restore the most recent full backup with the NORECOVERY clause.
3. Restore the most recent differential backup with the NORECOVERY clause
4. Restore all of the subsequent transaction log backups with the NORECOVERY clause except the last transaction log backup.
5. Restore the last transaction log backup with the RECOVERY clause and a STOPAT statement if the entire transaction log does not need to be applied.
For more details Refer Backup Restore Post

Q. How to take tail log backup of the database?
A: Tail log backup is same as normal log backup. We have two options to consider:
1. WITH NORECOVERY: When database online and you are planning to perform a restore after the tail log backup. It takes the database in restoring mode to make sure that no transactions performed after the tail log.
2. WITH CONTINUE_AFTER_ERROR: When database offline and does not starts. Remember we can only perform the log backup on damaged database when the log files are not damaged

Q.You have an environment which is having scenario like Backups are scheduled as below:
FULL Backup- Weekly, Every Sunday Night at 1:00AM
DIFF Backup- Daily once in a Day
LOG Backup- In every 30 Mins
Now you found that your one of the user database has been corrupted and while restoring you got to know that the last Sunday night full backup also has been corrupted. What’s your recovery plan?
A: Here are the steps to Recovery Database:

1. Restore Full backup with NORECOVERY (Taken 13 Days ago)
2. Restore Diff backup with NORECOVERY (Taken 8 Days ago)
3. Restore Subsequent 8 Days of Log backup with NORECOVERY
4. Finally recover the database with command:
Restore Database with RECOVERY

Q. Full backup size is 100 GB, usually Diff backup size varies between 100 MB and 5 GB, one day unfortunately Diff backup size was increased to 50 GB? What is the reason behind this? any idea?
A: This is basically happens because of some activity is scheduled just after or nearby the full backup.
There are might be following reasons:
1. Maintenance jobs are scheduled- like Rebuild\Reorg, UpdateStats.
2. Heavy Insert\Update\Delete Operation just after the full backup completed.

To avoid these issues with Diff backups , schedule the index or such maintenance jobs to happen right before the full backup.

Q. What are the phases of sql server database restore process?
A: There are following phases of database restore process:
1.Analysis: Analyse the disk space availability, check for MDF\NDF & LDF directory and create the files and all other basic checks.
2.REDO: Rollfoward all committed transactions to database and if it finds any uncommitted transactions it goes to the final phase UNDO.
3. UNDO: Rollback any uncommitted transactions and make database available to users.

Q. What is piecemeal Restore?
A: Consider we have a database of 4 TB where as on primary file group is a read write filegroup of size 1 TB and we have other files groups which are read-only of size 3 TB. We actually need not perform backup for read-only file groups, here we can perform Partial backups.
Piecemeal restore process allows us to restore the primary filegroup first and the database can be online and the remaining filegroups can be restored while the recovery the transactions are running on primary File group. Mostly suitable for data warehousing databases.

Q. Consider a situation where I have to take a backup of one database of 60 GB. My hard drive lacked sufficient space at that moment. I don’t find 64GB free on any drive. Fortunately, I have 3 different drives where I can hold 20 GB on each drive. How can you perform the backup to three different drives? How can you restore those files? Is this really possible?
A: Yes we can achieve this using Split Backup.
. We can split the backup files into different places and the same can be restored.

Q. What is the recovery model of master database and can we change it? and can we take log backup of master database?
A: By default the recovery model of master database is SIMPLE.
Yes we can change the recovery model of master can be set to FULL or BULK_LOGGED. However, BACKUP LOG is not supported for master. Therefore, even if the recovery model of master is changed to full or bulk-logged, the database continues to operate as if it were using the simple recovery model.

Q. Is it possible to restore master database? If yes what are the steps?
A: Master Database keeps is having all server level information stored in it that includes logins information etc.., so ensure you schedule a regular backup for master database.
Yes we can restore master database, Here are the steps to restore a master DB:
1. Start the SQL Server Services in single user mode (-m)
Syntax:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.1\MSSQL\Bin\sqlservr.exe -m -s
2. Issue the restore command with replace from SQLCMD prompt
Syntax:
RESTORE DATABASE master FROM WITH REPLACE
3. Restart the sql server in normal mode.

All databases as available at the time of master db backup must be attached as everything is tracked in master database.
If any databases are missing we can manually attach the mdf-ldfs.

SQL Server DBA Basic Ques & Ans Part-5

SQL Server Database Administrator Questions & Answers
Q. How to Rebuild System databases?
A: System databases must be rebuilt to fix corruption problems in the master, model, msdb, or resource system databases or to modify the default server-level collation.

The following procedure rebuilds the master, model, msdb, and tempdb system databases. You cannot specify the system databases to be rebuilt. For clustered instances, this procedure must be performed on the active node and the SQL Server resource in the corresponding cluster application group must be taken offline before performing the procedure.
This procedure does not rebuild the resource database.

This is very critical task especially if you are performing on Production Server, so ensure you list out all the pre/post steps. Follow the below steps to rebuild system databases:
1. Run Command Prompt as Administrator
1.1. From a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Release.
2. From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges.
Syntax:
If using Windows Authentication:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=DOMAIN\user_name [ /SQLCOLLATION=CollationName]

If using SQL Authentication:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

Example:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=sa /SAPWD= ******
3. When Setup has completed rebuilding the system databases, it returns to the command prompt with no messages. Examine the Summary.txt log file to verify that the process completed successfully. This file is located at C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Logs.





Post Steps: After rebuilding the database you may need to perform the following additional tasks:
1. Restore your most recent full backups of the master, model, and msdb databases. For more information, see Back Up and Restore of System Databases (SQL Server).
Note: If you have changed the server collation, do not restore the system databases. Doing so will replace the new collation with the previous collation setting.
If a backup is not available or if the restored backup is not current, re-create any missing entries. For example, re-create all missing entries for your user databases, backup devices, SQL Server logins, end points, and so on. The best way to re-create entries is to run the original scripts that created them.
2. If the instance of SQL Server is configured as a replication Distributor, you must restore the distribution database. For more information, see Back Up and Restore Replicated Databases.
3. Move the system databases to the locations you recorded previously. For more information, see Move System Databases.
4. Verify the server-wide configuration values match the values you recorded previously.

Q. How to Rebuild Resource Database in SQL Server?
A: The following procedure rebuilds the resource system database. When you rebuild the resource database, all service packs and hot fixes are lost, and therefore must be reapplied.
To rebuild the resource system database:
1. Launch the SQL Server Setup program (setup.exe) from the distribution media.
2. In the left navigation area, click Maintenance, and then click Repair.
3. Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. Click OK or Install to continue.
4. On the Select Instance page, select the instance to repair, and then click Next.
5. The repair rules will run to validate the operation. To continue, click Next.
5. From the Ready to Repair page, click Repair. The Complete page indicates that the operation is finished.

Q. If the msdb database is damaged and you do not have a backup of the msdb database, can you to create new msdb database in SQL Server?
A: Yes, you can create new msdb by using the instmsdb script. Follow the following steps:
Note:Rebuilding the msdb database using the instmsdb script will eliminate all the information stored in msdb such as jobs, alert, operators, maintenance plans, backup history, Policy-Based Management settings, Database Mail, Performance Data Warehouse, etc.
1. Stop all services connecting to the Database Engine, including SQL Server Agent, SSRS, SSIS, and all applications using SQL Server as data store.
2. Start SQL Server from the command line using the command:

NET START MSSQLSERVER /T3608
3. In another command line window, detach the msdb database by executing the following command, replacing with the instance of SQL Server:

SQLCMD -E -S -dmaster -Q"EXEC sp_detach_db msdb"
4. Using the Windows Explorer, rename the msdb database files. By default these are in the DATA sub-folder for the SQL Server instance. 5. Using SQL Server Configuration Manager, stop and restart the Database Engine service normally. 6. In a command line window, connect to SQL Server and execute the command:

SQLCMD -E -S -i"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o" C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.out"
Replace with the instance of the Database Engine. Use the file system path of the instance of SQL Server.
7. Using the Windows Notepad, open the instmsdb.out file and check the output for any errors.
8. Re-apply any service packs or hotfix installed on the instance.
9. Recreate the user content stored in the msdb database, such as jobs, alert, etc.
10. Backup the msdb database.

Q. What are main performance monitor counters to be monitor for SQL Server?
A:Following few perfmon counters to be monitor for SQL Server:
Processor\%Processor Time: Monitoring CPU consumption allows you to check for a bottleneck on the server (indicated by high sustained usage).
High percentage of Signal Wait: Signal wait is the time a worker spends waiting for CPU time after it has finished waiting on something else (such as a lock, a latch or some other wait). Time spent waiting on the CPU is indicative of a CPU bottleneck. Signal wait can be found by executing DBCC SQLPERF (waitstats) on SQL Server 2000 or by querying sys.dm_os_wait_stats on SQL Server 2005.
Physical Disk\Avg. Disk Queue Length: Check for disk bottlenecks: if the value exceeds 2 then it is likely that a disk bottleneck exists.
MSSQL$Instance: Buffer Manager\Page Life Expectancy: Page Life Expectancy is the number of seconds a page stays in the buffer cache. A low number indicates that pages are being evicted without spending much time in the cache, which reduces the effectiveness of the cache.
MSSQL$Instance: Plan Cache\Cache Hit Ratio: A low Plan Cache hit ratio means that plans are not being reused.
MSSQL$Instance:General Statistics\Processes Blocked: Long blocks indicate contention for resources.

Q. What is “Lock Pages in Memory” option? How it will impact\useful to SQL Server?
A:Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tell Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems, but depends on various conditions this option needs to be turned on.
We must be very careful in dealing with this option. One can enable this after a detailed analysis of current environment.

Following issues may rise when “Lock Pages in Memory” is not turned on:
--SQL Server performance suddenly decreases.
--Application that connects to SQL Server may encounter timeouts.
--The hardware running SQL Server may not respond for a short time periods.

Q. Task manager is not showing the correct memory usage by SQL Server. How to identify the exact memory usage from SQL Server?
A:To know the exact memory usage relay on column “physical_memory_in_use_kb” from DMV “sys.dm_os_process_memory”.
OR
Using performance counters also we can find the usage.

Performance object: Process
Counter: Private Bytes
Instance: sqlservr
Performance object: Process
Counter: Working Set
Instance: sqlservr
The Private Bytes counter measures the memory that is currently committed. The Working Set counter measures the physical memory that is currently occupied by the process.

For 64-bit sql servers we can also check the current memory usage using the below performance counter.

Performance object: SQL Server:Memory Manager
Counter: Total Server Memory (KB)

Q.I wanted to know what are the maximum worker threads setting and active worker thread count on sql server. Can you tell me how to capture this info? What’s the default value for max thread count?
A: We can check the current settings and thread allocation using the below queries.

–Thread setting
select max_workers_count from sys.dm_os_sys_info

–Active threads
select count(*) from sys.dm_os_threads
Default value is 255.

Increasing the number of worker threads may actually decrease the performance because too many threads causes context switching which could take so much of the resources that the OS starts to degrade in overall performance.