Monday, October 24, 2011

SQL Server 2011 (Denali): Changing Backup Files Default Path

Up to SQL Server 2008 R2 (10.5), we were unable to change default path (easily) for “Backup files”, though it was possible after making some changes in registry at path. “HKEY_LOCAL_MACHINE\SOFTWARE\ Microsoft\Microsoft SQL Server\MSSQL.1\ MSSQLServer\BackupDirectory
Note: For SQL Server 2005 its MSSQL.1, for SQL Server 2008, its MSSQL10 and for SQL Server 2008 R2 its MSSQL10_50
Thanks to SQL Server 2011 (Code name DENALI), as now we can change this default path for backup files to our required one by just opening server properties page and on “Database Settings” tab.

Try Taking a backup from SSMS and now you can find that it has already pointing toward you given path.

Read More about SQL Server 2012 (Code Name: Denali

·         Introducing New Edition "Business Intelligence"

·         New Backup/Restore Options

·         CTP 3 Product Guide Released

·         TRY_CONVERT(), a Good Addition

·         Table Partition Limit Enhancement

·         Format(), a Most Wanted Function

·         Get Date/Time from Parts

·         New Function to Get Last Date of Month

·         IIF Logical Function

·         A New More Flexible Create Index Dialog box

Tuesday, October 18, 2011

SQL Server: How to Import Data from Pervasive

To extract data from pervasive data files Pervasive database Engine is required. Latest Trial version 11 can be downloaded from
Once you have installed pervasive Engine, follow following steps to view and import it into SQL Server.
1. Open Control Center through programs>Pervasive> PSQL v11> control Center and documentation.

2.       Make sure services pervasive PSQL Relational Engine and pervasive PSQL Transaction Engine are running (Expand services in Left panel)
3.       To open a database Expand Engines>[ server name] > Databases
4.       Right Click on Databases and select New>Database
5.       In Database Name Box give a name to your database.
6.       In Location Box locate the database files path.  (.mkd, .ddf, .dat)
7.       You can uncheck “relational integrity enforced”.
8.       Click Finish to complete the process.

 9.    Expand Database and you can see the added database.
10.   Double click on a table to see records

Moving data into SQL Server:
1.       Right click on target database and select Tasks> import data to open import and export wizard.
2.       Hit next on the welcome screen.

4.       To choose a data source click on the drop down list and select Pervasive Provider, Release 3.2
5.       Under Standard connection: specify pervasive Database Name, Host and server DSN as shown in figure below.  DSN DEMODATA is automatically created by Pervasive Engine.
6.       Click next.
7.       Chose SQL server native Client as Destination. Hit Next.
8.        Click next
9.     Provide Query to extract data from a given table. Click parse to verify. Click next
10.   Change Destination table name and click on Edit Mappings button.  You can also preview source data.
11.   In column mappings you can specify Field names, data type and size for destination table.
12.   Click "OK" button to close Column mappings and hit finish to execute the import process.
13.   On successful completion refresh database in SQL Server to see imported data.

Monday, October 17, 2011

SQL Server: Using SQL Profiler to Capture Stored Procedure Call from Application

SQL Profiler is a high-quality tool to figure out different database issues, like “Which are costliest queries running”, “Which quires acquired exclusive locks”, “Which indexes are missing” and so on. But in development environment and on production when solving a problem, developers like to use SQL Profiler to get exact procedure call which is being generated by front end application.
Worst practice is, that developers like to use existing built in templates for this purpose and normally use default one i.e. STANDARD.  If you are also using SQL Profiler for this procedure call purpose, then selecting STANDARD trace template is not a good choice, as on production server it affects its performance and even on development server it returns much more extra information.
Good practice is, if you have not created your own template then always select TUNING.

 It also has some extra information so when you only need to catch procedure calls generated from your application then click on “Event Selection” tab and keep only “RPC:Completed” event. You need not to select “Sp: stmt Completed” as you just need to capture “execute procedure calls” and not all the statement inside this procedure. You can also omit “SP: Batch Completed” as we need calls that are only generated from application. If you also need to capture calls from SSMS then you can keep it.

To avoid extra work pressure on server and to get your required results only, you must also apply filters on “Database Name” and “Text

 Use % sign, just like you use in LIKE operator.

Now run your trace and you will find your required results quickly and clearly, even without putting extra work load on database server.