Archive:SEP sesam Microsoft SQL Server

From SEPsesam
Revision as of 12:32, 30 March 2018 by Sta (talk | contribs) (Corrected because of GUI change (backup type to backup level or to task type).)

Template:Copyright SEP AG en

Introduction

MS SQL Server consists of several Windows Services, the main service is MSSQLServer and additional services are MSDTC and SQLServerAgent. The database files of a standard installation are located under C:\<MSSQL_HOME> whereas a database is represented with a data file .mdf and a log file .ldf.

The source for a MS SQL Server backup must contain the SQL Server database name. If the database belongs to a SQL Server instance then the server and the instance must explicitly be given: <servername>/<instance>/<database>.

The names of the instances and databases may be retrieved with SQL Server Management Studio. SEP sesam provides an option to browse the database sources.

Key features

SEP sesam supports the following backups of SQL Server:

  • Full backup: A full database backup backs up the whole database stored on MS SQL server, including the transaction log. Full backups provide a duplicate of the database and enable database restore and recovery. Backups are performed at the database instance level.
  • Differential backup: Only the data that has changed since the last database backup is backed up. The full backup upon which a differential backup is based is the base of the differential. Note that restoring a differential backup requires its base to be restored first.
  • Transaction log backup (INCR): SEP sesam uses INCR backup to back up the transaction log. This backup level is relevant only for SQL Server databases that are using the full or bulk-logged recovery model. It requires at least one full backup to be performed before creating any log backups. Incremental (log) backups contain .TRN files and provide all transactions that have changed since the last FULL, DIFF or INCR.
SEP Tip.png Tip
Plan your SQL Server backups carefully. SEP recommends using the full recovery model. As for the backup strategy, a typical backup schema may include weekly full database backups, daily differential backups and frequent intervals of the transaction log (INCR) backup, e.g., every 15 minutes. Note that the optimal interval for each backup level depends on the importance of the data, database size, frequency of changes, server workload etc.

Requirements

To ensure error-free operation of SEP sesam and improve performance, make sure that the following conditions are met:

Limitation

  • In case of Microsoft SQL Server 2012/2014/2016 replication scenario, only the Publisher database is supported for backups. Backup of replicated Distributor or Subscriber database is not supported.

Configuration

Specifying the user account for the SEP sesam client service

  1. On the MS SQL server, configure the SEP sesam client service with a user account that has appropriate permissions to connect to all SQL databases; typically, this is a user with domain administrator privileges.
  2. Information sign.png Note
    MS SQL backup is not supported if the SEP sesam client service is running under the Local System account!

    Open the Windows service management console on your MS SQL server, edit the properties of the SEP sesam service, then switch to tab Log on and use valid credentials. Ensure that the specified user has appropriate permissions for backing up and restoring SQL databases.

    System account sql.PNG

  3. After this step restart the SEP sesam daemon. For details, see How to Start and Stop SEP sesam.

Selecting a recovery model for your MS SQL database

Before you configure SEP sesam backup for your MS SQL databases, you have to set up some database configurations to ensure your SQL databases are backed up correctly. MS SQL server provides different recovery models; you select the appropriate recovery model in your SQL Server Management Studio:
Right click on the database name, select Properties, select the Options tab and then select the recovery model from the drop-down list (the difference between the models is explained below). Click OK to save your selection.

The following three recovery models are available: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. For detailed explanation, refer to Microsoft article Recovery Model Overview.

