Restoration Part-2

Q: What is the Difference between WITH RECOVERY and WITH NORECOVERY parameter?

• RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. Select this option if you are restoring all of the necessary backups now.
•  RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. Additional transaction logs can be restored. The database cannot be used until it is recovered.

Q: What is RESTORE LABELONLY option?

It returns a result set containing information about the backup media identified by the given backup device.

RESTORE LABELONLY FROM DISK='C:\Backup\Adv_Full.bak'

Q: 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.

 

Q: 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’

Q: 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)

Q: 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;

Q: 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.

Please visit other related articles...

https://sqldbaiq.blogspot.com/p/restoration.html
https://sqldbaiq.blogspot.com/p/restoration-part-2.html
https://sqldbaiq.blogspot.com/p/restoration-part-3.html
https://sqldbaiq.blogspot.com/p/restoration-part-4.html
https://sqldbaiq.blogspot.com/p/restoration-part-5_16.html
https://sqldbaiq.blogspot.com/p/restoration-part-6_16.html

https://sqldbaiq.blogspot.com/p/restoration-part-7.html

References: Thanks to the all the SQL Server bloggers who wrote and shared the valuable information on their 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.