How to Change SQL Database Backup Location

Importance of database backup for data security

Database backups are the ultimate safety net for SQL systems. They act as a fail-safe against data loss, which can result from a multitude of reasons, including hardware failures, human errors, and malicious attacks. By regularly creating backups, organizations can recover their data to a known good state, reducing downtime and ensuring business continuity. The ability to restore databases from backups also safeguards against ransomware attacks and other data-related disasters, making database backups a fundamental aspect of data security.

Reasons for changing backup location

Changing the backup location for your data is a crucial step in ensuring the safety and accessibility of your valuable information. There are several compelling reasons why you might consider changing your backup location:

• Data Security: Organizations might need to move backups to more secure storage to protect sensitive information from unauthorized access.
• Disaster Recovery: To mitigate the risk of data loss during natural disasters or catastrophic events, moving backups to an offsite location is essential.
• Storage Optimization: As databases grow, the need for more storage space increases. Shifting backups to different storage can help manage space efficiently.

How to change SQL database backup location

It is a fundamental task to change SQL database backup location for ensuring data security and efficient storage management. Here are the guides on how SQL Server change default database backup location:

Use SSMS to change backup location

1. Navigate to the Microsoft SQL Server program on the Windows Server by clicking on Start >> Programs.

2. Launch SQL Server Management Studio and log in with either SQL authentication or Windows authentication, provided you are logged into Windows as an Administrator.

3. Right-click the SQL Server instance and select Properties to set property devices SQL backup.

4. Within the Database Settings section, locate the Backup default location at the bottom of the window. Make your selection for the new location.

Use Windows Registry to change backup location

1. While in the registry editor, navigate to the registry keys associated with your specific named database instance of SQL Server. For instance, if you have an instance named 'ACCTIVATE' in SQL Server 2008, you would locate a key similar to the following:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.ACCTIVATE\MSSQLServer

2. Look for a key labeled 'BackupDirectory.' If you can't immediately locate the 'BackupDirectory' key, you may consider conducting a search within the HKEY_LOCAL_MACHINE registry for 'BackupDirectory.'

3. Right-click the BackupDirectory key and select to edit SQL backup job.

4. Input the desired Backup Directory into the respective field.

Use Extended Stored Procedures to change backup location

An alternative method for modifying the registry involves utilizing the extended stored procedures XP_REGREAD and XP_REGWRITE.

1. To extract data from the registry, you can execute the following command (ensuring the key value aligns with the correct SQL Server installation path):

DECLARE @BackupDirectory VARCHAR(100)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer',

@value_name='BackupDirectory',

@BackupDirectory=@BackupDirectory OUTPUT

SELECT @BackupDirectory

2. This command provides the following output, given your prior alteration in the registry.

3. If the intention is to revert the folder location to its default setting, you can utilize this command and specify the path within the value parameter:

EXEC master..xp_regwrite

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer',

@value_name='BackupDirectory',

@type='REG_SZ',

@value='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup'

4. Executing this command again will display the updated status:

DECLARE @BackupDirectory VARCHAR(100)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer',

@value_name='BackupDirectory',

@BackupDirectory=@BackupDirectory OUTPUT

SELECT @BackupDirectory

5. To ascertain the installation location of SQL Server, you can right-click on the Server name, choose Properties. The root directory, as highlighted below, will indicate the corresponding installation name for the instance, like 'MSSQL2', helping you identify the relevant registry entry requiring modification."

Freely choose your SQL database backup location with AOMEI Cyber Backup

Regularly creating and safeguarding backups is essential for data security, disaster recovery, and overall business continuity. While the process of changing the backup location in SQL databases is a fundamental task, the right software – AOMEI Cyber Backup can make it more convenient and efficient.

AOMEI Cyber Backup is a powerful and user-friendly backup solution that empowers you to have full control over your SQL database backup location. With this software, you can enjoy the following benefits:

✤Easy-to-use: SQL backups may be configured without writing any code thanks to the user-friendly interface.
✤Operation Within LAN: Assist you in doing numerous backups of SQL database, including SQL Express within LAN.
✤Backup Schedule: Schedule an automatic backup task to back up the database at predetermined periods.
✤Auto & Centralized Backup: Schedule SQL backups and run them automatically.

AOMEI Cyber Backup supports Microsoft SQL (2005-2022), VMware ESXi (6.0 and above), and Hyper-V (in Windows 8/8.1/10/11, Windows Server/Microsoft Hyper-V Server 2012 R2 and later versions) from a centralized console and here’s a 30-day free trial for you to enjoy this professional software:

✍ Before you perform a database backup, please make sure:
1. Your computer with both AOMEI Cyber Backup Agent and Microsoft SQL Server installed.
2. The local disk or network shared to store backup files.

Steps to back up SQL database with ease

1. Access to Source Device >> Add Microsoft SQL. If the database exists and the version is supported, it will appear automatically. Otherwise, you can click Add Microsoft SQL >> Download proxy program, and install the program on the device with SQL Server installed. Then click​​​ Already installed proxy and select the proxies you want to add.

2. Click … >> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication. Enter the credentials and click Verify.

3. Create Backup Task: Click Backup Task >> Create New Task, and select Microsoft SQL Backup as your backup type. Then set the Device Name, Backup Target, Schedule, Backup Cleanup and Email Notification according to your need.

4. Start Backup: You can choose to Add the schedule and start backup now or Add the schedule only and click Start Backup to execute automatic SQL server backup.

Steps to restore SQL database from backup

The task you create for backups will be individually listed and tracked, allowing you to verify their advancement, modify their timing, and initiate their restoration.

1. Click Backup Task on the left menu bar, locate the task you want to restore, and click … >> Restore.

2. Specify the target location by choosing Restore to original location or Restore to new location.

Conclusion

Changing the backup location of your SQL databases is not a matter of convenience but a critical step in safeguarding your data, maintaining business continuity, and adhering to regulatory standards. It's a proactive measure that fortifies data security, prepares your organization for disaster recovery, and promotes efficient storage management.

In an age where data is the lifeblood of businesses, making data security and accessibility a top priority is a strategic move that ensures your organization's resilience in the face of adversity.