How-To - ArcFM - Back Up a Microsoft SQL Server Database for Transfer to Technical Support

Version 16

    Solution

     

    This document describes the steps required to perform a SQL Server database backup using SQL Server Management Studio (SSMS) when your data is requested by a Schneider Electric Technical Support Engineer. These steps are applicable to Microsoft SQL Server versions 2008r2, 2012, 2014, and 2016. Every attempt has been made to ensure that these steps are generic and applicable to all system configurations. If you experience any issues following these steps, please contact the Support Engineer assigned to your case.

     

    Note: During the backup there must be NO active edit sessions open in ArcGIS. If possible, ensure that there are no connections to the database. If that is not feasible, at minimum make certain that no one is actively editing the database in any ArcGIS product.

     

    1. Connect to the instance as the 'sa' user or a user with the sysadmin server role assigned. Determine the version and edition of the SQL Server instance by executing the following statement in a query window:

       

      SELECT @@VERSION;

       

      This returns the version and edition of SQL Server. If the edition returned is Enterprise Edition, please contact the Support Engineer assigned to your case. We restore the database to SQL Server Standard Edition, and there may be features enabled in Enterprise Edition that prevent a successful restore to Standard Edition. The Support Engineer then discusses this with our Database Administrator and sends instructions on how to proceed.

     

    If the edition returned is Standard Edition, then proceed to Step 2.

     

    1. Ensure that no sessions exist in the database by executing the following in a query window:

       

      sp who2

       

      This returns all logins connected to any of the databases in the instance. Scroll through the list to ensure that there are no entries for the database that is to be backed up.

    2. Check the size of the Transaction Log(s) by executing the following query against the database to be backed up:

       

      select sum(size*8192/(1000*1000*1000))[TXN_LOG_SIZE_GB]

      from sys.database_files

      where type_desc = 'LOG'

       

      --backup Transaction Log (change to your path/file)

      backup log [database_name} to disk='c:\backup\[log_backup_name].trn'

       

      --shrink Transaction Log(s)

      select 'DBCC SHRINKFILE (' + name + ', 1);'

      from sys.database_files

      where type_desc = 'LOG'

       

      --execute the statement(s) returned by the query above

       

      --recheck the size of the Transaction Logs(s)

      select sum(size*8192/(1000*1000*1000))[TXN_LOG_SIZE_GB]

      from sys.database_files

      where type_desc = 'LOG'

       

    3. In Object Explorer, right-click the database to be exported and select Tasks > Back Up.

    4. On the General page, click Add and browse to the location where the backup file is to be saved. When naming the file, add the .bak extension to the end of it. Click OK to start the backup.

    5. Repeat Steps 2 through 5 for each ArcGIS database (for example, you may have a PROCESS or RESPONDER database in addition to the database containing the GIS data).

    6. Compress the backup file(s) and provide them along with the SQL Server version information generated in Step 1 to the Support Engineer assigned to your case.

      Note: The files must be compressed or they may not upload to the ftp site provided by Technical Support.