SQL Server Backup and Maintenance

SQL Server Backup and Maintenance

To view these instructions in more details, download the files at the bottom of this page. 

Does your server have backup software that can backup active SQL Server databases? For most business-level backup products that is a feature upgrade, so if you're not sure, then you probably don't have that option. Instead you need a backup process that temporarily detaches the database from the server, creates a backup copy of the database files, then reattaches it to the server.  

To use the backup script

  1. Create the backup file using the text at the end of this article or use the attached zipped file. 
  2. Edit the script using notepad and replace the values at the top of the page: SERVER_NAME, INSTANCE_NAME (if used), TEMP_FOLDER, BACKUP_FOLDER.      
  3. Save your changes.
  4. Double-click the .bat file to test it.  In a minute or two, a backup file should appear in the backup folder.  If it does not, check the variables and try again. You may need to switch between windows authentication and SQL Server authentication.

To schedule a backup using this script 

  1. Click the Start button on the server that hosts your SQL Server database.
  2. In the search box, enter "Task Scheduler".
  3. Select "Create Basic Task" in the Action list at right.
  4. Give the task a name such as "ProTracker Backup". Click Next.
  5. Choose "Daily". Click Next.
  6. Select the Start Time, a time when you expect no one to be using the system. Click Next
  7. Select "Start a Program". Click Next.
  8. Browse to the script file you saved above.  Click Next.
  9. Click Finish.
  10. Right-click the name of the task in the list and select "Run" to test it.

 

Maintaining the SQL database

SQL Server databases do not normally require maintenance. SQL Server is very good at maintaining the database for optimal performance without user intervention. You may wish to shrink the database files from time to time as the log file grows. This is not required, but it may be helpful if disk space is short or to troubleshoot performance issues.

Note: All users should close out of the program before shrinking the database.

To shrink your SQL database:

  1. Click the Start button on the server that hosts your SQL Server database.
  2. In the search box, enter "SQL Server Management Studio".
  3.  When prompted, connect to your SQL Server instance, providing the server name, user name, and password.
  4. Right-click the ProTracker database and select Tasks>Shrink>Database.
  5. Click OK and wait a minute or two for the process to complete.

________________________________ 

Save, Extract and modify the attached sqlbackupscript or create using the text below.

 To create a Backup script – copy & paste text into a document. Save as .bat

 

@ECHO OFF
REM ================================================
REM SQL SERVER VARIABLES
REM If win auth, then use "none" in UID, PWD
REM ================================================
SET SERVER=SERVERNAME\SQLEXPRESS
SET DATABASE=ProTrackerAdvantage
SET UID=none
SET PWD=none

REM ================================================
REM BACKUP PATH VARIABLES
REM VALID XFER_TYPES; ZIP,BAK,ALL
REM If no DIR_Stage1 or DIR_Stage2, then put "none"
REM ================================================
SET XFER_TYPE=ZIP
SET FILE_NAME=ProTrackerAdvantage
SET DIR_TEMP=C:\Temp\
SET DIR_STAGE=C:\Progra~2\ProTra~1\
SET DIR_STAGE1=none
SET DIR_STAGE2=none

REM ================================================
REM APP VARIABLES
REM ================================================
SET APP_ZIP=C:\Progra~2\7-zip\7z.exe

 

REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
REM !!! DO NOT MODIFY BELOW THIS LINE !!!
REM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

REM ================================================
REM SYSTEM VARIABLES
REM ================================================
SET DAY_NAME=%DATE:~0,3%
SET FILE_BACKUP=%FILE_NAME%.bak
SET FILE_ZIP=%FILE_NAME%_%DAY_NAME%.zip
SET FILE_LOG=backuplog.txt

SET PATH_BACKUP=%DIR_TEMP%%FILE_BACKUP%
SET PATH_ZIP=%DIR_TEMP%%FILE_ZIP%
SET PATH_LOG=%DIR_TEMP%%FILE_LOG%

REM ================================================
ECHO Preparing for backup ...
REM ================================================

if exist %PATH_BACKUP% del "%PATH_BACKUP%"
if exist %PATH_ZIP% del "%PATH_ZIP%"
if exist %PATH_LOG% del "%PATH_LOG%"

IF %UID%==none GOTO :BACKUP_WIN_AUTH
IF NOT %UID%==none GOTO :BACKUP_SQL_AUTH

