| Recently, we have found that our testing database has been restored on the server and we lost our recent work. We have to identify that at what time the database has been restored, so we can know that what are the changes need to be restored again on the database. We have found that MSDB database keeps information for each restore we have made on the server and luckily we have found the time and user we had restored the database. Let me share the simple script that I have found: Reference: Tejas Shah (http://www.SQLYoga.com) |
Learn SQL and database management at SQLYoga for articles, tutorials, and tips to improve your skills and streamline data operations. Join our community!
October 16, 2012
SQL SERVER: T-SQL to find out when database was restored
18+ years of Hands-on Experience
MICROSOFT CERTIFIED PROFESSIONAL (Microsoft SQL Server)
Proficient in .NET C#
Hands on working experience on MS SQL, DBA, Performance Tuning, Power BI, SSIS, and SSRS
April 6, 2010
SQL SERVER SSIS: Get File Name with Flat File Source, Data Flow Component
| As I explained earlier about For Each Loop Container, which process each file from selected folder. There is a requirement to save File name along with record, so later on we can identify which record comes from which file. Let me explain it how to achieve with FileNameColumnName property of Flat File Connection to get it easily. 1. Setup Flat File Connection with CSV file, as mentioned Basic Example of Data Flow Task. 2. Right click on Flat File Source, and click on Show Advanced Editor: 3. Click on "Component Properties" and go to FileNameColumnName, Custom properties: 4. Setup FileNameColumnName value with desired column name. Let's say "File Name". Congratulations, This column is added to output list with actual filename of that connection. |
18+ years of Hands-on Experience
MICROSOFT CERTIFIED PROFESSIONAL (Microsoft SQL Server)
Proficient in .NET C#
Hands on working experience on MS SQL, DBA, Performance Tuning, Power BI, SSIS, and SSRS
March 7, 2010
SQL SERVER: Execute Stored Procedure when SQL SERVER is started
We have a requirements to execute Stored Procedure when SQL SERVER is started/restarted and we need to start some processes. I found that SQL SERVER provides a way to call Stored Procedure when SQL services are restarted.
SQL SERVER provides this SP: "sp_procoption", which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way.
Let me show you how to use it. Syntax to use SP:
EXEC SP_PROCOPTION
@ProcName = 'SPNAME',
@OptionName = 'startup',
@OptionValue = 'true/false OR on/off'
- @ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in "master" database.
- @OptionName, should be "startup" always.
- @OptionValue, this should be set up to execute this given sp or not. If it is "true/on", given sp will be execute every time when SQL SERVER is started. If it is "false/off", it will not.
That's it, I hope this is very clear to use this feature.
Reference : Tejas Shah (http://www.SQLYoga.com)
18+ years of Hands-on Experience
MICROSOFT CERTIFIED PROFESSIONAL (Microsoft SQL Server)
Proficient in .NET C#
Hands on working experience on MS SQL, DBA, Performance Tuning, Power BI, SSIS, and SSRS
December 3, 2009
SQL SERVER: How to Read Excel file by TSQL
| Many times developers asked that, they want to import data from Excel file. We can do this by many ways with SQL SERVER. 1. We can use SSIS package 2. Import/Export Wizard 3. T-SQL Today, I am going to explain, How to import data from Excel file by TSQL. To import Excel file by TSQL, we need to do following: 1. Put Excel file on server, means we need to put files on server, if we are accessing it from local. 2. Write following TSQL, to read data from excel file SELECT Name, Email, PhoneFROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\SQLYoga.xls', [SQL$]) NOTE: Here, Excel file is on "C:\" named "SQLYoga.xls", and I am reading sheet "SQL" from this excel file If you want to insert excel data into table, INSERT INTO [Info]SELECT Name, Email, PhoneFROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test\SQLYoga.xls', [SQL$]) That's it. |
18+ years of Hands-on Experience
MICROSOFT CERTIFIED PROFESSIONAL (Microsoft SQL Server)
Proficient in .NET C#
Hands on working experience on MS SQL, DBA, Performance Tuning, Power BI, SSIS, and SSRS
December 2, 2009
SQL SERVER: How to remove cursor
| Many times developer ask me that How can they remove Cursor? They need to increase Query Performance, that's why they need to remove SQL SERVER Cursor and find the alternate way to accomplish the same. Please find this code to remove cursor with Table variable: --declare table to keep records to be processedDECLARE @Table AS TABLE(AutoID INT IDENTITY, Column1 VARCHAR(100), Column2 VARCHAR(100))--populate table variable with data that we want to processINSERT INTO @Table(Column1, Column2)SELECT Column1, Column2FROM <Table>WHERE <Conditions>--declare variables to process each recordDECLARE @inc INT, @cnt INT--Assign increment counterSELECT @inc = 1--Get Number of records to be processedSELECT @cnt = COUNT(*)FROM @TableWHILE @inc <= @cnt BEGIN--As we have AutoID declared as IDENTITY, it always get only one record.--Get values in Variable and process it as you want.SELECT @Column1 = Column1,@Column2 = Column2FROM @TableWHERE AutoID = @inc--do your calculation here........--Select next recordSET @inc = @inc = 1END By this way, we can remove CURSOR by Table variable. It is quite easy to implement. One more benefit is: It will process one record at a time, so it locks only that record at a time. Let me know if you have any questions. |
18+ years of Hands-on Experience
MICROSOFT CERTIFIED PROFESSIONAL (Microsoft SQL Server)
Proficient in .NET C#
Hands on working experience on MS SQL, DBA, Performance Tuning, Power BI, SSIS, and SSRS