SQL Server: Copias de seguridad automáticas con rotación de archivos (Tutorial paso a paso)
How to Set Up Automatic Backups in SQL Server
Introduction to Automatic Backups
- The video introduces the process of setting up automatic backups in SQL Server, emphasizing its importance for data security against hardware failures, software issues, or cyberattacks.
Activating SQL Server Agent
- To begin, users must open Microsoft SQL Server Management Studio and check if the SQL Server Agent is activated; it is typically off by default.
- If the agent is not running, access the SQL Server Configuration Manager to change its startup mode from manual to automatic and start the service.
Configuring Backup Jobs
- Once the agent is active, users can create a new job for backups. This involves naming the job (e.g., "copia de seguridad") and defining its steps.
- In the steps section, users will specify how to execute the backup using T-SQL commands tailored for their database.
Writing Backup Scripts
- A script example is provided that creates daily backups stored in a designated folder with filenames reflecting each day of the week.
- The script ensures that only seven copies are kept—one for each day—overwriting older files as new ones are created weekly.
Scheduling Backups
- Users can set a schedule for these jobs by specifying execution times (e.g., 2 AM daily), ensuring minimal disruption during off-hours.
- After configuring all settings and schedules, users confirm their setup by checking that tasks appear correctly in SQL Server Management Studio.
Testing Backup Functionality
- Finally, users can test their backup task by manually initiating it through right-click options in Management Studio to ensure everything functions as intended.