SQL Server DBA Interview Questions and Answers – Database Backups and Restore-4
Here I am posting fourth and last series of Backup/Restore interview questions. I am trying to cover most of the known errors and some concepts too. You may take a look at the other posts on Backup/Restore here.
Database Backup and Restore – 1, Database Backup and Restore – 2 & Database Backup and Restore – 3
1) What are Partial Backups in SQL Server?
Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary filegroup.
2) What is the command to take a Partial backup?
BACKUP DATABASE DBPartial READ_WRITE_FILEGROUPS TO DISK = N'C:\SQLBackups\DBPartial_PARTIAL_Full.bak' GO
Backup scenario:
3) What are the options to restore the database till Point 8 i.e. P8?
- Option 1: F1 > D2 > T5
- Option 2: F1 > D1 > T3 > T4 > T5
- Option 3: F1 > T1 > T2 > T3 > T4 > T5
4) What are the options to restore the database till Point 10 i.e. P10?
- Option 1: F2 > T6
- Option 2: F1 > D2 > T5 > T6
- Option 3: F1 > D1 > T3 > T4 > T5 > T6
- Option 4: F1 > T1 > T2 > T3 > T4 > T5 > T6
5) What are the options to do a point in time recovery P13?
- Option 1: F2 > D3 >T8 with STOPAT Time stamp of P13
- Option 2: F2 > T6 > T7 > T8 with STOPAT Time stamp of P13
- Option 3: F1 > D2 > T5 > T6 > T7 > T8 with STOPAT Time stamp of P13
- Option 4: F1 > D1 > T3 > T4 > T5 > T6 > T7 > T8 with STOPAT Time stamp of P13
- Option 5: F1 > T1 > T2 > T3 > T4 > T5 > T6 > T7 > T8 with STOPAT Time stamp of P13
6) What is the below error?
Msg 3004, Level 16, State 1, Line 19 The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS. Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally
Below SQL statement will through an error if you are trying to take a Partial backup with simple recovery model of the database.
BACKUP DATABASE Database_name FILEGROUP = ‘PRIMARY’ TO DISK = ‘PATH:\Database_name.bak”
Instead of this use READ_WRITE_FILEGROUPS parameter to take a partial backup.
BACKUP DATABASE Database_name READ_WRITE_FILEGROUPS TO DISK = ‘PATH\Database_name.bak’
7) 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.
8) What is the below error?
Msg 3024, Level 16, State 0, Line 1 You can only perform a full backup of the master database. Use BACKUP DATABASE to back up the entire master database. Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.
When you try to take a differential backup of the master database you will get this error. Be design differential backup of master database is not allowed.
9) What is the below error?
Msg 3033, Level 16, State 0, Line 1 BACKUP DATABASE cannot be used on a database opened in emergency mode.
We can’t take the database backup if the database is in emergency mode.
10) What is the below error?
The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
This error occurs when we try to restore the database from upper version to lower version. It is not possible to do a database restore with the backup file of upper version.
11) What is the below error?
Server: Msg 3101, Level 16, State 1, Line 1 Database in use. The system administrator must have exclusive use of the database to run the restore operation. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.
This error occurs when the target database is in use and exclusive access is not granted. We have to kill all the connections on the database to overcome this issue.
12) What is the below error?
Msg 1834, Level 16, State 1, Line 1 The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf' cannot be overwritten. It is being used by database 'AdventureWorks'. Msg 3156, Level 16, State 4, Line 1 File 'AdventureWorks_Data' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'. Use WITH MOVE to identify a valid location for the file.
This issue occurs when we try to restore the database with a file location which contains files with the same name or the source database file location are on a drive letter or folder which is not available on the target server. To resolve this issue we can use “WITH MOVE” option.
13) What is the below error?
Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward
This error happens when Full back up is not restored before attempting to restore differential backup or full backup is restored with WITH RECOVERY option. Make sure database is not in operational conditional when differential backup is attempted to be restored.
14) What is the below error?
Msg 3154, Level 16, State 4, Line 1 The backup set holds a backup of a database other than the existing 'admin_test3' database. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
This issue occurs when we want to restore an existing database. To successfully perform the database restore we have to use WITH REPLACE option in the restore command.
15) What is the below error?
Server: Msg 3168, Level 16, State 1, Line 1 The backup of the system database on device d:\temp\master.bak cannot be restored because it was created by a different version of the server (134217904) than this server (134217920). Server: Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
We cannot restore a backup of a system database (master, model, or msdb) on a server build that is different from the build on which the backup was originally performed. An attempt to perform such a restore causes the above error message.
16) What is the below error?
“This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.“
SQL Server does support restoring earlier version databases on later versions it also allows you to restore t-logs from earlier vertions to later. The databases get upgraded along the way when you perform restores of databases but , the upgrade doesn’t happen until recovery of the database occurs and for that reason can’t use STANDBY mode in this situation.
Use NORECOVERY instead of Standby.
17) How can we kill or take an exclusive access of the database?
ALTER DATABASE [adb] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [adb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Or Write a cursor or loop to kill the DB connections
DECLARE @cmdKill VARCHAR(50)
DECLARE killCursor CURSOR FOR
SELECT 'KILL ' + Convert(VARCHAR(5), p.spid)
FROM master.dbo.sysprocesses AS p
WHERE p.dbid = db_id('MyDB')
OPEN killCursor
FETCH killCursor INTO @cmdKill
WHILE 0 = @@fetch_status
BEGIN
EXECUTE (@cmdKill)
FETCH killCursor INTO @cmdKill
END
CLOSE killCursor
DEALLOCATE killCursor
18) Why Database restores from upper version to lower version is not allowed?
Database servers get changed with service packs and new releases. New object types get added and the lower versions cannot understand these object types.
In order to avoid such conflicts and problems – Higher end database restorations cannot be performed directly on lower end database servers.
19) Is there any alternate method of restoring the database from Upper version to lower version?
There is no proper method of restore the database from upper version to lower version. However we can use below techniques to perform this task:
- Script out the database Objects and create these on the target database
- Use SQL Server Import Export Wizard to copy data from source server to destination server (only Data)
- Copy data to destination tables using BCP (only Data)
20) Is it possible to attach the Data and log files of upper version to lower version SQL Server instance?
No, It is not possible.
21) What are Mirrored Backup Media Sets?
Mirrored backup media sets are supported only in the Enterprise edition of SQL Server.
Mirroring a media set increases backup reliability by reducing the impact of backup-device malfunctions. These malfunctions are very serious because backups are the last line of defense against data loss. As database grows, the probability increases that a failure of a backup device or media will make a backup non restorable. Mirroring backup media increases the reliability of backups by providing redundancy.
22) Is it possible to mark Primary File Group as Read only?
No it’s not possible to make Primary File Group read only.
23) How to make the File Group read only?
Filegroups can be marked as read-only. Any existing filegroup, except the primary filegroup, can be marked as read-only. A filegroup marked read-only cannot be modified in any way. Read-only filegroups can be compressed.
ALTER DATABASE ReadFilegroup MODIFY FILEGROUP Test1FG1 Read_Only;
24) What are the benefits of Read only file groups?
- Can be compressed (using NTFS compression)
- During recovery you don’t need to apply logs to recover a read-only file group
- Protection of data from accidental modifications
25) How to script out the user permissions of the database before the database restore?
Please refer to the below URL to get a SQL Script to extract the user permissions.
http://dbathings.com/database-permissions-extraction-script/
26) How can you mount a database from MDF file even if you don’t have the transaction log?
The only time that you can do this is when the DB was shut down cleanly before the log file was lost. It’s still not a good idea. While attaching a data file without the log file may be possible in some circumstances, it is not a recommended approach and is only for cases when the log file has been damaged or lost due to hardware problems and there are no backups available. Of course, you cannot have a database without a log file, so SQL Server just recreates a log file when you attach the database.
Attaching a data file without the log file breaks the log chain and may render the database transactionally or structurally inconsistent depending on the state of the database before the log file was lost.
27) What is Piecemeal Restore of Database (Simple Recovery Model)?
A piecemeal restore sequence restores and recovers a database in stages at the filegroup level, starting with the primary and all read/write, secondary filegroups.
- Partial restore of the primary and filegroups A and C.
RESTORE DATABASE adb FILEGROUP='A',FILEGROUP='C' FROM partial_backup WITH PARTIAL, RECOVERY;
At this point, the primary and filegroups A and C are online. All files in filegroup B are recovery pending, and the filegroup is offline.
- Online restore of filegroup B.
RESTORE DATABASE adb FILEGROUP='B' FROM backup WITH RECOVERY;
28) Is it possible to perform a point in time recovery with Bulk Logged recovery model?
Yes, it is possible to perform a point in time recovery with Bulk logged recovery model till the time we don’t perform any minimal logged operation on the database.
29) How to recover a database that is in the “restoring” state?
RESTORE DATABASE AdventureWorks WITH RECOVERY GO
30) What are the important points which need to be taken care when we restore the database from lower version to upper version?
- Change the compatibility mode of the database
- Run Update Usage command
- If possible (Time permits) run Index rebuild and Update statistics
31) How to change the database owner of the database?
EXEC sp_changedbowner 'Myuser';
References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.
https://www.simple-talk.com/sql/backup-and-recovery/partial-backup-and-restore/
https://msdn.microsoft.com/en-us/library/ms189557(v=sql.110).aspx
http://www.mssqltips.com/sqlservertip/3113/sql-server-database-restore-with-move-or-not-with-move/
