Sunday, July 26, 2015

Error while creating a new Database by Restore the backup file.


This is the known old issue. I was trying to make a local copy of my business database. I have First created the database and tried to restore the backup and it gave me the below error. Even when I check the Restore Option Overwrite the existing database (WITH REPLACE).



Error Message:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The backup set holds a backup of a database other than the existing 'Test' database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)
Workaround:
1)      Leave the created DB as is or delete it if you already create one (I have created). Right Click on Databases in Object Explorer and select restore database option. Select the backup file and click ok. Database name and other options will auto-populate. You can change if you want.
2)      Click on files tab and reset the data and log file locations if you want.
3)      Click on options tab and select Overwrite the existing Database (WITH REPLACE) option as shown below.
4)      Click ok and check for the restore completion. You can see the percentage of completion on the top of the window.







Reference: http://blog.sqlauthority.com/2007/09/27/sql-server-fix-error-3154-the-backup-set-holds-a-backup-of-a-database-other-than-the-existing-database/

Thursday, May 21, 2015

INSERTING AN IDENTITY COLUMN IN THE SQL TABLE FROM EXCEL SPREAD SHEET USING SQL SERVER IMPORT AND EXPORT


I had to import data to a SQL table from excel sheet couple of days ago. As the data is huge I cannot manually insert so I have decided to use SQL Server Import/Export Wizard.

Started importing the spread sheet by click on Enable Identity Insert option in the wizard but getting errors. Here is the best way I found.

Create your original SQL table with identity column. Import the spread sheet in to the default table (Sheet1$) in the same database. You can use different server if you have linked servers between both of the servers.

I have imported the data in the default table of same DB and inserted in the main table using T-SQL.


Monday, April 13, 2015

Server side trace


We have frequent long running jobs in our all environments which are stuck at same time every night. We thought to run the profiler and see what is happening at the back end at specific time slot. But as it the midnight we decided to schedule a profiler to trace the commands which are running. That was the starting of this Article.

1)      To set up a SQL job with scheduled trace first we need to take script to create/stop and run the trace. To get that script we need to run the trace. Open the Trace from SSMS tools or from performance tools. Connect to the server, choose file or table option and given the path, check on enable trace stop time (you can change it in the script as per your requirement). Select all required events and start and stop the trace.
 


2)      Go to file->export and click on script trace definition. Save the script. It will be in below format.

   /****************************************************/

/* Created by: SQL Server 2012  Profiler          */

/* Date: 04/11/2015  08:24:15 PM         */

/****************************************************/

      -- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

declare @DateTime datetime

set @DateTime = '2015-04-14 00:00:00.000'

set @maxfilesize = 25

 
-- Please replace the text InsertFileNameHere, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename automatically. If you are writing from

-- remote server to local drive, please use UNC path and make sure server has

-- write access to your network share

 

exec @rc = sp_trace_create @TraceID output, 0, N'C:\Trace.trc', @maxfilesize, @Datetime

if (@rc != 0) goto error

 

-- Client side File and Table cannot be scripted

 
-- Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 10, 9, @on

exec sp_trace_setevent @TraceID, 10, 2, @on

exec sp_trace_setevent @TraceID, 10, 10, @on

exec sp_trace_setevent @TraceID, 10, 6, @on

exec sp_trace_setevent @TraceID, 10, 11, @on

exec sp_trace_setevent @TraceID, 10, 12, @on

exec sp_trace_setevent @TraceID, 10, 13, @on

exec sp_trace_setevent @TraceID, 10, 14, @on

exec sp_trace_setevent @TraceID, 10, 15, @on

exec sp_trace_setevent @TraceID, 10, 16, @on

exec sp_trace_setevent @TraceID, 10, 17, @on

exec sp_trace_setevent @TraceID, 10, 18, @on

exec sp_trace_setevent @TraceID, 12, 1, @on

exec sp_trace_setevent @TraceID, 12, 9, @on

exec sp_trace_setevent @TraceID, 12, 11, @on

exec sp_trace_setevent @TraceID, 12, 6, @on

exec sp_trace_setevent @TraceID, 12, 10, @on

exec sp_trace_setevent @TraceID, 12, 12, @on

exec sp_trace_setevent @TraceID, 12, 13, @on

exec sp_trace_setevent @TraceID, 12, 14, @on

exec sp_trace_setevent @TraceID, 12, 15, @on

exec sp_trace_setevent @TraceID, 12, 16, @on

exec sp_trace_setevent @TraceID, 12, 17, @on

exec sp_trace_setevent @TraceID, 12, 18, @on

exec sp_trace_setevent @TraceID, 13, 1, @on

exec sp_trace_setevent @TraceID, 13, 9, @on

