| By product |
| Other |
|
Knowledgebase Tips and how-to articles for ApexSQL tools ApexSQL Log Connection monitor (Part III) - Showing additional user data
Applies to ApexSQL Log Summary How to show and use the Connection monitor captured information Description After the stage has been set by setting up the ApexSQL Log Connect monitor service and the Connection monitor repository, it’s time to put it to good use. The Connection monitor stores additional data in the repository database that doesn't show up in the auditing reports by default Regarding connection specific information, only the User column is shown in the main grid, by default. This column shows the SQL Server user if SQL authentication is used, and windows user if otherwise. If you also need to see the application and its host name associated with a transaction, additional steps are required. Although the information is saved in the Connection monitor’s repository database, ApexSQL Log will associate it automatically to the corresponding transactions during the analysis Note that, to see the additional connection information, these additional columns in the ApexSQL Log’s main grid must be enabled prior to the analysis. It is not possible to enable them afterwards without restarting the entire process. This is the result of ApexSQL Log’s optimization; it will not perform any unnecessary analysis if not instructed to do so To enable additional columns in the ApexSQL Log’s main grid:
After processing transaction logs, the results are displayed in the ApexSQL Log’s main grid with connection specific information associated to each of the transaction log entries 
To summarize this ApexSQL Log Connection monitor article: it’s easy to set up and use if you need the connection specific information. On the other hand, if the additional information provided by the Connection monitor is not required, it is easy as well as recommended to turn it off. After all, ApexSQL Log is an optimized tool, but some additional optimization by setting options and filters properly is always welcome About ApexSQL Log
ApexSQL Log is a SQL Server Transaction Log reader that allows viewing transaction log data in read-friendly format. Audit and undo SQL database changes of your choosing. Determine who changed the data and when the change occurred. Read the transaction log to find out who created, changed or dropped a database object
Labels: ApexSQL Log ...ApexSQL Log Connection monitor (Part II) - Configuring repository database
Applies to ApexSQL Log Summary How to customize the location of the SQL Server repository database used by the Connection monitor to store captured information Description As discussed in the introduction and basic setup of the ApexSQL Log Connection monitor article, it is possible to customize the location of the SQL Server repository database used to store captured information. Similarly, it’s possible to configure the Connection monitor to:
To completely remove captured information from the database, click the Connection monitor manager on the Home tab of the main toolbar. Then click the Change button  Select the Data repository tab and then click the Remove all captured data now button, which brings up the confirmation dialog  Click Ok to close the Connection monitor settings dialog Disabling the Connection monitor service Select the Stop button to disable the Connection monitor service, and turn off additional logging  Be advised that this procedure doesn't remove the repository database or the structure in it Setting up automatic deletion of obsolete captured data Regarding the automatic removal of unneeded / obsolete captured information, the steps are identical to those described in the captured data removal procedure up to the point of opening the Data repository tab By selecting the Delete captured data after option and specifying the number of days, the Connection monitor will purge captured information automatically Now that the Connection monitor service and repository have been set up properly, let’s learn how to see the captured and stored data About ApexSQL Log
ApexSQL Log is a SQL Server Transaction Log reader that allows viewing transaction log data in read-friendly format. Audit and undo SQL database changes of your choosing. Determine who changed the data and when the change occurred. Read the transaction log to find out who created, changed or dropped a database object
Labels: ApexSQL Log ...ApexSQL Log Connection monitor (Part I) - Introduction and basic setup
Applies to ApexSQL Log Summary How to use the Connection monitor as a server side component of ApexSQL Log Description The Connection monitor is a server side component of ApexSQL Log, which captures and stores the SQL Server user name (or Windows user name if Windows authentication is used) and other connection specific information, such as application and host name of an application used to perform a transaction. Most of this information is not stored in the transaction log, but it is valuable in many use cases, when it’s associated with the corresponding transaction For instance, SQL Server 2000 stores only the SQL Server user ID, while SQL Server 2005, 2008, 2008 R2 and 2012 stores the SID of the user that initiated each transaction. ApexSQL Log is capable of mapping a user ID to the user name, or mapping an SID to the Windows username or the SQL Server user name, depending on the user account configuration without using the Connection monitor On the other hand, none of the SQL Server versions have a client host or an application name stored in its transaction logs. Therefore, to obtain the connection specific information and provide comprehensive database auditing, ApexSQL Log uses active connection monitoring The transactions executed while the Connection monitor service was enabled are displayed with extra information compared to the ones executed while it was disabled: 
The Connection monitor can be enabled during or after the installation of ApexSQL Log. If you check the Capture Login information option during the installation process, you will be prompted to specify a database in which to store captured data The default repository database, named ApexSQLLog, will be created if the Default option is selected. It is also possible to select any of the already available databases 
However, if you need to change this setting later, you can do it by selecting the Connection monitor manager on the Home tab. The Connection monitor service must be running in order to change the database used to capture data Click Start to enable/start the Connection monitor service 
Select Change after the Connection monitor’s status changes to Running 
Then, change the database used as the repository in the Connection monitor settings dialog, under the Data repository tab 
So, these were the steps for setting up the repository. Similarly, it’s possible to stop the Connection Monitor service or to purge stored information About ApexSQL Log
ApexSQL Log is a SQL Server Transaction Log reader that allows viewing transaction log data in read-friendly format. Audit and undo SQL database changes of your choosing. Determine who changed the data and when the change occurred. Read the transaction log to find out who created, changed or dropped a database object
Labels: ApexSQL Log ...ApexSQL Restore - Introduction ApexSQL Restore is a SQL Server tool that attaches both native and natively compressed SQL database backups and transaction log backups as live databases, accessible via SQL Server Management Studio, Visual Studio or any third-party tool. It allows attaching a single or multiple full, differential and transaction log backups as well as mounting backup sets to a specific point in time, making it ideal for quickly reverting inadvertent or malicious changes With ApexSQL Restore you can:
See alsoHow to use ApexSQL RestoreHow does ApexSQL Restore work When to use ApexSQL Restore How to attach a full database backup using ApexSQL Restore How to restore to a point in time using ApexSQL Restore Installing ApexSQL Restore on Windows XP Attaching a FILESTREAM backup on Windows XP Labels: ApexSQL Restore ...Installing ApexSQL Restore on Windows XP When installing ApexSQL Restore on Windows XP, the installation process might prompt for a missing installation package, as shown:  The reason for this is that in Windows XP, the installer for Microsoft Visual C++ Redistributable, a component necessary for ApexSQL Restore to function, could have a problem locating the vc_red.msi file
See also: Labels: ApexSQL Restore ...When to use ApexSQL Restore ApexSQL Restore can attach native or natively compressed backups and backup sets created in SQL Server 2000 and above to any edition of SQL Server 2005 and above, including full and differential database backups as well as transaction log backups As such, ApexSQL Restore is very useful, among others, in the following scenarios:
Labels: ApexSQL Restore ...How to use ApexSQL Restore ApexSQL Restore can attach native or natively compressed backups and backup sets created in SQL Server 2000 and above to any edition of SQL Server 2005 and above, including full and differential database backups as well as transaction log backups
Detached backup sets are listed under the History section and can be re-attached in single click. Please note that reattaching a detached backup set by clicking the Re-attach virtual database command will result in the backup set being attached with the same settings
For example, if you’ve attached and afterwards detached a full database backup, a differential database backup and the first subsequent transaction log backup from a backup set which contains three additional transaction log backups, clicking the Re-attach virtual database command will result in those same backups being attached and the same restore path being used; if you want to attach a different backup configuration form the same backup set you’ll need to use the Add virtual database command (i.e. create a new virtual database) Labels: ApexSQL Restore ...How does ApexSQL Restore work
ApexSQL Restore can attach native or natively compressed backups and backup sets created in SQL Server 2000 and above to any edition of SQL Server 2005 and above, including full and differential database backups as well as transaction log backups
ApexSQL Restore uses the ApexSQL Restore Service which starts automatically on Windows startup. This service encapsulates the backup restoring capabilities of the SQL Server itself using proprietary algorithms to reduce the physical size of the restored backup up to 125 times and makes the restore process up to 25 times faster in some cases
The entire process is transparent to the user as the
backup set will be attached as a fully usable online database, which is
accessible via SQL Server Management Studio, Visual Studio or any third-party
tool; in a way this is an equivalent of restoring the backup set with the WITH
RECOVERY option
Upon attaching a backup set, ApexSQL Restore creates files whose names correspond to the names
of the physical .MDF, .LDF and .NDF files of the database the backup set was
taken from. Those files, located in the specified restore path take
only the fraction of the space the original files take
Labels: ApexSQL Restore ...Attaching a FileStream backup on Windows XP
When restoring a backup of a FileStream database using ApexSQL Restore on Windows XP, the following error is shown:
Unable to open the physical file "c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\FileStreamTSCDB321". Operating system error -2147024891: "0x80070005(Access is denied.)" This problem is specific to restoring FileStream database backups on Windows XP, regardless of the method used – via SSMS, ApexSQL Restore, the T-SQL RESTORE statement, or any other third-party tool To solve the problem, use this Microsoft Windows XP hotfix: Service cannot access the \?? namespace in Windows XP After the aforementioned hotfix is applied, FileStream database backups can be restored on a Windows XP machine seamlessly, using any restore method – including ApexSQL Restore See also: Operating system error -2147024891: "0x80070005(Access is denied.)" Service cannot access the \?? namespace in Windows XP Labels: ApexSQL Restore ...How to attach a full database backup using ApexSQL Restore
To attach a full database backup using ApexSQL Restore:
Labels: ApexSQL Restore ...How to restore to a point in time using ApexSQL Restore
To restore to a point in time using ApexSQL Restore:
1. The name of the SQL Server instance the backup will be attached to
2.The preferred authentication method; to use SQL Server authentication a set of valid set of SQL Server credentials needs to be provided
4.Click Next 5.To specify the backup set to be attached click Add file(s)… , navigate to the backup files and click Open 6. Review the backup properties and select the full database backup, followed by differential database backups (if they exist) and a sequence of transaction log backups up to a point in time the backup set is to be restored to 7. To specify a non-default restore path click Advanced >> and specify the patch 8. Click Finish to attach the backup Labels: ApexSQL Restore ...How to use ApexSQL Script to deploy a database as a .Net executable
See how to script both
structure and data for a database, and how to compile it into a .Net
executable
The good news about the executable is that it can be executed even on the workstations without the SQL Server client tools installed 1. Run ApexSQL Script 2. Connect to a SQL Server instance 3. Select a database and click Open    7. Select Structure and data in the Scripting mode drop-down list 8. Select .NET executable in the Output type drop-down list  10. Select the Include dependent database objects option to avoid constraint problems  12. Select the options for the executable file
a) Check Run executable now to execute the created exe file immediately
after it’s been created
b) Specify the database name,
MDF and LDF file directories, collation, recovery model, and size 14. Specify the file name and the patch for the created file The executable file is started as soon as it’s created  16. To specify what to do when an error is encountered, click Options and select the Error handling tab   Last updated April 7, 2013 Labels: ApexSQL Script ...Recovering a corrupted database
Your database crashed and you've lost the LDF file. The MDF file
is still there, but it’s corrupted and cannot be attached to the SQL server How to recover the database from the corrupted MDF file?
Last updated 20 November 2012 Labels: ApexSQL Recover ...How to synchronize data between databases with different settings
Description
The behavior of DATETIME or SMALLDATETIME types of data can be influenced by the client, server, and/or the operating system settings. This KB article describes how to use ApexSQL Data Diff in situations where these settings must be considered Solution To illustrate, three databases located in three different countries will be used. The goal is to synchronize the data between any two of these databases. The main database is hosted on a SQL Server instance with the US English settings; the other two databases with the French and German settings The script below creates three databases: General, French, and German. Each database contains one table named T. The table T contains only one DATETIME column named TheDate CREATE DATABASE GENERAL COLLATE LATIN1_GENERAL_CI_AI GO CREATE DATABASE FRENCH COLLATE FRENCH_CS_AI GO CREATE DATABASE GERMAN COLLATE GERMAN_PHONEBOOK_CS_AI GO USE GENERAL CREATE TABLE T ( THEDATE DATETIME ) GO USE FRENCH CREATE TABLE T ( THEDATE DATETIME ) GO USE GERMAN CREATE TABLE T ( THEDATE DATETIME ) GO Common problems with temporal data when working in international environments
USA, France, and Germany all use a different date format. To illustrate what happens when data is inserted into the databases above, the Set language command is used
To insert data into the General database using the French date format DD/MM/YYYY, the script below is executed: USE GENERAL SET LANGUAGE ENGLISH INSERT INTO T VALUES('23/08/2007') CHANGED LANGUAGE SETTING TO US_ENGLISH. MSG 242, LEVEL 16, STATE 3, LINE 3 THE CONVERSION OF A CHAR DATA TYPE TO A DATETIME DATA TYPE RESULTED IN AN OUT-OF-RANGE DATETIME VALUE. THE STATEMENT HAS BEEN TERMINATED. Due to the difference in language settings, the insertion fails. SQL Server cannot resolve the string '23/08/2007' to a valid date format To insert the same data into the German database, the following script is executed: USE GERMAN SET LANGUAGE GERMAN INSERT INTO T VALUES('23/08/2007') DIE SPRACHENEINSTELLUNG WURDE AUF DEUTSCH GEäNDERT. (1 ROW(S) AFFECTED) The data is inserted successfully. The same thing is done with the French database using the script below: USE FRENCH SET LANGUAGE FRENCH INSERT INTO T VALUES('23/08/2007') LE PARAMèTRE DE LANGUE EST PASSé à FRANçAIS. (1 ROW(S) AFFECTED)
The insertion into the French database is successful as well. The results would be the same if there were an attempt to insert data with the German date format DD.MM.YYYY into the three sample databases; the insertion would fail on the General database whereas it'd be successful on the other two
On the other hand, if there was an attempt to insert data that's in the US date format MM/DD/YYYY into all three databases, only the insertion into the General database would be successful The two other INSERT statements cannot be committed successfully and would raise a conversion error Using the SQL Server's CONVERT statement to apply the correct date format style before performing the insertion, resolves these insertion errors. In the next section, you’ll see how to synchronize temporal data in international environments that are similar to the given example by using ApexSQL Data Diff and without the additional "conversion" step Using ApexSQL Data Diff to synchronize temporal data when working in international environments Example 1: In this example, the French database has one row that's not in the General database. To synchronize the General database with the French database, ApexSQL Data Diff generates the synchronization script below: USE GENERAL SET LANGUAGE ENGLISH SET IDENTITY_INSERT [DBO].[T] ON INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, '20070823 00:00:00.000') SET IDENTITY_INSERT [DBO].[T] OFF CHANGED LANGUAGE SETTING TO US_ENGLISH. (1 ROW(S) AFFECTED) Example 2: In this example, the German database has one row that's not in the French database. To synchronize the French database with the German database, ApexSQL Data Diff generates the following synchronization script: USE FRENCH SET LANGUAGE FRENCH SET IDENTITY_INSERT [DBO].[T] ON INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, '20070823 00:00:00.000') SET IDENTITY_INSERT [DBO].[T] OFF LE PARAMèTRE DE LANGUE EST PASSé à FRANçAIS. (1 ROW(S) AFFECTED) Example 3: In this example, the General database has one row that's not in the German database. To synchronize the German database with the General database, ApexSQL Data Diff generates the following synchronization script: USE GERMAN SET LANGUAGE GERMAN SET IDENTITY_INSERT [DBO].[T] ON INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, '20070823 00:00:00.000') SET IDENTITY_INSERT [DBO].[T] OFF DIE SPRACHENEINSTELLUNG WURDE AUF DEUTSCH GEäNDERT. (1 ROW(S) AFFECTED) All of the scripts shown above complete successfully. By default, ApexSQL Data Diff uses the language-neutral date format settings. This default setting can be modified in the Project dialog window by selecting Options in the left pane, and then locating Apply date format under Database configuration in the main dialog window The "Apply date format" option uses the non-separated date format by default: 
The most commonly used formats are also readily available: 
To add a format that's not in the list or to use one that already is, check "Apply date format", select (or enter) the desired format, and click the Save as my defaults button. The selected format will then be used by ApexSQL Data Diff on all (SMALL)DATETIME data type columns encountered during the scripting process For example, if the US date format is used: 
SET IDENTITY_INSERT [DBO].[T] ON INSERT INTO [DBO].[T] ([ID], [THEDATE]) VALUES (1, '08/23/2007 00:00:00.000') SET IDENTITY_INSERT [DBO].[T] OFF Last updated April 7, 2013 Labels: ApexSQL Data Diff ...Configuring ApexSQL Diff to work with source control data sources
Summary
This article explains how to configure ApexSQL Diff to work with source control data sources (how to set a source control data source and how to export a live database to a source control repository)
In ApexSQL Diff, you are accessing the source control data sources exclusively through the Source control wizard. There are 2 cases when the wizard is used a) When you want to export a live database to some source control repositories: From the Home tab, in the Exports group, select Export data source The Export data source dialog will pop up Then, select a database to export 
b) When you want to set a source control repository as a comparison/synchronization data source Open the Project settings dialog, and set the Data source type to Source control 
The Source control wizard 
1. Source control type selection 2. Source control login 3. Advanced (Configure mappings) 1. Source control type selection Here you need to select between different drivers, as in the image, based on the installed source control software: 
You can access the Source control by:
a) Native source control systems (via the drivers developed by ApexSQL for the easy and straightforward way of accessing source control systems) The advantage of this choice is that you don’t need to install a source control client, on the same machine that is running ApexSQL Diff, because native drivers are shipped along with the application b) MSSCCI-compliant source control systems (via the drivers of the MSSCCI-compliant source control client installed on your machine) The limitation of this choice is that you must have an appropriate source control client installed (3rd party or the one shipped with the source control installation), on the same machine where ApexSQL Diff is installed The advantage of this choice is that you can use any source control system from ApexSQL Diff that is MSSCCI-compliant 2. Source control login In order to connect to a source control project, you need to login first Here's how the login forms may look in case of: Subversion: 
User and Password are the user credentials for accessing the Microsoft Visual SourceSafe server Repository URL represents the path to a Subversion repository Project is a project you made within your Subversion database Working folder is a folder on your hard disk you have chosen for your temporary storage SourceGear Vault: 
User and Password are the user credentials for accessing the Microsoft Visual SourceSafe server Vault Server is a resource locator path to a Vault Server Repository is a repository name located in a Vault Server Project is a project name in the repository of a Vault Server Working folder is a temporary folder on your hard disk Microsoft Visual SourceSafe: 
User and Password are the user credentials for accessing the Microsoft Visual SourceSafe server Database represents the path to a Microsoft Visual SourceSafe repository. It has the form Hostname/Repository, where "Hostname" is the name of a Server machine on which the Microsoft Visual SourceSafe server is installed; this name should be pingable from the network and it may be either a NET bios name or a DNS name. "Repository" is a network share name from the host machine Project is a project name you made within your Microsoft Visual SourceSafe database (here within the production database) Working folder is a folder on your hard disk you have chosen to be your Microsoft Visual SourceSafe stub 3. Advanced (Configure mappings) The purpose of the file and folder mapping options is to define the way the objects from your database will be exported/mapped to a source control project Here is an example of the mappings: 
Last updated
April 7, 2013
Labels: ApexSQL Diff ...ApexSQL Search - Introduction
ApexSQL Search is a free SQL search add-in for SSMS and Visual Studio. It offers text search in SQL database objects and data, allows safe renaming of SQL objects, and graphical visualization of object interdependencies With ApexSQL Search you can:
See alsoApexSQL Search - Search phrases, history and resultsApexSQL Search - Object Search ApexSQL Search - Text search ApexSQL Search - Dependency viewer ApexSQL Search - Smart rename System requirements Last updatedOctober 07, 2012Labels: ApexSQL Search ... |