Simple recovery model
Provides a simple backup that can be used to recover the entire database only to the most recent backup, because the transaction log is not backed up. With this recovery model all transactions since the last backup are lost, thus exposing you to any failures since the last backup completed. The following note recaps the Microsoft article Backup Under the Simple Recovery Model about the data loss exposure:
Information sign.png Note
The simple recovery model is inappropriate for production systems for which loss of recent changes is unacceptable. In these cases, you should use the full recovery model.
Full recovery model
The most complete recovery model that allows a database to be recovered to any point in time as long as all backup files are usable. Note that transaction log backups are required. SEP sesam provides the transaction log backup with INCR backup level. Therefore, the full recovery model must be used when backing up a database with SEP sesam FULL/DIFF/INCR (transaction log backup) backup level.
Bulk logged model
An adjunct of the full recovery model which does not fully log certain bulk operations, such as BULK INSERT, CREATE INDEX, SELECT INTO, etc... in the transaction log, thus reducing log space consumption. Note that bulk-logged recovery model does not support point-in-time recovery, but it is possible to recover to the end of the latest FULL, DIFF and INCR (transaction log) backup.
Information sign.png Note
Depending on the selected recovery model, you have to choose the appropriate SEP sesam backup level. For example, if you want to utilize the transaction log backups (SEP sesam INCR backup), you have to use the full recovery model, otherwise an error is issued, as described in the following success status section.
SEP sesam MS SQL backup success status
Depending on SEP sesam backup level (COPY or FULL/DIFF/INCR), the SQL database recovery model and specified backup source, the backup will issue warnings or fail if a transaction log backup (INCR) is performed on database which does not support log backups (is configured with simple recovery model). MS SQL backup success status can be one of the following:
  • COPY and DIFF backups can be used for any SQL database recovery model. These backups should finish successfully with simple, full or bulk-logged model.
  • When a database uses simple recovery model and in the backup task this particular database is set as a source, e.g., my_database, then COPY/FULL/DIFF backups will finish successfully but INCR will fail with message:
  • Error: DB Module: [Incremental backup for database with simple recovery model]
  • When a database uses simple recovery model and in the backup task the source is set to multiple databases (e.g., with specified instance or as 'all'), which means that this database is backed up in one session together with other databases, then only COPY and DIFF backups are successful. FULL/INCR backup will end with warning database not logged for the respective database:
  • Warning: Item [<database_name>] is not logged. Recovering may fail.
  • As of SEP sesam Tigon V2, there are some exceptions when logging the backup status for the system databases master, model and msdb. If these databases are configured with simple recovery model and a FULL/INCR backup is performed, the missing transaction log backup for these system DBs is ignored and no warnings are issued during the backup.
  • This new no-warning behavior is based on recommended backup strategy for system databases: typically, master and msdb databases are using simple recovery mode (although the recovery mode can be set to FULL for master DB, transaction log backup cannot be performed); the model database sets the default recovery model of new databases and should have the recovery mode set to FULL. For details, see Microsoft article Back Up and Restore of System Databases (SQL Server).
SEP Tip.png Tip
If you have a mixture of databases on the same SQL Server, i.e. some with simple and other with full recovery mode, create separate backup jobs for DBs with simple and DBs with full model.

Transaction log truncation

