5 Replies Latest reply on May 6, 2016 9:18 AM by Steve Watson

    Stopping Pharos services in a particular order for backup

    Steve Watson Wayfarer

      Hi All,

       

      I've been trying to run a backup on my pharos server for a few days, and it keeps bombing out before it begins. I suspect the fact that the SQL database is still running, and want to create a batch file that stops the services before the backup begins, then another batch file to restart them once the backup is done. Kinda clunky, but if it gets the job done....

       

      Anyway, when I send a command via the batch file to stop the database server, I keep getting errors telling me other running services are dependent upon it. Anyone know which ones and in which order to stop in order to prevent this?

       

      Thanks!

        • Re: Stopping Pharos services in a particular order for backup
          David Teviotdale Adventurer

          Refer: View service dependencies: Management Services

          Summary: To get service dependencies for any service:

          • Open Services
          • In the details pane, right-click the service that you want to view dependencies for, and then click Properties.
          • Click the Dependencies tab.
          1 of 1 people found this helpful
          • Re: Stopping Pharos services in a particular order for backup
            Richard Post Guide

            David Teviotdale is right on point with determining service dependencies.

             

            That said, I have never see a problem with making a SQL backup, even while the server was running in production.

             

            It might be worthwhile touching base with our Support folks if they have some best practices around making SQL backups.

              • Re: Stopping Pharos services in a particular order for backup
                Steven English Guide

                Steve Watson,

                 

                I am not aware of a reason you would encounter an issue with creating a backup of the database via SQL commands (if backing up to a network device, maybe try local first?).  That said, if you are instead talking about using an external backup solution that is backing up the server files/state rather than just the database, I believe that many of them require a premium edition in order to backup the database files in use by the SQL server (look for advertisement of support for SQL server backups and volume shadow services VSS, if I recall correctly).

                 

                If you are talking about backing up the server instead of backing up just the database, you will want to schedule a backup of the database through a maintenance plane or a scheduled task/batch file like below if you are running a free/express edition. That will create a back up file of the live mdf/ldf files which can then be easily accessed by your backup solution without having to worry about the files being in use or taking the system down. 

                 

                Regards,

                Steven

                 

                @echo off
                
                REM ============================================================================================
                REM ==                                                                                        ==
                REM ==    INSTRUCTIONS FOR CONFIGURATION AND USE                                            ==
                REM ==  -----------------------------------------------------------------------------------  ==
                REM ==  1) Ensure that the backup directory/folder (defined below) actually exists.          ==
                REM ==  2) Ensure that the SQL server instance name is correct or removed (default instance)  ==
                REM ==  3) Ensure that the SQL server credentials are entered correctly (-U user -P password)  ==
                REM ==  4) Verify batch file completes the backup, then schedule it (Windows Scheduled Task)  ==
                REM ==                                                                                        ==
                REM ============================================================================================
                
                SET BACKUP_DIR=C:\SQL
                
                REM Enter servername\instancename below (if installed as the default instance, all you need is the servername)
                SET SERVER=MyServer\MSSQLSERVER
                
                for /f "tokens=2" %%d in ('echo %date%') do (
                    for /f "tokens=1-3 delims=/" %%j in ('echo %%d') do (
                      set month=%%j
                      set day=%%k
                      set year=%%l
                    )
                )
                
                set /a day=%day%-1
                if %day% lss 10 (
                  set day=0%day%
                )
                
                set year=%year:~2,3%
                set mydate=%month%%day%%year%
                
                for /f "tokens=1-2 delims=: " %%a in ('time /t') do set XTime=%%a%%b
                
                sqlcmd -S %SERVER% -U sa -P ******* -d pharos -Q "exec sp_msforeachdb @Command1 = N'BACKUP DATABASE [pharos] TO DISK=''%BACKUP_DIR%\Pharos.Full.%mydate%.%XTIME%.bak'''"
                
                1 of 1 people found this helpful
              • Re: Stopping Pharos services in a particular order for backup
                Scott Olswold Guide

                Steve,

                You can schedule a database backup, or perform one manually, even if the database is in use. Here's why: When a backup is requested, the SQL Server service "freezes" the data file (*.MDB) but allows the database to be "writeable" by pushing everything to the transaction log file (*.LDF). At the same time, it records the highest transaction log value so that, when the backup is finished, it knows at what transaction to start writing to catch the data file up. But saying that, there are conditions within a "live" database that can prevent a successful backup (not enough space on the destination, permissions on the folder are insufficient for the account running the backup, full transaction log....). If the database needed to be put in single-user mode, or the applications that access it couldn't be running, there would be a LOT of people unhappy with Microsoft and an equal number very happy with other competitive database management software.

                 

                The key in your question is where and why it is bombing out. If you run a manual backup via SQL Management Studio and there is a failure, it will tell you why. If you do the same thing as a SQL Agent job, it'll tell you why there, too (but you have to dig under History). Sometimes the error messages are misdirecting, but they are often dead on.

                -Scott

                1 of 1 people found this helpful
                • Re: Stopping Pharos services in a particular order for backup
                  Steve Watson Wayfarer

                  Sorry gang, I might not have been as descriptive as I should have been...

                  Database backup runs fine. I'm trying to schedule a SERVER backup (OS, etc...) from Windows Server 2008 r2, so I need the database and any running Pharos services stopped before the backup because it keeps making the backup quit before it starts.

                   

                  I'll check the dependencies page linked to here. That may have the info I need.

                   

                  Thanks to all for your help!