exec sp_trace_setevent @TraceID, 13, 11, @on

exec sp_trace_setevent @TraceID, 13, 6, @on

exec sp_trace_setevent @TraceID, 13, 10, @on

exec sp_trace_setevent @TraceID, 13, 12, @on

exec sp_trace_setevent @TraceID, 13, 14, @on

 
-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

 

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 91dd8a1d-cd63-42af-b0ad-7bb89dff9ee1'

-- Set the trace status to start

exec sp_trace_setstatus @TraceID, 1

 

-- display trace id for future references

select TraceID=@TraceID

goto finish

 

error:

select ErrorCode=@rc

 

finish:

go


3)      Set the datetime, maxfilesize parameters and setup the file location.

4)      Set up the SQL job with this code and schedule as per your requirement. Trace would stop based on the datetime parameter or if the mentioned maxfilesize exceeds.

5)      Use below query to get your trace details and check the status of it.

 SELECT * FROM fn_trace_getinfo(default)

6)      Use the below stmt to load the trace file into the SQL table.


USE DBName

GO

SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\my_trace.trc', default)

Well: Above one is working fine to run the job once but if we want to schedule it for daily with standard stop time we need few additions. Go through the below query.


/****************************************************/

/* Created by: SQL Server 2012  Profiler          */

/* Date: 04/11/2015  08:24:15 PM         */

/****************************************************/

 
 

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

declare @stoptime datetime

set @stoptime=Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()+1))

set @maxfilesize = 25

 

-- Please replace the text InsertFileNameHere, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename automatically. If you are writing from

-- remote server to local drive, please use UNC path and make sure server has

-- write access to your network share

 

declare @tracefilename nvarchar(255)  set @tracefilename = N'C:\Trace\StartupTrace'+CAST(DATEPART(d,GETDATE()) AS varchar(2))+CAST(DATEPART(M,GETDATE()) AS varchar(2))+CAST(DATEPART(YYYY,GETDATE()) AS varchar(4))+CAST(DATEPART(HH,GETDATE()) AS varchar(2))+CAST(DATEPART(MI,GETDATE()) AS varchar(2))

exec @rc = sp_trace_create @TraceID output, 0, @tracefilename,

@maxfilesize, @stoptime

if (@rc != 0) goto error

 

-- Client side File and Table cannot be scripted

 

-- Set the events

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 10, 9, @on

exec sp_trace_setevent @TraceID, 10, 2, @on

exec sp_trace_setevent @TraceID, 10, 10, @on

exec sp_trace_setevent @TraceID, 10, 6, @on

exec sp_trace_setevent @TraceID, 10, 11, @on

exec sp_trace_setevent @TraceID, 10, 12, @on

exec sp_trace_setevent @TraceID, 10, 13, @on

exec sp_trace_setevent @TraceID, 10, 14, @on

exec sp_trace_setevent @TraceID, 10, 15, @on

exec sp_trace_setevent @TraceID, 10, 16, @on

exec sp_trace_setevent @TraceID, 10, 17, @on

exec sp_trace_setevent @TraceID, 10, 18, @on

exec sp_trace_setevent @TraceID, 12, 1, @on

exec sp_trace_setevent @TraceID, 12, 9, @on

exec sp_trace_setevent @TraceID, 12, 11, @on

exec sp_trace_setevent @TraceID, 12, 6, @on

exec sp_trace_setevent @TraceID, 12, 10, @on

exec sp_trace_setevent @TraceID, 12, 12, @on

exec sp_trace_setevent @TraceID, 12, 13, @on

exec sp_trace_setevent @TraceID, 12, 14, @on

exec sp_trace_setevent @TraceID, 12, 15, @on

exec sp_trace_setevent @TraceID, 12, 16, @on

exec sp_trace_setevent @TraceID, 12, 17, @on

exec sp_trace_setevent @TraceID, 12, 18, @on

exec sp_trace_setevent @TraceID, 13, 1, @on

exec sp_trace_setevent @TraceID, 13, 9, @on

exec sp_trace_setevent @TraceID, 13, 11, @on

exec sp_trace_setevent @TraceID, 13, 6, @on

exec sp_trace_setevent @TraceID, 13, 10, @on

exec sp_trace_setevent @TraceID, 13, 12, @on

exec sp_trace_setevent @TraceID, 13, 14, @on

 

 

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

 

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 91dd8a1d-cd63-42af-b0ad-7bb89dff9ee1'

-- Set the trace status to start

exec sp_trace_setstatus @TraceID, 1

 

-- display trace id for future references

select TraceID=@TraceID

goto finish

 

error:

select ErrorCode=@rc

 

finish:

go