Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb. Luckily, there is a free version SQL Express, but does have a few limitations.
Most small companies would tend to use SQL Express, however setting up backups using the express manager can be a bit of a pain, as it doesn't allow you to schedule them. Well luckily the express manager still lets you script actions to an SQL script using the GUI - so for example you can setup a once off backup operation, then script that operation to an SQL script file.
Using the following method, you can schedule the backup of a live SQL database. Please note that these steps assume you are familiar with the SQL Express GUI and also familiar with creating a scheduled task within windows.
The following steps must be taken:
1. First, you must have a database on a MS SQL Server.
2. Open SQL Express manager and right click the database that requires backing up. Go to all tasks, backup.
3. This will open a backup window, where you can perform one time backups with the GUI, this GUI also has an option to ‘script’ the action that you just setup. So basically, you setup the backup location etc, then script that to a file that can be called in the batch file.
4. Select script action and let the script generate. Save this script to your SQL server Eg: C:\scripts\SQL\
5. Make a new Text file, rename to ‘sqlbackup.bat’ and add the following line:
sqlcmd -U user -P Pass -i PathToSQLScript\script.sql
6. Save it to the same spot as your .sql script
That gives you the 2 files that you need. Then all that is left to do is open up windows scheduler on the SQL server, setup a new task that calls the batch file and set it to run as often as you need (daily, weekly etc).
This is a great way to do backups for small companies that dont want to buy the full version of MS SQL Server, but still want the reassurance of scheduled backups.
Glen
Most small companies would tend to use SQL Express, however setting up backups using the express manager can be a bit of a pain, as it doesn't allow you to schedule them. Well luckily the express manager still lets you script actions to an SQL script using the GUI - so for example you can setup a once off backup operation, then script that operation to an SQL script file.
Using the following method, you can schedule the backup of a live SQL database. Please note that these steps assume you are familiar with the SQL Express GUI and also familiar with creating a scheduled task within windows.
The following steps must be taken:
1. First, you must have a database on a MS SQL Server.
2. Open SQL Express manager and right click the database that requires backing up. Go to all tasks, backup.
3. This will open a backup window, where you can perform one time backups with the GUI, this GUI also has an option to ‘script’ the action that you just setup. So basically, you setup the backup location etc, then script that to a file that can be called in the batch file.
4. Select script action and let the script generate. Save this script to your SQL server Eg: C:\scripts\SQL\
5. Make a new Text file, rename to ‘sqlbackup.bat’ and add the following line:
sqlcmd -U user -P Pass -i PathToSQLScript\script.sql
6. Save it to the same spot as your .sql script
That gives you the 2 files that you need. Then all that is left to do is open up windows scheduler on the SQL server, setup a new task that calls the batch file and set it to run as often as you need (daily, weekly etc).
This is a great way to do backups for small companies that dont want to buy the full version of MS SQL Server, but still want the reassurance of scheduled backups.
Glen