MS SQL backup
Microsoft SQL Server Management Studio Express (SSMSE) is a free, easy-to-use graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services. The backup can then be used at a later date to restore the database if any information is lost or in case the database goes corrupt.
The backup file needs to have a .BAK file extension.
The backup file needs to have a .BAK file extension.
To backup your MS SQL Server 2005 database, please follow these steps:
1. Open MSSQL Server Management Studio.
2. Connect to your MS SQL Server database using the DB username & password.
3. Right-click your database, expand All Tasks and select back up. [as shown in the following image..]
4. Under Back Up type, select Full. [as shown in the following image..]
5. Under Destination, click Add. [as shown in the following image]
6. Enter the path and file name for the backup file. [as shown in the following image]
7. Click OK & review the settings for the backup.
The instructions above will create a complete backup of your database into the desired folder. This is a recommended method to ensure that all data is backed up properly.
Please let us know if you encounter any problems while connecting to the server or during the backup operation, we'll fix it quickly.
Please let us know if you encounter any problems while connecting to the server or during the backup operation, we'll fix it quickly.
Batch files for the start & stop MS Sql service before backup
Create a batch file that stops the MS SQL Server:-
Syntax
sc [ServerName] stop [ServiceName] [ServiceArguments]
Parameters
ServerName: Specifies the name of the remote server on which the service is located. The name must use the Universal Naming Convention (UNC) format ("\\myserver"). To run SC.exe locally, ignore this parameter.
ServiceName: Specifies the service name to be stopped.
To automate the startup sequence of MS SQL Server a batch file that calls sc.exe needs to be created and added as a “before action” in the backup job scheduled in Backup4all. Below is an example of commands that can be used to stop the MSSQLServer and SQLServerAgent services on a local computer (code also contains a sequence that sets a delay between each commands, to ensure the services were stopped):
sc stop sqlserveragent
@ECHO OFF
SET NUM=1
:LOOP
IF %NUM% == 6000 GOTO END1
SET /A NUM=NUM+1
GOTO LOOP
:END1
@ECHO ON
SET NUM=1
:LOOP
IF %NUM% == 6000 GOTO END1
SET /A NUM=NUM+1
GOTO LOOP
:END1
@ECHO ON
sc stop mssqlserver
@ECHO OFF
SET NUM=1
:LOOP2
IF %NUM% == 6000 GOTO END2
SET /A NUM=NUM+1
GOTO LOOP2
:END2
@ECHO ON
SET NUM=1
:LOOP2
IF %NUM% == 6000 GOTO END2
SET /A NUM=NUM+1
GOTO LOOP2
:END2
@ECHO ON
MS SQL Server can be restarted using the sc.exe command line program.
Here is the syntax for calling sc.exe:
Here is the syntax for calling sc.exe:
Syntax
sc [ServerName] start [ServiceName]
Parameters
ServerName: Specifies the name of the remote server on which the service is located. The name must use the Universal Naming Convention (UNC) format ("\\myserver"). To run sc.exe locally, ignore this parameter.
ServiceName: Specifies the service name to be started.
ServiceArguments: Specifies service arguments to pass to the service to be stopped. Note that this option is not used when the command “stop” is issued.
To automate the restart sequence of MS SQL Server, a batch file that calls sc.exe needs to be created and added as an “after action” in the scheduled backup job in Backup4all. Below is an example of commands that can be used to restart the MSSQLServer and SQLServerAgent on a local computer:
sc.exe start mssqlserver
@ECHO OFF
SET NUM=1
:LOOP
IF %NUM% == 6000 GOTO END
SET /A NUM=NUM+1
GOTO LOOP
:END
@ECHO ON
SET NUM=1
:LOOP
IF %NUM% == 6000 GOTO END
SET /A NUM=NUM+1
GOTO LOOP
:END
@ECHO ON
sc.exe start sqlserveragent
Thanks & Regards,
Gaurav Singh
