About Me

My photo
I am MCSE in Data Management and Analytics with specialization in MS SQL Server and MCP in Azure. I have over 13+ years of experience in IT industry with expertise in data management, Azure Cloud, Data-Canter Migration, Infrastructure Architecture planning and Virtualization and automation. Contact me if you are looking for any sort of guidance in getting your Infrastructure provisioning automated through Terraform. I sometime write for a place to store my own experiences for future search and read by own blog but can hopefully help others along the way. Thanks.

Restore SQL Server Databases with different options.

 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.

Questions on sql server database backup and restore

What is a database backup in SQL Server?
A. A copy of the database that can be used to restore data in case of a disaster or data loss.

How can you perform a database backup in SQL Server Management Studio (SSMS)?
A. Right-click on the database, select Tasks, then click Backup.
How can you perform a database backup in SQL Server using T-SQL

You can use the T-SQL BACKUP DATABASE statement to perform a database backup in SQL Server. Here's an example command to perform a full backup of a database:


BACKUP DATABASE [database_name] 
TO DISK = 'C:\backup\database_name.bak' 
WITH INIT;
Replace [database_name] with the name of the database you want to back up. 
The TO DISK option specifies the path and filename for the backup file, and the WITH INIT option overwrites any existing backup file with the same name. You can also specify additional options to customize the backup, such as compression or encryption.

Here's an example command to perform a differential backup of a database:


BACKUP DATABASE [database_name] 
TO DISK = 'C:\backup\database_name_diff.bak' 
WITH DIFFERENTIAL;
The WITH DIFFERENTIAL option specifies that this is a differential backup, which only includes changes made since the last full backup.

You can also perform a transaction log backup using the BACKUP LOG statement, like this:


BACKUP LOG [database_name] 
TO DISK = 'C:\backup\database_name_log.bak';
This will create a backup file containing all the transactions that have been made since the last transaction log backup.




What is a differential backup in SQL Server?
A. A backup that only includes the changes made since the last full backup.

What is a transaction log backup in SQL Server?
A. A backup that includes all the changes made to the database since the last transaction log backup.

How can you restore a database in SQL Server Management Studio (SSMS)?
A. Right-click on the database, select Tasks, then click Restore. Choose the backup file to restore and select the restore options.

What is the difference between a full backup and a differential backup?
A. A full backup includes all the data in the database, while a differential backup only includes the changes made since the last full backup.

What is the difference between a full backup and a transaction log backup?
A. A full backup includes all the data in the database, while a transaction log backup only includes the changes made to the database since the last transaction log backup.

How can you check the backup history for a database in SQL Server?
A. Right-click on the database, select Reports, then click Standard Reports and choose Backup and Restore Events.
SELECT 
    [backup_start_date], 
    [backup_finish_date], 
    [database_name], 
    [backup_size], 
    [user_name], 
    [type]
FROM 
    msdb.dbo.backupset
WHERE 
    database_name = '<database_name>'
ORDER BY 
    backup_finish_date DESC


What is the purpose of the WITH NORECOVERY option when restoring a database?
A. It allows additional transaction log backups to be restored after the restore operation.

What is the purpose of the WITH RECOVERY option when restoring a database?
A. It brings the database online and makes it available for use after the restore operation.

what are the different backup database options

In SQL Server, there are several backup database options that you can use to customize the backup process. Here are some of the most common options:

WITH INIT: Overwrites any existing backup file with the same name.
WITH FORMAT: Formats the backup media (such as a tape or disk) before performing the backup.
WITH SKIP: Skips backing up any files that are marked as read-only or offline.
WITH NOFORMAT: Skips formatting the backup media before performing the backup.
WITH NOINIT: Appends the backup to an existing backup file instead of overwriting it.
WITH COMPRESSION: Compresses the backup file to reduce its size.
WITH ENCRYPTION: Encrypts the backup file for security purposes.
WITH CHECKSUM: Verifies the integrity of the backup file by computing a checksum value.
These options can be combined together to create a customized backup strategy that meets your specific needs. For example, you might use WITH INIT to overwrite any existing backup files, WITH COMPRESSION to reduce the size of the backup file, and WITH CHECKSUM to verify its integrity.




No comments:

Post a Comment

Subscribe to: Post Comments (Atom)