SQL Server database transaction log records all transactions and related database modifications. Transaction log backups are essential if you want to recover the database to a specific point in time or to the point of failure. Point-in-time restore is only possible with SQL database full recovery model (see section Selecting a recovery model for your MS SQL database, which requires scheduled FULL (database backup) as well as INCR (log) backups to get the unbroken log chain that enables the database restore to any point in time.

After the transaction log (INCR) backup, Microsoft SQL Server and Microsoft SQL Server Express databases automatically truncate the inactive portions (these are completed transactions that are no longer required for recovery) of the transaction log thus preventing the transaction log to grow and fill up. Note that it cannot be truncated until all of its records are captured in a log (INCR) backup! Therefore, under the SQL database full recovery model the log files will not be truncated at all without the INCR backup.

As specified in the Microsoft article The Transaction Log (SQL Server), log truncation typically occurs automatically after the following events:

  • When simple recovery model is used, after a checkpoint.
  • When full recovery model or bulk-logged recovery model is used, if a checkpoint has occurred since the previous backup, truncation occurs after a log backup (unless it is a copy-only log backup).
Information sign.png Note
  • Some factors, such as a long-running transaction, can delay log truncation, therefore you should monitor log size regularly. You should not truncate the transaction log manually as this will break the backup chain.
  • Transaction log (INCR) backup is based on FULL backup, so make sure that at least one successful full backup exists before creating any log backups.

For details on managing the transaction logs, see Microsoft article The Transaction Log (SQL Server).

Adding the MS SQL client to SEP sesam environment

Once you have downloaded the SEP sesam package, configure your MS SQL client by adding it to the SEP sesam environment: Main selection -> Components -> Topology -> New client -> add your MS SQL client. For details, see Configuring Clients.

Information sign.png Note
Before proceeding, you should validate that you can backup to SEP sesam. Run a test Path backup manually to ensure that the backups work. For details on typical backup configuration, see Standard Backup Procedure.

Configuring MS SQL database backup

To back up your SQL server data, you have to create a backup task (select the data to be backed up), then set up a backup schedule (specify when you want to back up your data), and link a backup event to it – in this 3rd step you select the appropriate SEP sesam backup level depending on the used recovery model of your SQL databases. For example, if you want to utilize the transaction log backups (SEP sesam INCR backup), you have to use the full recovery model, otherwise an error is issued. For details, see section Selecting a recovery model for your MS SQL database. For general details on how to configure a backup and its prerequisites, see Standard Backup Procedure.

SEP Tip.png Tip
If you have a mixture of databases on the same MS SQL Server, i.e. some with simple and other with full recovery mode, create separate backup tasks for DBs with simple and DBs with full model.

Create a new backup task to backup all or only selected databases on an instance.

  1. From Main Selection -> Tasks -> By clients, select MS SQL client and click New backup task. The New backup task window opens.
  2. Specify the Source with the database name. Browse for the database (or all databases) that you want to include in the backup and select it. You can also enter the source manually with the following values:
    • <HOSTNAME>/<Instance>/<DB_name>: Specify a backup of specific database; the database name is case-sensitive.
    • all: Specify a backup of all databases on an instance.
    By selecting the source, the task type and task name are set automatically.
  3. MS SQL bck 2.png
  4. You may want to exclude some databases on an instance from the backup. Use the Exclude List Editor to specify the exclusions by using regular expressions. Add the exclusion patterns one by one followed by a comma. You can also use a file to define a backup source to be excluded. For details on different exclude options, see Creating Exclude List.
  5. To specify additional options for backup, switch to the Options tab and enter options in the Save options field.
  6. If you want to assign a task to the schedule, click the corresponding tab in the New backup task window.
  7. Click OK to create the task.
  8. If you want to start the newly created task immediately, right-click the name of the task and click Immediate start. If you want to schedule the task, click New schedule under Main Selection -> Scheduling -> Schedules. For details, see Creating a Schedule.
  9. Once you have configured a schedule, you must create a new backup event for it. Go to Main Selection -> Scheduling -> New Backup Event, click the Parameter tab and select one of the available backup levels. For more information on schedules and related events, see Schedules 4.4.

Backup of a Database in 1st Instance

In this example database master in 1st Instance of MS SQL Server COSINUS should be backed up. Therefore a new Backup Task with Task type MS-SQL Server and following source

 COSINUS/Master

must be inserted with SEP sesam GUI.

Hint Use NETBIOS host name as Server Host name.

Mssql task example.JPG

Backup of a Database in a further Instance

In this example a database from a further Instance of a SQL Server should be backed up.

Mssql enterpr man2.JPG

If database master under a further Instance should be backed up then give the following source:

 COSINUS/ZWEITE_DB/master

Mssql db instanz2.JPG

SQL Server Check on Command line

Browsing Database Sources

The switch '-D' (directory) allows the browsing of MS SQL Server sources.

As starting root the following possibilities exists:

  • "sbcmsql:/Net:" All net reachable MS SQL Servers
  • "sbcmsql:/NetInstances:" All net reachable MS SQL Servers with Instances
  • "sbcmsql:/MS SQL Server:[/<server>[/<instance>]]" All on locale server reachable MS SQL Servers and further levels with Instances and Databases
  • "sbcmsql:/MS SQL Server:/<server>/<instance>/<database>" Logical and physical file names - delimiter ':'

Examples:

sbc -D "sbcmsql:/Net:"
"/MS SQL Server:/BUCHFIX2" d_ 2007.10.17 18:02:01 2007.10.17 18:02:01
"/MS SQL Server:/COSINUS" d_ 2007.10.17 18:02:01 2007.10.17 18:02:01
"/MS SQL Server:/SEHNIX" d_ 2007.10.17 18:02:01 2007.10.17 18:02:01
sbc -D "sbcmsql:/NetInstances:"
"/MS SQL Server:/BUCHFIX2" d_ 2007.10.17 18:02:26 2007.10.17 18:02:26
"/MS SQL Server:/COSINUS" d_ 2007.10.17 18:02:26 2007.10.17 18:02:26
"/MS SQL Server:/COSINUS\ZWEITE_DB" d_ 2007.10.17 18:02:26 2007.10.17 18:02:26
"/MS SQL Server:/SEHNIX" d_ 2007.10.17 18:02:26 2007.10.17 18:02:26
sbc -D "sbcmsql:/MS SQL Server:"
"/MS SQL Server:/MIRACULIX" d_ 2007.10.17 18:02:52 2007.10.17 18:02:52
sbc -D "sbcmsql:/MS SQL Server:/MIRACULIX"
"/MS SQL Server:/MIRACULIX/(local)" d_ 2007.10.17 18:03:11 2007.10.17 18:03:11
"/MS SQL Server:/MIRACULIX/SECOND" d_ 2007.10.17 18:03:11 2007.10.17 18:03:11
"/MS SQL Server:/MIRACULIX/SQLSERVER2005" d_ 2007.10.17 18:03:11 2007.10.17 18:03:11
"/MS SQL Server:/MIRACULIX/SQLSERVER2005B" d_ 2007.10.17 18:03:11 2007.10.17 18:03:11
sbc -D "sbcmsql:/MS SQL Server:/MIRACULIX/(local)"
/MS SQL Server:/MIRACULIX/(local)/master fb 2003-04-08 09:13:36.390 07.10.17 18:06:33. 4096 -,
/MS SQL Server:/MIRACULIX/(local)/model fb 2003-04-08 09:13:36.390 07.10.17 18:06:33. 4096 -,
/MS SQL Server:/MIRACULIX/(local)/msdb fb 2005-10-14 01:54:05.240 07.10.17 18:06:33. 4096 -,
/MS SQL Server:/MIRACULIX/(local)/msdb2 fb 2007-10-10 12:55:46.030 07.10.17 18:06:33. 4096 -,
/MS SQL Server:/MIRACULIX/(local)/sesam_db fb 2007-09-20 14:17:04.730 07.10.17 18:06:33. 4096 -,
/MS SQL Server:/MIRACULIX/(local)/sesam_db2 fb 2007-09-20 16:21:01.030 07.10.17 18:06:33. 4096 -,
/MS SQL Server:/MIRACULIX/(local)/tempdb fb 2007-10-17 18:06:27.747 07.10.17 18:06:33. 4096 -,  
not_saveable only for temporary operations
/MS SQL Server:/MIRACULIX/(local)/testdb fb 2007-09-24 16:19:36.123 07.10.17 18:06:33. 4096 -,
sbc -D "sbcmsql:/MS SQL Server:/MIRACULIX/SECOND"
/MS SQL Server:/MIRACULIX/SECOND/AdventureWorks fb 2007-08-16 16:17:06.717 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/AdventureWorksDW fb 2007-08-16 16:16:45.640 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/master fb 2003-04-08 09:13:36.390 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/model fb 2003-04-08 09:13:36.390 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/msdb fb 2005-10-14 01:54:05.240 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/sesam_db fb 2007-10-10 13:26:53.310 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/sesam_db2 fb 2007-10-10 15:09:26.200 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/sesam2 fb 2007-10-10 15:13:49.607 07.10.17 18:05:54. 4096 -,
/MS SQL Server:/MIRACULIX/SECOND/tempdb fb 2007-10-17 18:05:41.967 07.10.17 18:05:54. 4096 -,  
not_saveable only for temporary operations
sbc -D "SBCMSQL:MS SQL Server:/miraculix/(local)/sesam_db" 2>nul
/MS SQL Server:/miraculix/(local)/sesam_db/sesam_db:"D:\Programme\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sesam_db.mdf" f_ 07.11.28 13:07:36. 07.11.28 13:07:36. 4096 -,
/MS SQL Server:/miraculix/(local)/sesam_db/sesam_db_log:"D:\Programme\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sesam_db_log.ldf" f_ 07.11.28 13:07:36. 07.11.28 13:07:36. 4096 -,

Backup

Identical sources due to automatic expansion of server and 1st instances: (Instance "(local)" for instance without specific Name)

sbc -b -s @sesam_db.save -v 3 sbcmsql:/MIRACULIX/(local)/sesam_db
sbc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/(local)/sesam_db
sbc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/sesam_db
sbc -b -s @sesam_db.save -v 3 sbcmsql:sesam_db

Wrong is source prefix "/MS SQL Server:", e.g. "/MS SQL Server:/MIRACULIX/(local)/sesam_db" is wrong!


Hint: Further Instances must explicitly be given.


Examples:

sbc -b -s @sesam_db.save -v 3 sbcmsql:/MIRACULIX/SECOND/sesam_db
sbc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/SECOND/sesam_db

Restore

Restores should be executed with option 'Overwrite' and 'Recover'.

If a restore was completed and option 'Recover' was not given then the following command allows a recover:

sbc -r -a recover -v 3 sbcmsql:MIRACULIX/SECOND/sesam_db

Restore to Original

Restore to Original with 'Overwrite' und 'Recover' for the given example:

sbc -r -s @sesam_db.save -o over -a recover -v 3 sbcmsql:MIRACULIX/SECOND/sesam_db

Hint:

If the database was online during the restore then the changes are not visible. It is necessary to set the database offline and online again to access the result of the restore.

Restore after Removal of Database or to a Different Instance

If the original database was removed or database should be restored to a different instance then the path for the database must be created before restore.

If the path does not exist then the following message appears in the protocol:

Example:

Directory lookup for the file "C:\Programme\Microsoft SQL Server\MSSQL$ZWEITE_DB\data\sesamdb_Data.MDF" 
failed with the operating system error 3(The system cannot find the path specified.).
File 'sesamdb_Data' cannot be restored to 'C:\Programme\Microsoft SQL Server\MSSQL$ZWEITE_DB\data\sesamdb_Data.MDF'. 
Use WITH MOVE to identify a valid location for the file.

If the database files should be restored under a different location then use the Move-Option, see SEP sesam Microsoft SQL Server#Restore to a Different Database Location - Move Option.

Restore to Identical Location

Is the location identical to original database, with identical logical File Names, then restore can directly done with:

sbc -r -s @sesam_db.save -o over -a recover -v 3 sbcmsql:"/MIRACULIX/SECOND/sesam_db"

Please check location and logical file names within Microsoft SQL Server Management Studio or with following command: (name == logical File Name)

osql -E -S MIRACULIX\SECOND -Q "select name,physical_name,state_desc from sys.database_files" -d sesam_db 
name        physical_name           state_desc
-----------------------------------------------------------------------------------------------------------------------
sesam_db       D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db.mdf       ONLINE
sesam_db_log   D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db_log.ldf   ONLINE


Hint For MS SQL Server 2000 the statement is: (example)

osql -E -S COSINUS\ZWEITE_DB -Q "select * from sysfiles" -d sesamdb


Hint

Retrieve all with:

osql -E -S w2003enterprise -Q "select name, filename from sysdatabases" -d master

Restore to a different database location WITHOUT move option

If the database was created with a different logical File Name then the Restore possible using either the 'MOVE TO' clause (command) or by changing the logical File Name in the 'SQL Server Management Studio'. Here the way with changing the logical filename.

The name of the original database is OriginalDB and the name of the restore database is RestoreDB.

  1. After a successful backup of OriginalDB create the restore database named RestoreDB by MS-SQL Management Studio. During The creation dialog select a different database location.
  2. Rename the default database logical names (RestoreDB and RestoreDB_log) to the original database logical names which should be restored (here OriginalDB and OriginalDB_log)
  3. Rename the default database filenames (RestoreDB.mdf and RestoreDB_log.ldf) to the original database filenames which should be restored (here OriginalDB.mdf and OriginalDB_log.ldf)
  4. After creation of the RestoreDB set the database offline
  5. Start Sesam Restore Wizard and select the database OriginalDB for restore.
  6. On the Restore Wizard page Save and start set the value new restore target and set the value W2K8R2SQL/MSSQLSERVER/RestoreDB. Choose the execution options Overwrite existing files and Auto recover after restore.
  7. Start the restore
  8. After the restore was successful set the RestoreDB database online.

Restore a database to a new location by using GUI relocation or WITH MOVE option

If you want to restore a Microsoft SQL database to the same machine, but to a different database name and location without affecting the original database, use one of the following two options depending on your SEP sesam version.

Use GUI restore wizard relocation (≥ 4.4.3.45 Tigon V2)

As of 4.4.3.45 Tigon V2, you can use the Restore wizard to select MS SQL database logical file names and configure relocated physical file names.

  1. Use the restore wizard as described in Standard Restore Procedure: select the save set you want to restore, then select the desired version and set the complete restore option.
  2. When you get to the last step of the restore wizard, leave options as they are set by default and only select New restore target under the Target path settings. Then click the button Expert Options.
  3. In the new window Restore: Expert Options, switch to the Relocation tab. You have to specify a new path for your database files (as you do with the move option which is explained below). In the field will be restored directly in the following path, the original file path is listed. Simply type a new path to the database and log file. For example, the current structure as shown in the below screenshot is as follows:
  4. C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\AdventureWorks2012_Data.mdf
    C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\AdventureWorks2012_log.ldf Now set a new path for the database. MS SQL relocation en.jpg
  5. Click OK; to start your restore immediately, click Start. Otherwise, save your restore task.

Use the move option (≤ 4.4.3.42 Tigon)

For versions ≤ 4.4.3.42 Tigon, the move operation must be used to relocate each of the database files and to avoid collision with existing files.

If the database was created with a different logical file name, then it is only possible to restore it using the move to clause (command) or by changing the logical file name in the SQL Server Management Studio.

Use the move option as follows:

-a move={Original logical_name}:"New file_name_mdf" -a move={Original logical_name_ldf}:"New file_name_ldf"


Example:

osql -E -S MIRACULIX\SECOND -Q "select name,physical_name,state_desc from sys.database_files" -d sesam2 
name        physical_name           state_desc
-----------------------------------------------------------------------------------------------------------------------
sesam_db2       D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db2.mdf       ONLINE
sesam_db2_log   D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db2_log.ldf   ONLINE

Possible restore command to move the backup of the database sesam_db to sesam2.

sbc -r -s @sesam_db.save -o over -a recover 
-a move=sesam_db:"D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db2.mdf" 
-a move=sesam_db_log:"D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam2_db2_log.ldf" 
sbcmsql:"/MIRACULIX/SECOND/sesam2"
SEP Warning.png Warning
When using the move option, make sure that the specified target directory name already exists. Pay attention not to overwrite the database files by mistake!

Hints:

  • The move option -a move=.:. can be entered as text (without changing lines) in the Restore wizard under Expert Options-> Option tab.
  • If the database is in use, e.g., by opening a table with Open Table in the SQL Server Management Studio, a restore will fail. That means before a restore you should make sure that the database is offline.
  • SEP sesam does not always know when a restore was actually successful, therefore after a restore you should always check the restore log file in the Error report window.
  • If the SEP sesam Server is working with a Postgres database, e.g., on Linux x64, then you must enter the \-character twice, if not entered twice it will disappear.
  • By performing the restore with the move option the logical file names of the target data base will adjusted.

Example: After a restore the adjusted logical file names will appear as follows:

osql -E -S MIRACULIX\SECOND -Q "select name,physical_name,state_desc from sys.database_files" -d sesam2 
name        physical_name           state_desc
-----------------------------------------------------------------------------------------------------------------------
sesam_db       D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db2.mdf       ONLINE
sesam_db_log   D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam_db2_log.ldf   ONLINE

Restore of a MS SQL Server 2000 Database to a MS SQL Server 2005

A database of a MS SQL Server 2000 instance can be restored to a MS SQL Server 2005.The database will be upgraded automatically after the restore.

Attention

MS SQL Server 2005 databases can not be restored to a MS SQL Server 2000 instance!

Disaster Recovery

In the event of a Disaster Recovery Situation, typically it is necessary to recreate an SQL Server or to recreate the master Database on an existing SQL server, you must also recreate the system Databases.


Hint:

To recreate the Master Database you can use the Rebuildm.exe Utility located at:

\..\mssql7\binn\ oder \..\Microsoft SQL server\80\Tools\Binn\ for SQL 2000/2005.


It should be noted that you should use the same 'Sort Order' and 'Code Page' as the SQL Server that is to be recovered.

Hint:

If a new SQL Server is going to be installed you should load the actual Service Packs.

You must then start the SQL Server with the Switch '-m' in Single User Mode, if this is for an Instance then the Instancename with '-s <instancename>' should be entered:

sqlservr.exe -m [-s <instancename>]

Afterward you can utilize the SEP sesam GUI for the restore of the master Database. Independent from the original Directory the Files from the master Database will be entered in the 'new' MS SQL Server requires them to be entered.

After the SQL Server will be started normally, i.e. as Service.

Now the msdb Database will be restored. Thereafter, the model Database.

Hint:

You should note that the path, also when using the 'MOVE' Option - must be present and if necessary the 'Overwrite' Option should be selected.

After the System Databases are again available the other Databases will be restored.

Hint:

If the File Path in the restored Master Database is different than the actual File Path of the Model Database the SQL Server Start will not be successful. This can be avoided. The Model Database Files model.mdf and modellog.ldf should be moved to the Directory that is predetermined (default) by the Master Database.

Troubleshooting

Login incorrect

Problem: If a Restore ends with errors and you find the following information in the Log Files:

DB Module: [DB-Library: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
DB Module: [DB-Library message: Login incorrect.]

an attempt has been made to address a SQL Server Instance on a client that is not entered locally on this system.

The selected "Trusteed Connection" only allows a registration on a SQL Server where the Instance is locally active.


Solution: In this case the Backup Client or the End Node must be changed on the active SQL Server.


Backup fails with 'The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "<database>" under the current security context.'

Problem: User "SYSTEM" is not allowed to connect to MS-SQL database, because the SEP sesam daemon runs as user "SYSTEM" by default.


Solution: In this case the SEP sesam daemon has to be changed to a user account, who is allowed to connect to the database(s). Open the Windows service management console on your MS-SQL server, edit the properties of the SEP sesam service, switch to tab "Log on" and use valid credentials. After this step restart the SEP sesam daemon.


Restore fails with "Directory lookup for the file '...' failed"

Problem: If you receive this response:

DB Module: [DB-Library: Directory lookup for the file "e:\Database\SQL Server 2000 SE\MSSQL\Data\sesam.mdf" 
  failed with the operating system error 21(The device is not ready.).]
DB Module: [DB-Library: File 'sesam_db' cannot be restored to 'e:\Database\SQL Server 2000 SE\MSSQL\Data\sesam.mdf'. 
  Use WITH MOVE to identify a valid location for the file.]

Then the existing Path where the Database Files were located is not on the Target System or an entry was made in the 'Move' Option that used a non-existing path name.


Solution: There must be a pathname created or the correct path must be entered in the 'Move' Option. Entering the correct path name is a much easier solution. Otherwise, it can be problematic when using a long path name in the 'Move' Option. In this case the input can be shortened.

Restore fails "Unable to connect: SQL Server does not exist or network access denied."

Problem: If this occurs:

DB Module: [DB-Library message: Unable to connect: SQL Server is unavailable or does not exist.  
  Unable to connect: SQL Server does not exist or network access denied.; Net-Library message: ConnectionOpen (Connect()).; ]

then the Server selected does not exist. It is possible that an Instance was incorrectly entered without the Server name.

Solution: Check the Server name, if necessary enter the Restore Target fully qualified as follows:

<HOSTNAME>/<Instance>/<DB Name>

Restore using the 'MOVE' Option fails with "The physical file name '...' may be incorrect"

Problem: If the following error occurs

DB Module: [DB-Library: A file activation error occurred. The physical file name 'c:/temp/sesam_log.ldf' may be incorrect. 
  Diagnose and correct additional errors, and retry the operation.]
DB Module: [DB-Library: File 'sesam_db_log' cannot be restored to 'c:/temp/sesam_log.ldf'. 
  Use WITH MOVE to identify a valid location for the file.]

Then a wrong syntax was used in the file name, e.g. / instead of \.

Solution: Enter the path with the correct syntax.

Attention: If the SEP sesam Server is using a Postgres database, e.g. with Linux x64, then the character '\' must be entered twice '\\', otherwise it will disappear!

Example:

-a move=Mgmt_data:"e:\\SQL Server 2000 SE\\MSSQL\\Data\\Mgmt.mdf" -a move=Mgmt_log:"e:\\SQL Server 2000 SE\\MSSQL\\Data\\Mgmt_log.ldf"

Restore with MOVE Option fails with "Logical file '...' is not part of database"

Problem:

If the error

DB Module: [DB-Library: Logical file 'Mgmt_data' is not part of database 'sesam_db2'. 
  Use RESTORE FILELISTONLY to list the logical file names.]

appears then a wrong Logical Name was entered in the 'Move' Option.

Solution: Enter the Logical file name correctly. This can be found in the Backup Log File (NOT-File).

Example: In the Backup Log file you see the following lines:

DB Module: [DB-Library: Processed 256 pages for database 'sesam_db', file 'sesam_db' on file 1.]
DB Module: [DB-Library: Processed 1 pages for database 'sesam_db', file 'sesam_db_log' on file 1.]

The logical file names in this situation are sesam_db und sesam_db_log. These should be entered in the 'Move' Option.

Restored Database remains in state 'Restoring'

Problem: A database remains in state '(Restoring...)' after the restore finished. This happens if option 'Auto Recover' was not selected in Restore Wizard.

Solution: Select option 'Auto Recover' in Restore Wizard. Or execute sbc on CLI with option -a recover for particular database.

Example:

MSSQL restoring.jpeg

Call sbc on command line with:

sbc -r -a recover sbcmsql:"/MIRACULIX/SECOND/msdb"

Hint During restore of a database with some additional transaction log files (generation restore) the database remains in state 'Restoring...' until last restore (with option '-a recover') ends.

Further Links/Literature