T-SQL script to restore a database.
RESTORE DATABASE AdventureWorks2012 FROM DISK='e:\advwork.bak'
T-SQL script to set RECOVERY FULL model.
ALTER DATABASE dbrnd_DB SET RECOVERY FULL;
T-SQL script to restore a multiple transaction log backups.
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
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.
When is a Page Restore Userful?
A page restore is intended for repairing isolated damaged pages. Restoring and recovering a few individual pages might be faster than a file restore, reducing the amount of data that is offline during a restore operation.
T-SQL syntax to restore corrupted pages.
RESTORE DATABASE database_name PAGE='1:57, 1:202, 1:916, 1:1016' FROM 'file_path'
WITH NORECOVERY;
How to find corrupted data pages?
Use suspect_pages table of MSDB database to find information of corrupted data pages.
Pages are listed in this table because they are suspected of being bad, but they might actually be fine. When a suspect page is repaired, its status is updated in the event_type column.
Can I perform Online page restore on all the edition of SQL Server?
SQL Server Enterprise edition supports online page restores, though they use offline restore if the database is currently offline. In most cases, a damaged page can be restored while the database remains online.
Meaning of different event type of suspect_pages table.
· 1 = An 823 error that causes a suspect page (such as a disk error) or an 824 error other than a bad checksum or a torn page (such as a bad page ID).
· 2 = Bad checksum.
· 3 = Torn page.
· 4 = Restored (page was restored after it was marked bad).
· 5 = Repaired (DBCC repaired the page).
· 7 = Deallocated by DBCC.
Why ApexSQL Recovery tool is better point-in-time recovery?
The advantages of ApexSQL tools over recovery to a point in time are that ApexSQL will recover just the tables you specify, while a point-in-time recovery will roll back all the transactions that happened in the meantime.
Can you access the database during a restore operation?
Most SQL Server restores are offline operations; users can’t access the database while it’s being restored.
With the full recovery model, partial restores and restores of nonprimary file groups are online operations by default.
How can we rebuild the system databases?
Please visit this official Microsoft document.
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.
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
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.
No comments:
Post a Comment