Backup and Restore SQL Server Databases Using PowerShell

Introduction:

 

The document outlines the procedures for performing backup and restoration of SQL Server databases using PowerShell. These processes ensure data safety, recovery readiness, and business continuity. Proper execution of the steps minimizes downtime and prevents data loss. The document covers prerequisites, backup size estimation, restoration methods, and validation steps to achieve a reliable and efficient workflow.

 

1.OS Level Requirements

• Actions/Fixes:

1.Ensure sufficient disk space is available on the backup server to store the backup file.

• Verify disk space using commands like df -h(Linux) or Get-Volume (Windows PowerShell).

• Free up space if required by deleting older, unused files or relocating them.

2.Confirm network connectivity to the backup copy share point or storage location.

• Test access to the share point with tools like ping (IP), telnet, or file explorer access.

 

Ping 1**.1**.1.1**

3. Verify read/write permissionsfor the SQL Server service account on the share point or backup directory.

• Update permissions using the appropriate Query/OS tools if missing.

4. Schedule a dedicated maintenance windowto avoid high system load during the backup/restoration.

• Validation

 

1. Confirm available disk space exceeds the size of the expected backup file.
2. Test access to the share point with a small test file upload and download.
3. Verify that backup files are written successfully to the target directory or share point.

2.Backup Size and Restore Space

• Actions/Fixes:

1. Calculate the estimated size of the backup.

 

• Use SQL Server query:

SELECT database_name, backup_size/1024/1024 AS BackupSize_MB

FROM msdb.dbo.backupset

WHERE database_name = ‘<DatabaseName>’

ORDER BY backup_finish_date DESC;

 

2.Ensure the target location for restoration has adequate space.

• Validate the size of the data and log files using:

EXEC sp_spaceused;

 

3.Identify large tables or indexes that may impact storage and defragment if necessary.

• Validation:

1. Verify the size of the backup file using file properties or SQL Server logs.

 

2. Confirm available free space on the target disk matches or exceeds the calculated size.

 

3. Perform a test backup and validate its size against expectations.

3. Restoration Method with Latest Data

• Actions/Fixes:

1. Restore the most recent full backupfirst:

RESTORE DATABASE [DatabaseName]

FROM DISK = ‘Backup_Location\FullBackup.bak’

WITH NORECOVERY;

 

2. Apply the latest differential backup(if applicable):

RESTORE DATABASE [DatabaseName]

FROM DISK = ‘Backup_Location\DiffBackup.bak’

WITH NORECOVERY;

3.Restore the transaction logsto bring the database to the latest state.

 

RESTORE LOG [DatabaseName]

FROM DISK = ‘Backup_Location\LogBackup.trn’

WITH RECOVERY;

 

4.For point-in-time recovery, specify the STOPAToption:

 

RESTORE LOG [DatabaseName]

FROM DISK = ‘Backup_Location\LogBackup.trn’

WITH STOPAT = ‘YYYY-MM-DD HH:MM:SS’, RECOVERY;

 

5.After restoration, check the database state and set it to multi-user mode.

 

ALTER DATABASE [DatabaseName] SET MULTI_USER;

• Validation:

 

1. Verify the restoration sequence (Full > Differential > Logs) in the SQL Server logs.

 

2. Confirm the application can connect to the restored database without errors.
3. 1. Check for orphaned users and resolve using.

    

   EXEC sp_change_users_login ‘Report’;

4. 1. Validate database consistency with DBCC CHECKDB:

    

   DBCC CHECKDB(‘DatabaseName’);

General Fixes for common Issues

Backup Fails Due to Disk Space:

• Archive older backups or add more storage to the disk.

Restore Fails Due to File Corruption

• Validate the backup using.

 

RESTORE VERIFYONLY FROM DISK = ‘Backup_Location\Backup.bak’;

 

• Use the most recent uncorrupted backup.

 

Restore Fails Due to Active Connections

 

• Set the database to single-user mode.

ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

 

Final Validation Checklist

 

1. Validate the OS-level disk space, network connectivity, and permissions.
2. Confirm the backup size matches the expected size.
3. Check restored database consistency using DBCC CHECKDB.
4. Verify application functionality post-restore.

Document the process, including any issues and their resolutions.

 

Conclusion

Proper implementation of the outlined backup and restoration procedures ensures the availability of data with minimal downtime. Regular validation and proactive issue resolution enhance the reliability of SQL Server environments. Adhering to these guidelines helps maintain data integrity and supports seamless recovery in critical scenarios