:BACKUP_SQL_AUTH
REM ================================================
ECHO Backing up %SERVER%::%DATABASE% as user %UID% ...
REM ================================================
SQLCMD -S%SERVER% -U%UID% -P%PWD% -Q "BACKUP DATABASE %DATABASE% TO DISK = '%PATH_BACKUP%'" -o "%PATH_LOG%"
GOTO :BACKUP_CHECK

:BACKUP_WIN_AUTH
REM ================================================
ECHO Backing up %SERVER%::%DATABASE% ...
REM ================================================
SQLCMD -E -S%SERVER% -Q "BACKUP DATABASE %DATABASE% TO DISK = '%PATH_BACKUP%'" -o "%PATH_LOG%"
GOTO :BACKUP_CHECK

:BACKUP_CHECK
REM ================================================
ECHO Validating Backup ...
REM ================================================
IF NOT Exist "%PATH_BACKUP%" (
ECHO Backup Failed
GOTO :END
)

REM Skip the Zip process if you can
IF %XFER_TYPE%==BAK GOTO :XFER_START

:BACKUP_ZIP
REM ================================================
ECHO Zipping Backup ...
REM ================================================
IF NOT Exist "%APP_ZIP%" (
ECHO Zip Application Not Available
GOTO :END
)
"%APP_ZIP%" a "%PATH_ZIP%" "%PATH_BACKUP%"

:BACKUP_ZIP_CHECK
REM ================================================
ECHO Checking Zip ...
REM ================================================
IF NOT Exist "%PATH_ZIP%" (
ECHO Zip Failed
GOTO :END
)

:XFER_START
REM ================================================
ECHO Checking Transfer Type ...
REM ================================================
IF %XFER_TYPE%==ZIP GOTO :XFER_ZIP
IF %XFER_TYPE%==BAK GOTO :XFER_BAK
IF %XFER_TYPE%==ALL GOTO :XFER_ZIP

:XFER_ZIP
REM ================================================
ECHO Transferring zip to staging directory ...
REM ================================================

XCOPY "%PATH_ZIP%" "%DIR_STAGE%" /Y
IF NOT Exist "%DIR_STAGE%%FILE_ZIP%" (
ECHO Transfer of ZIP to %DIR_STAGE% Failed
GOTO :END
)
IF NOT %DIR_STAGE1%==none (
XCOPY "%PATH_ZIP%" "%DIR_STAGE1%" /Y
IF NOT Exist "%DIR_STAGE1%%FILE_ZIP%" (
ECHO Transfer of ZIP to %DIR_STAGE1% Failed
GOTO :END
)
)
IF NOT %DIR_STAGE2%==none (
XCOPY "%PATH_ZIP%" "%DIR_STAGE2%" /Y
IF NOT Exist "%DIR_STAGE2%%FILE_ZIP%" (
ECHO Transfer of ZIP to %DIR_STAGE2% Failed
GOTO :END
)
)
IF %XFER_TYPE%==ZIP GOTO :XFER_END

:XFER_BAK
REM ================================================
ECHO Transferring bak to staging directory ...
REM ================================================

XCOPY %PATH_BACKUP% %DIR_STAGE% /Y
IF NOT Exist "%DIR_STAGE%%FILE_BACKUP%" (
ECHO Transfer of BAK Failed
GOTO :END
)
IF NOT %DIR_STAGE1%==none (
XCOPY "%PATH_BACKUP%" "%DIR_STAGE1%" /Y
IF NOT Exist "%DIR_STAGE1%%FILE_BACKUP%" (
ECHO Transfer of BAK to %DIR_STAGE1% Failed
GOTO :END
)
)
IF NOT %DIR_STAGE2%==none (
XCOPY "%PPATH_BACKUP%" "%DIR_STAGE2%" /Y
IF NOT Exist "%DIR_STAGE2%%FILE_BACKUP%" (
ECHO Transfer of BAK to %DIR_STAGE2% Failed
GOTO :END
)
)

:XFER_END


REM ================================================
ECHO Cleaning up ...
REM ================================================

DEL /S /Q "%PATH_BACKUP%"
DEL /S /Q "%PATH_ZIP%"
DEL /S /Q "%PATH_LOG%"
ECHO Backup successful!

:END
@ECHO ON

 

Have more questions? Submit a request

0 Comments

Article is closed for comments.