SEP sesam Microsoft SQL Server
From SEPsesam
(C)SEP AG
Copyright 1999-2009 by SEP AG. All Rights reserved.
Any form of reproduction of the contents or parts of this manual is allowed only with the express written permission from SEP AG. When compiling and designing user documentation SEP AG uses great diligence and attempts to deliver accurate and correct information. However, SEP AG cannot issue a guarantee for the contents of this manual.
Introduction
MS SQL Server 2000/2005 consists of several Windows Services, the main service is MSSQLServer. Two further 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".
Since SEP sesam Version 3.4 the database sources may be browsed.
System Requirements
Client Requirements
- MS SQL Server 2000 or 2005
- SEP sesam Client since version 3.2.1.17
- MSSQL Database Module is part of SEP sesam Client.
| Note |
|
"MS SQL Server 7.0" is not supported since SEP sesam Version 3.4. |
Source of MS SQL Server Database
Syntax for the source:
[<HOSTNAME>[/<Instance>]/]<DB Name>
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 Backup Type MS-SQL Server and following source
COSINUS/Master
must be inserted with Sesam GUI.
Hint
Use NETBIOS host name as Server Host name.
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.
If database master under a further Instance should be backed up then give the following source:
COSINUS/ZWEITE_DB/master
Log Truncation
Log Backups will be executed without option 'WITH NO_TRUNCATE' or option 'WITH COPY_ONLY'.
| Note |
|
Therefore it is very important to configure additional incremental backups to enable log file truncation! |
MS SQL Server Documentation:
SQL Server 2005 Books Online - Truncating the Transaction Log
Log truncation under the full and bulk-logged recovery models
Under the full recovery model or bulk-logged recovery model, all log records must be backed up to maintain the log chain—a series of log records having an unbroken sequence of log sequence numbers (LSNs). Therefore, the inactive portion of the log cannot be truncated until all of its log records have been captured in a log backup.
The log is truncated when you back up the transaction log, assuming the following conditions exist:
- The BACKUP LOG statement does not specify WITH NO_TRUNCATE or WITH COPY_ONLY.
- A checkpoint has occurred since the log was last backed up. A checkpoint is essential but not sufficient for truncating the log. After a checkpoint, the log remains intact at least until the next transaction log backup. For more information, see Checkpoints and the Active Portion of the Log.
- No other factor is preventing log transaction. Generally, with regular backups, log space is regularly freed for future use. However, various factors, such as a long-running transaction, can temporarily prevent log truncation. For more information, see Factors That Keep Log Records Active.
© 2007 Microsoft Corporation. All rights reserved.
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 physicale file names - delimiter ':'
Examples:
sbc_uc -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_uc -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_uc -D "sbcmsql:/MS SQL Server:" "/MS SQL Server:/MIRACULIX" d_ 2007.10.17 18:02:52 2007.10.17 18:02:52
sbc_uc -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_uc -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_uc -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_uc -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_uc -b -s @sesam_db.save -v 3 sbcmsql:/MIRACULIX/(local)/sesam_db sbc_uc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/(local)/sesam_db sbc_uc -b -s @sesam_db.save -v 3 sbcmsql:MIRACULIX/sesam_db sbc_uc -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_uc -b -s @sesam_db.save -v 3 sbcmsql:/MIRACULIX/SECOND/sesam_db sbc_uc -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 complete and option 'Recover' was not given then the following command allows a recover:
sbc_uc -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_uc -r -s @sesam_db.save -o over -a recover -v 3 sbcmsql:MIRACULIX/SECOND/sesam_db
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_uc -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 - Move Option
If the database was created with a different logical File Name then the Restore is only possible using the 'MOVE TO' clause (command) or by changing the logical File Name in the 'SQL Server Management Studio'.
To accomplish this use the 'Move' option.
Format for the 'Move' Option:
-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 comand to move the backup of the database sesam_db to sesam2.
sbc_uc -r -s @sesam_db.save -o over -a recover -a move=sesam_db:"D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam2.mdf" -a move=sesam_db_log:"D:\Programme\Microsoft SQL Server\MSSQL.3\MSSQL\DATA2\sesam2_log.ldf" sbcmsql:"/MIRACULIX/SECOND/sesam2"
Attention:
When using the 'Move' option it is imperative that entered Directory Name exists. In the event they do exist the entered File Names, as long as the 'Overwrite' Option is selected will overwrite. That means you should be careful that you do not overwrite 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.
- 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.
- SEPsesam doesn't 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 SEPsesam 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 SEPsesam 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 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 an SQL Server Instance on a client that is not entered locally on this sytem.
The selected "Trusteed Connection" only allows a registration an an 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.
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 SEPsesam 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:
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
SQL Server 2008 Books Online (November 2009) - BACKUP (Transact-SQL)
