Menu
What are the recommendations of software products for creating automated backups of SQL Server 2008 databases?
The backup should happen without taking the database offline/detatching.
696
From the Windows Control Panel, select Administrative Tools, then Services. Find the SQL Backup Agent service for the relevant instance of SQL Server, for example SQL Backup Agent-<instance name>. The SQL Backup Agent service for the local instance is called SQL Backup Agent. Right-click the service and select Stop.
Go to the Object Explorer window (located on the left) and make sure that your SQL Server Agent is running. Enter the name of the Maintenance Plan you are going to create. Press on the calendar icon on the top-right highlighted section on the job schedule screen. It will bring up the job schedule screen.
If you are using SQL Server Express, you won't find a UI to run periodic backups.
In this case you have to run a batch using Windows Scheduled Tasks or something similar.
Don't forget to use a user with enough privileges to access SQL Server.
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S (local)\SQLExpress -i D:\dbbackups\SQLExpressBackups.sql BACKUP DATABASE MyDataBase1 TO DISK = N'D:\DBbackups\MyDataBase1.bak' WITH NOFORMAT, INIT, NAME = N'MyDataBase1 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 BACKUP DATABASE MyDataBase2 TO DISK = N'D:\DBbackups\MyDataBase2.bak' WITH NOFORMAT, INIT, NAME = N'MyDataBase2 Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO I would recommend just creating a maintenance plan in SQL Server to handle the backups, it can be configured to backup to a specified location at specified times, without taking the databases offline, and will handle your incremental backup cleanup.
http://msdn.microsoft.com/en-us/library/ms189715.aspx
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
