(C)SEP AG
Copyright 1999-2011 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, 2005 and/or 2008
- 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:
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
must be inserted with SEP 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:
Log Truncation
| Note |
|
SEP strongly recommends to use the recovery model 'Full', see SQL Server Books Online SQL Server 2008 R2 (2010) Recovery Model Overview. |
Only with recovery model 'Full' no work is lost due to a lost or damaged data file and the databases can be recovered to an arbitrary point in time (for example, prior to application or user error).
The recovery model 'Full' requires log backups.
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! |
Original 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 physical file names - delimiter ':'
Examples:
Backup
Identical sources due to automatic expansion of server and 1st instances: (Instance "(local)" for instance without specific Name)
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:
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:
Restore to Original
Restore to Original with 'Overwrite' und 'Recover' for the given example:
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:
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:
Please check location and logical file names within Microsoft SQL Server Management Studio or with following command: (name == logical File Name)
Hint
For MS SQL Server 2000 the statement is: (example)
Hint
Retrieve all with:
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.
- After a successful backup of OriginalDB create the restore database named RestoreDB by MS-SQL Managment Studio. During The creation dialog select a different database location.
- 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)
- 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)
- After creation of the RestoreDB set the database offline
- Start Sesam Restore Wizard and select the database OriginalDB for restore.
- 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.
- Start the restore
- After the restore was successful set the RestoreDB database online.
Restore to a Different Database Location WITH 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'. Here the way with 'MOVE' option.
To accomplish this use the 'Move' option.
Format for the 'Move' Option:
Example:
Possible Restore comand to move the backup of the database sesam_db to 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.
- SEP sesam 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 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:
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:
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 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:
an attempt has been made to address an SQL Server Instance on a client that is not entered locally on this system.
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:
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:
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:
Restore using the 'MOVE' Option fails with "The physical file name '...' may be incorrect"
Problem: If the following error occurs
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:
Restore with MOVE Option fails with "Logical file '...' is not part of database"
Problem:
If the error
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:
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:
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
Request Call
Our sales team would be happy to assist you!
Media Library
Browse and discover videos by SEP.
