Restore SQL Server Databases
Restore filelistonly from disk
To restore a SQL Server database filelistonly from disk, you can use the following T-SQL command:
RESTORE FILELISTONLY FROM DISK = 'path\to\backupfile.bak';
Replace path\to\backupfile.bak with the actual path and filename of your backup file. This command will provide you with a list of the database files included in the backup, along with their logical names, physical file names, and file types.
Note that FILELISTONLY is a useful command when you need to verify the contents of a backup file or restore specific
files from a backup.
If you want to restore the entire database, you can use the RESTORE DATABASE command instead,
which will automatically restore all the files listed in the backup.
Restore verifyonly from disk
To verify the backup file without actually restoring it,
you can use the RESTORE VERIFYONLY command in SQL Server.
This command will check the backup file for errors and report any issues it finds without actually restoring the data. Here's the syntax for using this command:
RESTORE VERIFYONLY FROM DISK = 'path\to\backupfile.bak';
Replace path\to\backupfile.bak with the actual path and filename of your backup file. This command will perform a checksum on the backup file and ensure that it is readable and not corrupt. If the backup file passes the verification process, SQL Server will display a message indicating that the verification was successful.
Note that while RESTORE VERIFYONLY is a useful command to ensure the integrity of your backups, it does not guarantee that the backup can be successfully restored. To ensure that you can restore your backups when needed, it's important to test your restore process periodically and ensure that you have valid backups and a solid disaster recovery plan in place.
when actual you have to restore a database.
Different restore database parameters
When restoring a SQL Server database, there are several parameters that you can specify to control the behavior of the restore operation. Here are some of the most common parameters used with the RESTORE DATABASE command:
FROM DISK: Specifies the path and filename of the backup file to restore from.
WITH REPLACE: Specifies that the existing database should be overwritten with the restored data. This parameter should be used with caution as it can result in data loss.
WITH NORECOVERY: Specifies that the database should not be brought online after the restore operation completes. This is useful when you have multiple backup files to restore, and you want to apply transaction logs to the database before bringing it online.
WITH RECOVERY: Specifies that the database should be brought online after the restore operation completes. This is the default behavior if you do not specify WITH NORECOVERY.
MOVE: Specifies the new physical location and filename for the data and log files being restored. This parameter is useful when you need to restore a database to a different location or if the original files were lost.
REPLACE: Specifies that the existing data and log files should be replaced with the restored files. This parameter is useful when you need to restore a database with a different name to the same location as an existing database.
STATS: Specifies that progress information should be displayed during the restore operation.
These are just a few of the parameters that can be used with the RESTORE DATABASE command. The full list of available parameters can be found in the SQL Server documentation.
Example with all those parameter where existing database exist
Sure, here's an example of restoring a SQL Server database using the RESTORE DATABASE command with several of the parameters discussed, assuming the database doesn't exist already:
RESTORE DATABASE MyDatabase
FROM DISK = 'C:\Backup\MyDatabase.bak'
WITH REPLACE,
MOVE 'MyDatabase_Data' TO 'C:\Data\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'C:\Log\MyDatabase.ldf',
STATS = 10, NORECOVERY;
In this example:
MyDatabase is the name of the database being restored.
FROM DISK:-specifies the path and filename of the backup file to restore from.
WITH REPLACE:-overwrites any existing database with the same name as the restored database. Since the database doesn't exist yet, this parameter won't have any effect in this case.
MOVE: specifies the new physical location and filename for the data and log files being restored. In this case, the data file MyDatabase_Data is being moved to C:\Data\MyDatabase.mdf, and the log file MyDatabase_Log is being moved to C:\Log\MyDatabase.ldf.
STATS = 10:- displays progress information every 10 percent during the restore operation.
NORECOVERY:- specifies that the database should not be brought online after the restore operation completes. This is useful when you have multiple backup files to restore, and you want to apply transaction logs to the database before bringing it online.
After running this command, you would need to run a separate RESTORE LOG command to apply any transaction log backups to the restored database before bringing it online with the WITH RECOVERY parameter.
MOVE command takes 2 parameter, the first parameter 'MyDatabase_Data' and 'MyDatabase_Log'
its value you will get from restore filelistonly from disk = ''path\to\backupfile.bak'
The 2nd parameter C:\Data\MyDatabase.mdf and C:\Log\MyDatabase.ldf' are actual location where database data files exist.
MOVE 'MyDatabase_Data' TO 'C:\Data\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'C:\Log\MyDatabase.ldf',
If database is in recovering state, how will you bring it back to online.
USE [master]
RESTORE DATABASE [AdventureWorks2019] FROM DISK = N'G:\backup\AdventureWorks2019.bak' WITH FILE = 1,
MOVE N'AdventureWorks2017' TO N'F:\data_file\adv.mdf', MOVE N'AdventureWorks2017_log' TO N'G:\log_files\adv_lof.ldf',
NOUNLOAD, REPLACE, recovery, STATS = 5
GO
or
restore log [AdventureWorks2019] with recovery
What is difference between norecovery and recovery
In SQL Server, the NORECOVERY and RECOVERY options are used when restoring a database backup to determine whether the database should be made available for use after the restore operation completes.
NORECOVERY: Specifies that the database should not be brought online after the restore operation completes. This is useful when you have multiple backup files to restore, and you want to apply transaction logs to the database before bringing it online. If you use NORECOVERY, you can perform additional restores using the RESTORE LOG command to apply transaction log backups to the database before finally bringing it online with the RECOVERY option.
RECOVERY: Specifies that the database should be brought online after the restore operation completes. This is the default behavior if you do not specify NORECOVERY. If you use RECOVERY, the database will be available for use immediately after the restore operation completes. Once the database is in the online state, you cannot apply any additional transaction logs to the database.
In summary, NORECOVERY is typically used when you need to restore multiple backup files and apply transaction logs in a sequence before making the database available for use. RECOVERY is used when you have only one backup file to restore and want the database to be available for use immediately after the restore operation completes.
After restore you can check the status of a specific database using below commands
1. select * from sys.databases
2. select * from sys.sysdatabases
what is difference between sys.databases and sys.sysdatabases
sys.databases and sys.sysdatabases are both system views in SQL Server that contain information about databases on the server, but they differ in their compatibility and usage.
sys.databases: This is a dynamic management view that was introduced in SQL Server 2005. It is the recommended way to query database-related information in modern versions of SQL Server. It provides more comprehensive and detailed information about databases than sys.sysdatabases. For example, it includes columns like user_access_desc, recovery_model_desc, and log_reuse_wait_desc, which provide more descriptive information about a database's properties and state. sys.databases is more flexible, as it can be filtered and joined with other dynamic management views to get detailed insights into the server's behavior.
sys.sysdatabases: This is a compatibility view that has been present in SQL Server since its early versions. It provides information about databases, but it has limited information compared to sys.databases. sys.sysdatabases is only retained for backward compatibility, as some scripts or applications might still be using it.
In summary, sys.databases is a more comprehensive and flexible view, while sys.sysdatabases is a legacy view that is kept for backward compatibility. It is recommended to use sys.databases to query database-related information in modern versions of SQL Server.
what is log_resue_wait_desc column in sys.databases
The log_reuse_wait_desc column in the sys.databases system view in SQL Server provides information about why the transaction log of a particular database is not being truncated and reused.
When you perform any data modification operation (such as an INSERT, UPDATE, or DELETE), the transaction log records the changes made to the data. These records are kept in the log until they are no longer needed, at which point the space can be reclaimed and reused. The log_reuse_wait_desc column tells you why the transaction log space is not being reused and what is causing it to grow.
Here are some of the possible values for the log_reuse_wait_desc column:
CHECKPOINT: This indicates that the transaction log space is waiting for a checkpoint to occur. A checkpoint is a process that writes dirty pages from memory to disk and flushes the transaction log to disk, allowing the space to be reused.
REPLICATION: This indicates that the transaction log space is being used by replication.
DATABASE_MIRRORING: This indicates that the transaction log space is being used by database mirroring.
DATABASE_SNAPSHOT_CREATION: This indicates that the transaction log space is being used by a database snapshot creation process.
LOG_BACKUP: This indicates that the transaction log space is waiting for a log backup to occur. When you perform a log backup, the transaction log records that have been backed up can be truncated and the space can be reused.
ACTIVE_BACKUP_OR_RESTORE: This indicates that the transaction log space is being used by an active backup or restore operation.
ACTIVE_TRANSACTION: This indicates that the transaction log space is being used by an active transaction. The transaction log cannot be truncated until all active transactions have been committed or rolled back.
These are just a few of the possible values for the log_reuse_wait_desc column. By querying this column, you can identify what is preventing the transaction log space from being reused and take appropriate action to resolve the issue.
No comments:
Post a Comment