Jump to content

Ms Sql Server Technology Discussions


9Pardhu

Recommended Posts

[quote name='chelsea' timestamp='1358963438' post='1303159326']
[url="http://archive.msdn.microsoft.com/SQLExamples"]http://archive.msdn....com/SQLExamples[/url]

[b] Common Solutions for T-SQL Problems[/b]
[/quote]
()>> aa link lo first topic ey aripinchindi ga... *=: you rock

Link to comment
Share on other sites

[size=6]Hypothetical Indexes on SQL Server[/size]

Sometimes, you have to test out alternative indexing strategies, but the task of creating the candidate indexes would just take too long. Is there another way? Well, yes, why not use the same method the DTA (Database Tuning Advisor) uses, and take the tedium out of the job.

If we want to predict how well a query will perform as a result of adding a new index on the table, we need to wait while the index is created before we can test it. On larger tables, the creation of the index can take a significant amount of time and if you are trying a number of alternative indexing strategies, the wait can become very tedious. Furthermore, it is a common frustration to find that, after waiting for many minutes for the creation of the index, you realize that it is not using the index when you go to look at the query plan.
So wouldn’t it be nice if we could try a hypothetical index just to test if the index really will be useful for the query. That is possible, but not straightforward; The reason that the technique exists is that it is used by the DTA (Database Tuning Advisor) to recommend a missing index. In this article I’ll present you some undocumented commands that are used to do it.
[b] Creating a hypothetical index[/b]

There is a special syntax of the CREATE INDEX command that allows us to create a hypothetical index. This is an index that creates the metadata of the index on sysindexes and a statistic associated to the index, but does not create the index itself.
Suppose we have the following query from AdventureWorks2012 database:
[CODE]
SELECT SalesOrderID, OrderDate, Status, TerritoryID
FROM Sales.SalesOrderHeader
WHERE OrderDate = '20050701'

[/CODE]

If we want to create a hypothetical index on [b]SalesOrderHeader[/b] table we could run:
[CODE]
CREATE INDEX ixOrderDate ON Sales.SalesOrderHeader (OrderDate) WITH STATISTICS_ONLY = -1
[/CODE]

The relational index option STATISTICS_ONLY = -1, which is undocumented, means that the index itself will not be created, but only the statistic associated with the index. This index be neither considered nor used by the query optimizer unless you run a query in AUTOPILOT mode.
[b] DBCC AUTOPILOT and AUTOPILOT MODE[/b]

There is command called “[b]SET AUTOPILOT ON[/b]” used to enable support to hypothetical indexes, and this is used with other DBCC command called “[b]DBCC AUTOPILOT[/b]”.
First let’s see them working together and then I’ll give you more details about it:
[CODE]
SELECT dbid = DB_ID(),
objectid =
object_id,
indid = index_id
FROM sys.indexes
WHERE
object_id =
OBJECT_ID('Sales.SalesOrderHeader')
AND is_hypothetical = 1
/*
Results:
|dbid |objectid |indid |
|8 |1266103551 |15 |
*/

-- Use typeId 0 to enable a specifc index on AutoPilot mode
DBCC AUTOPILOT(0, 8, 1266103551, 15)
GO
SET AUTOPILOT ON
GO
SELECT SalesOrderID, OrderDate, Status, TerritoryID
FROM Sales.SalesOrderHeader
WHERE OrderDate = '20050701'
GO
SET AUTOPILOT OFF
[/CODE]

[img]http://www.simple-talk.com/iwritefor/articlefiles/1705-1-c490d761-2232-4322-804d-546dc0c7441d.png[/img]

[img]http://www.simple-talk.com/iwritefor/articlefiles/1705-1-2019f5e2-e642-47c2-8e42-3c06710ab9d3.png[/img]

When running on autopilot mode, SQL Server doesn’t execute the query but it returns an estimated execution plan that considers all indexes enabled by DBCC AUTOPILOT command, including the hypothetical ones.
[b] DBCC AUTOPILOT[/b]

There are a few things you could do with this command, first let’s find out what the syntax is. We can find out the syntax of all undocumented commands by using the trace flag 2588 and then running DBCC HELP to see:
[b] [CODE]
DBCC TRACEON (2588)
DBCC HELP('AUTOPILOT')[/b]

DBCC AUTOPILOT (typeid [, dbid [, {maxQueryCost | tabid [, indid [, pages [, flag [, rowcounts]]]]} ]])
[/CODE]
[b] Making AUTOPILOT easier to use[/b]

The parameters that you have to use are not straightforward. This means that, if you are working with a query with lots of tables, it can get boring to write all the DBCC AUTOPILOT commands and this might discourage you from using it. Because of this, I’ve created a procedure to make it a little easier to use.

Originally I created this procedure after answering a student’s question about how to make it easier to use hypothetical indexes on SQL Server. So I thought you may like it.

Unfortunately it relies on a CLR stored procedure to SET the AUTOPILOT, but if you don’t mind to use it in a develop environment (which is something normal to do) then you can use it, following is the CLR code, and if you are interested you can[url="https://skydrive.live.com/redir?resid=52EFF7477E74CAA6%212050"] download the project code here:[/url]
[CODE]
-- CLR Proc
/*
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CLR_GetAutoPilotShowPlan
(
SqlString SQL,
out SqlXml PlanXML
)
{
//Prep connection
SqlConnection cn = new SqlConnection("Context Connection = True");

//Set command texts
SqlCommand cmd_SetAutoPilotOn = new SqlCommand("SET AUTOPILOT ON", cn);
SqlCommand cmd_SetAutoPilotOff = new SqlCommand("SET AUTOPILOT OFF", cn);
SqlCommand cmd_input = new SqlCommand(SQL.ToString(), cn);

if (cn.State != ConnectionState.Open)
{
cn.Open();
}

//Run AutoPilot On
cmd_SetAutoPilotOn.ExecuteNonQuery();

//Run input SQL
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();

da.SelectCommand = cmd_input;
ds.Tables.Add(new DataTable("Results"));

ds.Tables[0].BeginLoadData();
da.Fill(ds, "Results");
ds.Tables[0].EndLoadData();

//Run AutoPilot Off
cmd_SetAutoPilotOff.ExecuteNonQuery();

if (cn.State != ConnectionState.Closed)
{
cn.Close();
}

//Package XML as output
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
//XML is in 1st Col of 1st Row of 1st Table
xmlDoc.InnerXml = ds.Tables[0].Rows[0][0].ToString();
System.Xml.XmlNodeReader xnr = new System.Xml.XmlNodeReader(xmlDoc);
PlanXML = new SqlXml(xnr);
}
};
*/

[/CODE]

And following is the code to compile it on SQL Server and to create another procedure to simulate the hypothetical indexes:

[CODE]
-- Enabling CLR
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO


-- Publishing Assembly
IF EXISTS(SELECT * FROM sys.assemblies WHERE name = 'CLR_ProjectAutoPilot')
BEGIN
IF OBJECT_ID('st_CLR_GetAutoPilotShowPlan') IS NOT NULL
DROP PROC st_CLR_GetAutoPilotShowPlan

DROP ASSEMBLY CLR_ProjectAutoPilot
END
GO
CREATE ASSEMBLY CLR_ProjectAutoPilot FROM 'C:\Fabiano\ ProjectAutoPilot\ProjectAutoPilot\bin\Release\ProjectAutoPilot.dll' WITH PERMISSION_SET = SAFE
GO

CREATE PROCEDURE st_CLR_GetAutoPilotShowPlan (@Query NVarChar(MAX), @ShowPlan XML OUTPUT)
AS
EXTERNAL NAME CLR_ProjectAutoPilot.StoredProcedures.CLR_GetAutoPilotShowPlan
GO

IF OBJECT_ID('st_TestHipotheticalIndexes', 'p') IS NOT NULL
DROP PROC dbo.st_TestHipotheticalIndexes
GO
CREATE PROCEDURE dbo.st_TestHipotheticalIndexes (@SQLIndex NVarChar(MAX), @Query NVarChar(MAX))
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRAN
DECLARE @CreateIndexCommand NVarChar(MAX),
@IndexName NVarChar(MAX),
@TableName NVarChar(MAX),
@SQLIndexTMP NVarChar(MAX),
@SQLDropIndex NVarChar(MAX),
@SQLDbccAutoPilot NVarChar(MAX),
@i Int,
@QuantityIndex Int,
@Xml XML

IF SubString(@SQLIndex, LEN(@SQLIndex), 1) <> ';'
BEGIN
RAISERROR ('Last character in the index should be ;', -- Message text.
16, -- Severity.
1 -- State.
);
END

SET @SQLDropIndex = '';
SET @QuantityIndex = LEN(@SQLIndex) - LEN(REPLACE(@SQLIndex, ';', ''))
SELECT @SQLIndexTMP = SUBSTRING(@SQLIndex, 0, CharIndex(';', @SQLIndex))

SET @i = 0
WHILE @i < @QuantityIndex
BEGIN
SET @SQLIndexTMP = SUBSTRING(@SQLIndex, 0, CharIndex(';', @SQLIndex))
SET @CreateIndexCommand = SUBSTRING(@SQLIndexTMP, 0, CharIndex(' ON ',@SQLIndexTMP))
SET @IndexName = REVERSE(SubString(REVERSE(@CreateIndexCommand), 0, CharIndex(' ', REVERSE(@CreateIndexCommand))))
SET @TableName = SUBSTRING(REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', ''), 0, CharIndex(' ', REPLACE(@SQLIndexTMP, @CreateIndexCommand + ' ON ', '')))
SET @SQLIndex = REPLACE(@SQLIndex, @SQLIndexTMP + ';', '')
--SELECT @SQLIndex, @SQLIndexTMP, @CreateIndexCommand, @TableName, @IndexName

-- Creating hypotetical index
IF CharIndex('WITH STATISTICS_ONLY =', @SQLIndexTMP) = 0
BEGIN
SET @SQLIndexTMP = @SQLIndexTMP + ' WITH STATISTICS_ONLY = -1'
END
-- PRINT @SQLIndexTMP
EXEC (@SQLIndexTMP)

-- Creating query to drop the hypotetical index
SELECT @SQLDropIndex = @SQLDropIndex + 'DROP INDEX ' + @TableName + '.' + @IndexName + '; '
-- PRINT @SQLDropIndex

-- Executing DBCC AUTOPILOT
SET @SQLDbccAutoPilot = 'DBCC AUTOPILOT (0, ' +
CONVERT(VarChar, DB_ID()) + ', '+
CONVERT(VarChar, OBJECT_ID(@TableName),0) + ', ' +
CONVERT(VarChar, INDEXPROPERTY(OBJECT_ID(@TableName), @IndexName, 'IndexID')) + ')'

EXEC (@SQLDbccAutoPilot)
--PRINT @SQLDbccAutoPilot

SET @i = @i + 1
END

-- Executing Query
DECLARE @PlanXML xml

EXEC st_CLR_GetAutoPilotShowPlan @Query = @Query,
@ShowPlan = @PlanXML OUT
SELECT @PlanXML

-- Droping the indexes
EXEC (@SQLDropIndex)

COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
-- Execute error retrieval routine.
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
END
GO
[/CODE]
The stored procedure [b]st_TestHipotheticalIndexes[/b] expects two input parameters:[list]
[*][b]@[/b][b]SQLIndex[/b]: Here you should specify the command to create the index that you want to try (the hypothetical indexes), if you want to try more than one index, just call it separating many “create index” commands by a semicolon. For instance:
[/list]
@SQLIndex = 'CREATE INDEX ix_12 ON Products (Unitprice, CategoryID, SupplierID) INCLUDE(ProductName);CREATE INDEX ix_Quantity ON Order_Details (Quantity);',
[list]
[*][b]@Query[/b]: Here you should write the query you want to try.
[/list]
Here is a sample of how to call it:
[CODE]
EXEC dbo.st_TestHipotheticalIndexes @SQLIndex = 'CREATE INDEX ix ON Order_Details(Quantity);',
@Query = 'SELECT * FROM Order_Details WHERE Quantity < 1'

[/CODE]

The results of the query above is an XML datatype with the query plan considering the suggested index:

[img]http://www.simple-talk.com/iwritefor/articlefiles/1705-b27d8e96-93c7-44b3-aadb-431ec43a9dd5.png[/img]

Another sample:
-- Sample 2
[CODE]
EXEC dbo.st_TestHipotheticalIndexes @SQLIndex = 'CREATE INDEX ix_12 ON Products (Unitprice, CategoryID, SupplierID) INCLUDE(ProductName);CREATE INDEX ix_Quantity ON Order_Details (Quantity);', @Query = 'SELECT p.ProductName, p.UnitPrice, s.CompanyName, s.Country, od.quantity FROM Products as P INNER JOIN Suppliers as S ON P.SupplierID = S.SupplierID INNER JOIN order_details as od ON p.productID = od.productid WHERE P.CategoryID in (1,2,3) AND P.Unitprice < 20 AND S.Country = ''uk'' AND od.Quantity < 90'
[/CODE]

[img]http://www.simple-talk.com/iwritefor/articlefiles/1705-3c56c020-53c1-4157-93f6-1d641c00443a.png[/img]

Now it is easier to try out the effect of various indexes. Let me know what do you think and please don’t mind the clumsy code in the procedure to get the [b]tablename[/b], i[b]ndexname[/b].
[b] Conclusion[/b]

There is a lot of mystery about these undocumented features, but I’m sure this will be enough to get you started with doing tests using Hypothetical indexes. I am sure I don’t need to tell you not to use this is in production environment do I? This is undocumented stuff, so nobody can guarantee what it is really doing, and the side-effects unless Microsoft chooses to make it officially public and documented.

Link to comment
Share on other sites

[b] VARCHAR (MAX), NVARCHAR (MAX) and NTEXT Data Types[/b]


Despite NTEXT and TEXT being deprecated in SQL Server for some time they are still both used in production systems. In this article, the difference between a VARCHAR (MAX), NVARCHAR (MAX) and the NTEXT data types will be demonstrated, and the impact on performance from using NTEXT/TEXT.

Let’s create a table named Student using the below T-SQL.

[CODE]
create table student
(
student_id int identity(1,1),
student_name varchar(max)
)
[/CODE]

Let’s insert a few records into the table using the below T-SQL.
[CODE]
insert student(student_name)
select 'abc'
union
select 'pqr'
union
select 'xyz'
[/CODE]
Now let’s have a look at the query execution plan:

[img]http://c3154802.r2.cf0.rackcdn.com/article_images/varchar_nvarchar_ntext_files/image001.jpg[/img]

It is obvious from this that you do not have a Clustered Index on the table. Let’s create it using the below T-SQL:
[CODE]
create clustered
index clu_student_stuid on student(student_id)
[/CODE]
Now look again at the query execution plan :

[img]http://c3154802.r2.cf0.rackcdn.com/article_images/varchar_nvarchar_ntext_files/image002.jpg[/img]

Now you can see that the Table Scan gets converted to a Clustered Index Scan which means that the base table is completely scanned by the Clustered Index. Now let’s try to improve it’s performance by creating a Non Clustered Index on the column named Student_name using the below T-SQL.

[img]http://c3154802.r2.cf0.rackcdn.com/article_images/varchar_nvarchar_ntext_files/image003.jpg[/img]

As you can see, a Non Clustered index isn’t allowed to be created on a column using VARCHAR(MAX) as a data type. This will hurt the performance when the there is a large volume of data inside the table and hence this is one of the major disadvantages of using the VARCHAR(MAX) data type.

A SQL Data row has a max limited size of 8000 bytes. Therefore a VARCHAR(MAX) which can be up to 2 GB in size cannot be stored in the data row and so SQL Server stores it "Out of row". VARCHAR(MAX) is overkill for many types of data such as person’s names, locations, colleges etc. Therefore instead of using VARCHAR(MAX) such such types of data, one should limit the Varchar size to a reasonable number of characters which will greatly improve performance.

If you have variable length Unicode string data then you can go either for an NVARCHAR or NTEXT but note the difference between these:
Let’s create a table named Student using the below T-SQL.

[CODE]
create table student
(
student_id int,
student_name ntext
)
[/CODE]

Now we can insert a few records :

[CODE]
insert student(student_name)
select 'abc'
union
select 'pqr'
union
select 'xyz'
[/CODE]


Now let’s have a look at the Statistic count using the below T-SQL:
[CODE]
set statistics io on
select * from student
set statistics io off
[/CODE]

[img]http://c3154802.r2.cf0.rackcdn.com/article_images/varchar_nvarchar_ntext_files/image004.jpg[/img]

Note that since the data type of the student_name column is NTEXT the query optimizer is treating the data in that column as a Large Object.
Now let’s change the data type from NTEXT to NVARCHAR(MAX) :

[CODE]
alter table student
alter column student_name nvarchar(max)
[/CODE]

Now, look at the Statistics count again :
[CODE]
set statistics io on
select * from student
set statistics io off
[/CODE]

[img]http://c3154802.r2.cf0.rackcdn.com/article_images/varchar_nvarchar_ntext_files/image005.jpg[/img]

Still not good as despite changing the data type of the column, the LOB Logical Read count is still the same (ie 3). This is because SQL Server has maintained the data in the Large Object structure and now uses a pointer as a reference to extract the text from this column. In order to fix this problem, execute the below T-SQL.

[CODE]
update student
set student_name = student_name
[/CODE]

Now let’s have a look at the Statistics count again:
[img]http://c3154802.r2.cf0.rackcdn.com/article_images/varchar_nvarchar_ntext_files/image006.jpg[/img]

Now the query optimizer does not treat is as a LOB Data. Therefore, from a performance standpoint NVARCHAR data type is always superior to NTEXT.

Link to comment
Share on other sites

How many row(s) are affected by the following code in the estimated execution plan?
[sql]Begin Transaction
SET QUOTED_IDENTIFIER ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
COMMIT[/sql]

Link to comment
Share on other sites

[quote name='Kaarthikeya' timestamp='1358988869' post='1303161975']
How many row(s) are affected by the following code in the estimated execution plan?
[sql]Begin Transaction
SET QUOTED_IDENTIFIER ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
COMMIT[/sql]
[/quote]


2 my knowledge enni rows affect ayyayo count chupettadu.....b/c SET QUOTED IDENTIFER iz ON

Link to comment
Share on other sites

@karthikeya : nee question ento sariga ardam kaaledu maastaaru.. okkasari nee actual tables di sample DDL ga chesi vesav anuko with some test data like 2-3 records then result set easy... antey gaani result set icchi tables ni guess kottadam antey kastam ga undi...

konchem ddl pls, tarvataa rawkudaam....

Link to comment
Share on other sites

[quote name='loveindia' timestamp='1358995547' post='1303162471']
@karthikeya : nee question ento sariga ardam kaaledu maastaaru.. okkasari nee actual tables di sample DDL ga chesi vesav anuko with some test data like 2-3 records then result set easy... antey gaani result set icchi tables ni guess kottadam antey kastam ga undi...

konchem ddl pls, tarvataa rawkudaam....
[/quote]

repu vestha veelaithe office ki vellaaka

Link to comment
Share on other sites

[b] How to Use Instead of Trigger[/b]


[center]A trigger is an exceptional sort of stored procedure which functions when we try to amend the data in a table like inserting, deleting or updating data. It is a database object, executed automatically and is bound to a table. Fundamentally, triggers are classified into two types mainly-[/center][list]
[*]Instead of Trigger
[*]After Trigger
[/list]
We know how we can insert, delete or update operations aligned with excessively intricate views to support natively through ‘Instead of Trigger’. In other words, we can use this trigger as an interceptor for an action attempted on our table.

Instead of Trigger is an important element which is emphasized in almost every [url="http://www.koenig-solutions.com/"]SQL course[/url]. Here, we will discuss the situation where we want to make a table column which can auto generate the customized sequence. We can see an example of the same below-

[img]http://www.pinaldave.com/bimg/triggerexp1.png[/img]

Here, we don’t have to misunderstand the id column above to be an identity column. This column is of character data type . All we want is to autogenerate this column as it is displayed in the figure above.
[b] [b]Usage[/b][/b]

[i][b]‘Instead of Trigger’[/b][/i] can help us to easily solve the situation above. In ‘Instead of Trigger ‘ we insert the data into the virtual tables prior to checking the constraints. As far as ‘After Trigger’ constraints are concerned, they are checked in the first place. Data is then inserted into the virtual tables ( inserted and deleted tables).

We can consider the code mentioned below for better understanding-

[CODE]
CREATE TABLE [dbo].[Employee1](
[id] CHAR(10) PRIMARY KEY,
[name] VARCHAR(50)
)
GO
INSERT INTO [dbo].[Employee1] VALUES('a1','John')
GO
[/CODE]

Now, for an id column, we need to automatically generate a2, a3, a4….. For this, we can write a code in an insert trigger. Therefore, everytime the trigger command occurs, the trigger fires and the next number is generated.
Let us consider the command mentioned under-

[CODE]
INSERT INTO [dbo].[Employee1] (name) VALUES('Aslam')
[/CODE]

Now we will enter the data in the column (name). By doing so, we will be entering NULL values in the column (id). We have a primary key column in the (id) column. In a primary key, there is no permission for NULL. Therefore, the Primary Key constraint is violated.

In case, we make use of ‘After Trigger’, then constraints are checked prior to inserting the data into the implied table. The primary key constraint is violated in this case. Therefore, we can’t put data into virtual table. As a result, we will not find the trigger firing. On the other hand, on making use of ‘Instead of Trigger’, data is inserted into the virtual table prior to the constraint check.

Therefore, our virtual (inserted) table will be as-

[img]http://www.pinaldave.com/bimg/triggerexp2.png[/img]

Instead of Trigger’s code will be now fired. It is written as-


[CODE]
--Instead of Trigger
CREATE TRIGGER AutoIncrement_Trigger ON [dbo].[Employee1]
instead OF INSERT AS
BEGIN
DECLARE @ch CHAR
DECLARE @num INT
SELECT @num=SUBSTRING(MAX(id),2,1) FROM [dbo].[Employee1]
SELECT @ch=SUBSTRING(MAX(id),1,1) FROM [dbo].[Employee1]
IF @num=9
BEGIN
SET @num=0
SET @ch= CHAR( ( 1 + ASCII(@ch) ))
END
INSERT INTO [dbo].[Employee1] (id,name) SELECT (@ch+CONVERT(VARCHAR(9),(@num+1))),inserted.name FROM inserted
END
[/CODE]
[b] [b]Explanation of the Code Above[/b][/b]

The trigger’s code gets the greatest value from the id column. This is done when we use MAX(id)function, parse the integer data and the character. Now with the use of substring function, put it in @ch and @num variables respectively.
When @num turns 9 then @num is reset to 0. The character is then increased to the next character.

[CODE]
For instance, if @ch= 'a' then
ASCII('a')=97
@ch=CHAR(1+97)=CHAR(98)='b'
[/CODE]

Soon after, @num raises by 1 and gets coupled with the @ch variable. Then, it will be placed into the dbo.employee1 table.
Now we can run the commands mentioned under-

[CODE]
INSERT INTO [dbo].[Employee1] (name) VALUES('Aslam')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alan')
INSERT INTO [dbo].[Employee1] (name) VALUES('Mike')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rahul')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vikas')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vijay')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vineet')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rajat')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alice')
SELECT * FROM [dbo].[Employee1]
[/CODE]

With the information provided in the piece of writing above, we know how important is Instead of Trigger in SQL. It provides a clear approach to modify views that we cannot change directly through DML statements (INSERT, UPDATE, and DELETE).

[b][color=#ff0000]NOTE:[/color][/b] It is always a good idea to design your system such a way that you do not need trigger to use. You can include the logic of trigger in your code as well in your procedure and avoid the usage of the trigger. Triggers are very difficult to debug as well adds lots of overhead to the system. There are many performance problems due to poor implementation of the trigger. This post is just created for demonstration of how triggers can be used in special cases.

Link to comment
Share on other sites

[b] T-SQL Restore Script Generator[/b]


[b] Summary[/b]

This is a feature rich and flexible 'SQL Restore Script Generator' procedure for use in:[list]
[*]
Production recovery situations - After a tail log backup or using STOPAT
[*]
Test environment refreshes - Parameters using WITH MOVE to override file folder
[*]
Point in Time recovery from data lose- Using @StopAt & @StandbyMode parameters to step through.
[/list]
The inclusion of STOPAT & STANDBY parameters is significant when recovering lost data. If the deletion/truncation happened a week and many full backups ago, none of the other procedures referenced would help. The Restore Script Genie will query backup history and construct a restore script appropriate and optimized to the STOPAT point in time required. The procedure is effectively a single SQL query involving repeated calls to a Common Table Expression (CTE). There are no dynamic SQL, #temporary tables or @table variables used, and the script only reads a few backup history tables in msdb. A script is returned as the result which can and should be carefully verfied before using it.

[b] Procedure[/b]

The procedure allows changing the backup file/data/log paths using WITH MOVE, it uses checksum where possible and CHECKDB to verify backups, ignores IS_COPY_ONLY and Symatech, other non SQL, VDI Device type backups. It checks Last_LSN is incremental in the recovery sequence and databases can be left in STANDBY recovery mode to allow stepping through, it can cater for up to 10 striped backup files.
There are a few versions of this type of 'Generate a SQL Restore Script' available, examples:[list]
[*][url="http://sqlserveradvisor.blogspot.co.uk/2009/02/generate-restore-scripts-from-backup.html"][color="#59753d"][u]http://sqlserveradvisor.blogspot.co.uk/2009/02/generate-restore-scripts-from-backup.html[/u][/color][/url]
[*][url="http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/04/30/generate-restore-script-automatically-by-backup-history-table/"][color="#59753d"][u]http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/04/30/generate-restore-script-automatically-by-backup-history-table/[/u][/color][/url]
[/list]
The sp_RestoreScriptGenie inclusion of STOPAT & STANDBY mode recovery options differentiate it from other scripts,

[b] Examples[/b]

To generate a script to restore all user databases, to the most current point in time possible, from the default backup locations, to the existing data and log file locations - run the procedure with no parameters. This might be required following a media failure on the drive hosting the database data files (and assumes current backup history in MSDB), you should always take a tail log backup first if possible before starting to plan restores.
To show options beyond this involving STOPAT, STANDBY and WITH MOVE, a small virtual lab was built with backup history as below.
[b] Test Lab Backup History[/b]

The table below is a summary of the backup history for database db_workspace in a virtual test lab.
[img]http://paulbrewer.dynalias.com/Images1/sp_RSG_1.jpg[/img]
[b] Example - Recover to the most recent Point in Time[/b]

Executing the script below

[CODE]
EXECUTE [dbo].[sp_RestoreScriptGenie]
@Database = 'db_workspace'
Generated the T-SQL script below
RESTORE DATABASE db_workspace FROM DISK = 'X:\Backups\Temp\db_workspace.bak' WITH REPLACE, FILE = 4,CHECKSUM,NORECOVERY, STATS=10, MOVE 'db_workspace' TO 'x:\data\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\db_workspace_log.ldf'
, MOVE 'db_workspace_FG2' TO 'x:\data\db_workspace_FG2.ndf'
, MOVE 'db_workspace_FG1' TO 'x:\data\db_workspace_FG1.ndf'
;
RESTORE DATABASE db_workspace FROM DISK = 'X:\Backups\Temp\db_workspace.bak' WITH REPLACE, FILE = 6,CHECKSUM,NORECOVERY, STATS=10, MOVE 'db_workspace' TO 'x:\data\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\db_workspace_log.ldf'
, MOVE 'db_workspace_FG2' TO 'x:\data\db_workspace_FG2.ndf'
, MOVE 'db_workspace_FG1' TO 'x:\data\db_workspace_FG1.ndf'
;RESTORE LOG db_workspace FROM DISK = 'X:\Backups\Temp\db_workspace_log.trn' WITH NORECOVERY, FILE = 8
;RESTORE DATABASE db_workspace WITH RECOVERY
DBCC CHECKDB('db_workspace') WITH NO_INFOMSGS IF @@ERROR > 0 PRINT N'CONSISTENCY PROBLEMS IN DATABASE : db_workspace' ELSE PRINT N'CONSISTENCY GOOD IN DATABASE : db_workspace'
[/CODE]
[b] Example - Point In Time Recovery to Test 4[/b]

Executing the script below

[CODE]
EXECUTE [dbo].[sp_RestoreScriptGenie]
@Database = 'db_workspace'
,@StopAt = '2013-01-11 16:52:40'
Generated the T-SQL script below
RESTORE DATABASE db_workspace FROM DISK = 'X:\Backups\Temp\db_workspace.bak' WITH REPLACE, FILE = 1,CHECKSUM,NORECOVERY, STATS=10, MOVE 'db_workspace' TO 'x:\data\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\db_workspace_log.ldf'
, MOVE 'db_workspace_FG2' TO 'x:\data\db_workspace_FG2.ndf'
, MOVE 'db_workspace_FG1' TO 'x:\data\db_workspace_FG1.ndf'
;RESTORE DATABASE db_workspace FROM DISK = 'X:\Backups\Temp\db_workspace.bak' WITH REPLACE, FILE = 2, NORECOVERY, STATS=10, MOVE 'db_workspace' TO 'x:\data\db_workspace.mdf', MOVE 'db_workspace_log' TO 'x:\data\db_workspace_log.ldf'
, MOVE 'db_workspace_FG2' TO 'x:\data\db_workspace_FG2.ndf'
, MOVE 'db_workspace_FG1' TO 'x:\data\db_workspace_FG1.ndf'
;RESTORE LOG db_workspace FROM DISK = 'X:\Backups\Temp\db_workspace_log.trn' WITH NORECOVERY, FILE = 2
;RESTORE LOG db_workspace FROM DISK = 'X:\Backups\Temp\db_workspace_log.trn' WITH NORECOVERY, FILE = 3 ,STOPAT = '2013-01-11 16:52:40'
;RESTORE DATABASE db_workspace WITH RECOVERY
DBCC CHECKDB('db_workspace') WITH NO_INFOMSGS IF @@ERROR > 0 PRINT N'CONSISTENCY PROBLEMS IN DATABASE : db_workspace' ELSE PRINT N'CONSISTENCY GOOD IN DATABASE : db_workspace'
[/CODE]
[b] Example - Point In Time Recovery to Test 6, WITH MOVE and STANDBY[/b]

Executing the script below

[CODE]
EXECUTE [dbo].[sp_RestoreScriptGenie]
@Database = 'db_workspace'
,@StopAt = '2013-01-11 16:56:10'
,@ToFileFolder = 'D:\SQLData\ReadOptimizedDrive\'
,@ToLogFolder = 'L:\SQLLogs\WriteOptimizedDrive\'
,@StandbyMode = 1
Generated the T-SQL below
RESTORE DATABASE db_workspace FROM DISK = 'X:\Backups\Temp\db_workspace.bak' WITH REPLACE, FILE = 4,CHECKSUM,STANDBY =N'X:\Backups\Temp\db_workspace_ROLLBACK_UNDO.bak ', STATS=10, MOVE 'db_workspace' TO 'D:\SQLData\ReadOptimizedDrive\db_workspace.mdf', MOVE 'db_workspace_log' TO 'L:\SQLLogs\WriteOptimizedDrive\db_workspace_log.ldf'
, MOVE 'db_workspace_FG2' TO 'D:\SQLData\ReadOptimizedDrive\db_workspace_FG2.ndf'
, MOVE 'db_workspace_FG1' TO 'D:\SQLData\ReadOptimizedDrive\db_workspace_FG1.ndf'
;RESTORE LOG db_workspace FROM DISK = 'X:\Backups\Temp\db_workspace_log.trn' WITH STANDBY =N'X:\Backups\Temp\db_workspace_ROLLBACK_UNDO.bak ', FILE = 5
;RESTORE LOG db_workspace FROM DISK = 'X:\Backups\Temp\db_workspace_log.trn' WITH STANDBY =N'X:\Backups\Temp\db_workspace_ROLLBACK_UNDO.bak ', FILE = 6 ,STOPAT = '2013-01-11 16:56:10'
[/CODE]
[b] Usage Summary[/b]

I've found this procedure useful in a number of situations such as:[list]
[*]
Migrating databases using a 'one time' log shipping technique to minimize downtime
[*]
Verifying backups
[*]
Commissioning development environments
[*]
Recoverying lost data by stepping through in STANDBY mode
[*]
Whenever a quick generation of a RESTORE script is needed.
[/list]
[b] Version 2 Plans[/b]

To include:[list]
[*]STOPBEFOREMARK for stopping at specific LSN
[*]Options for LiteSpeed and other 3rd party backups possible.
[*]Options for piecemeal file, filegroup and page restores.
[*]Full text index backups
[/list]
[b] References[/b]

[url="http://www.sqlservercentral.com/blogs/robert_davis/2013/01/04/day-3-of-31-days-of-disaster-recovery-determining-files-to-restore-database/"][u][color="#000080"]http://www.sqlservercentral.com/blogs/robert_davis/2013/01/04/day-3-of-31-days-of-disaster-recovery-determining-files-to-restore-database/[/color][/u][/url] - Guidance on LSN checks included in Version 1.02
[url="http://www.sqlservercentral.com/articles/Backups/93224/"][u][color="#000080"]http://www.sqlservercentral.com/articles/Backups/93224/[/color][/u][/url] - The article "Importance of Validating Backups" that prompted development of the procedure.
[url="http://sqlserverpedia.com/wiki/Restore_With_Standby"][u][color="#000080"]http://sqlserverpedia.com/wiki/Restore_With_Standby[/color][/u][/url] - Info for new STANDBY recovery mode option in V1.03
[url="http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx"][u][color="#000080"]http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx[/color][/u][/url] - Coding standards and header template
[url="http://www.sqlservercentral.com/blogs/robert_davis/2013/01/12/day-11-of-31-days-of-disaster-converting-lsn-formats/"][u][color="#000080"]http://www.sqlservercentral.com/blogs/robert_davis/2013/01/12/day-11-of-31-days-of-disaster-converting-lsn-formats/[/color][/u][/url] - STOPBEFOREMARK for V2.0 options to stop at a specific LSN
[url="http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx"][u][color="#000080"]http://technet.microsoft.com/en-us/sqlserver/ff977043.aspx[/color][/u][/url] - Restore Internals

[b] The Procedure[/b]

[CODE]
USE master
GO

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_RestoreScriptGenie')
EXEC ('CREATE PROC dbo.sp_RestoreScriptGenie AS SELECT ''stub version, to be replaced''')
GO

/*********************************************************************************************
Restore Script Generator v1.05 (2013-01-15)
(C) 2012, Paul Brewer

Feedback: mailto:[email protected]
Updates: http://paul.dynalias.com/sql

License:
Restore Script Genie is free to download and use for personal, educational, and internal
corporate purposes, provided that this header is preserved. Redistribution or sale
of sp_RestoreScriptGenie, in whole or in part, is prohibited without the author's express
written consent.

Usage examples:

sp_RestoreScriptGenie
No parameters = Generates RESTORE commands for all USER databases, from actual backup files to existing file locations to most current time, consistency checks, CHECKSUM where possible

sp_RestoreScriptGenie @Database = 'db_workspace', @StopAt = '2012-12-23 12:01:00.000', @StandbyMode = 1
Generates RESTORE commands for a specific database from the most recent full backup + most recent differential + transaction log backups before to STOPAT.
Databases left in STANDBY
Ignores COPY_ONLY backups, restores to default file locations from default backup file.

sp_RestoreScriptGenie @Database = 'db_workspace', @StopAt = '2012-12-23 12:31:00.000', @ToFileFolder = 'c:\temp\', @ToLogFolder = 'c:\temp\' , @BackupDeviceFolder = 'c:\backup\'
Overrides data file folder, log file folder and backup file folder.
Generates RESTORE commands for a specific database from most recent full backup, most recent differential + transaction log backups before STOPAT.
Ignores COPY_ONLY backups, includes WITH MOVE to simulate a restore to a test environment with different folder mapping.

CHANGE LOG:
December 23, 2012 - V1.01 - Release
January 4,2013 - V1.02 - LSN Checks + Bug fix to STOPAT date format
January 11,2013 - V1.03 - SQL Server 2005 compatibility (backup compression problem) & @StandbyMode for stepping through log restores with a readable database
January 14, 2013 - V1.04 - Cope with up to 10 striped backup files
January 15, 2013 - V1.05 - Format of constructed restore script, enclose database name in [ ]
*********************************************************************************************/

ALTER PROC dbo.sp_RestoreScriptGenie
(
@Database SYSNAME = NULL,
@ToFileFolder VARCHAR(2000) = NULL,
@ToLogFolder VARCHAR(2000) = NULL,
@BackupDeviceFolder VARCHAR(2000) = NULL,
@StopAt DATETIME = NULL,
@StandbyMode BIT = 0,
@IncludeSystemBackups BIT = 0
)
AS
BEGIN

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_WARNINGS ON;
SET NUMERIC_ROUNDABORT OFF;
SET ARITHABORT ON;

IF ISNULL(@StopAt,'') = ''
SET @StopAt = GETDATE();

--------------------------------------------------------------------------------------------------------------
-- Full backup UNION Differential Backup UNION Log Backup
--------------------------------------------------------------------------------------------------------------
WITH CTE
(
database_name
,current_compatibility_level
,Last_LSN
,current_is_read_only
,current_state_desc
,current_recovery_model_desc
,has_backup_checksums
,backup_size
,[type]
,backupmediasetid
,family_sequence_number
,backupfinishdate
,physical_device_name
,position
)
AS
(
--------------------------------------------------------------------------------------------------------------
-- Full backup (most current or immediately before @StopAt if supplied)
--------------------------------------------------------------------------------------------------------------

SELECT
bs.database_name
,d.[compatibility_level] AS current_compatibility_level
,bs.last_lsn
,d.[is_read_only] AS current_is_read_only
,d.[state_desc] AS current_state_desc
,d.[recovery_model_desc] current_recovery_model_desc
,bs.has_backup_checksums
,bs.backup_size AS backup_size
,'D' AS [type]
,bs.media_set_id AS backupmediasetid
,mf.family_sequence_number
,x.backup_finish_date AS backupfinishdate
,mf.physical_device_name
,bs.position
FROM msdb.dbo.backupset bs

INNER JOIN sys.databases d
ON bs.database_name = d.name

INNER JOIN
(
SELECT
database_name
,MAX(backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'D'
AND b.[Device_Type] = 2
AND a.is_copy_only = 0
AND a.backup_finish_date <= ISNULL(@StopAt,a.backup_finish_date)
GROUP BY database_name
) x
ON x.database_name = bs.database_name
AND x.backup_finish_date = bs.backup_finish_date

JOIN msdb.dbo.backupmediafamily mf
ON mf.media_set_id = bs.media_set_id
AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number

WHERE bs.type = 'D'
AND mf.physical_device_name NOT IN ('Nul', 'Nul:')

--------------------------------------------------------------------------------------------------------------
-- Differential backup (most current or immediately before @StopAt if supplied)
--------------------------------------------------------------------------------------------------------------
UNION

SELECT
bs.database_name
,d.[compatibility_level] AS current_compatibility_level
,bs.last_lsn
,d.[is_read_only] AS current_is_read_only
,d.[state_desc] AS current_state_desc
,d.[recovery_model_desc] current_recovery_model_desc
,bs.has_backup_checksums
,bs.backup_size AS backup_size
,'I' AS [type]
,bs.media_set_id AS backupmediasetid
,mf.family_sequence_number
,x.backup_finish_date AS backupfinishdate
,mf.physical_device_name
,bs.position
FROM msdb.dbo.backupset bs

INNER JOIN sys.databases d
ON bs.database_name = d.name

INNER JOIN
(
SELECT
database_name
,MAX(backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'I'
AND b.[Device_Type] = 2
AND a.is_copy_only = 0
AND a.backup_finish_date <= ISNULL(@StopAt,GETDATE())
GROUP BY database_name
) x
ON x.database_name = bs.database_name
AND x.backup_finish_date = bs.backup_finish_date

JOIN msdb.dbo.backupmediafamily mf
ON mf.media_set_id = bs.media_set_id
AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number

WHERE bs.type = 'I'
AND mf.physical_device_name NOT IN ('Nul', 'Nul:')
AND bs.backup_finish_date <= ISNULL(@StopAt,GETDATE())

--------------------------------------------------------------------------------------------------------------
-- Log file backups after 1st full backup before @STOPAT, before next full backup after 1st full backup
--------------------------------------------------------------------------------------------------------------
UNION

SELECT
bs.database_name
,d.[compatibility_level] AS current_compatibility_level
,bs.last_lsn
,d.[is_read_only] AS current_is_read_only
,d.[state_desc] AS current_state_desc
,d.[recovery_model_desc] current_recovery_model_desc
,bs.has_backup_checksums
,bs.backup_size AS backup_size
,'L' AS [type]
,bs.media_set_id AS backupmediasetid
,mf.family_sequence_number
,bs.backup_finish_date as backupfinishdate
,mf.physical_device_name
,bs.position

FROM msdb.dbo.backupset bs

INNER JOIN sys.databases d
ON bs.database_name = d.name

JOIN msdb.dbo.backupmediafamily mf
ON mf.media_set_id = bs.media_set_id
AND mf.family_sequence_number Between bs.first_family_number And bs.last_family_number

LEFT OUTER JOIN
(
SELECT
database_name
,MAX(backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'D'
AND b.[Device_Type] = 2
AND a.is_copy_only = 0
AND a.backup_finish_date <= ISNULL(@StopAt,a.backup_finish_date)
GROUP BY database_name
) y
ON bs.database_name = y.Database_name

LEFT OUTER JOIN
(
SELECT
database_name
,MIN(backup_finish_date) backup_finish_date
FROM msdb.dbo.backupset a
JOIN msdb.dbo.backupmediafamily b
ON a.media_set_id = b.media_set_id
WHERE a.[type] = 'D'
AND b.[Device_Type] = 2

AND a.is_copy_only = 0
AND a.backup_finish_date > ISNULL(@StopAt,'1 Jan, 1900')
GROUP BY database_name
) z
ON bs.database_name = z.database_name

WHERE bs.backup_finish_date > y.backup_finish_date
AND bs.backup_finish_date < ISNULL(z.backup_finish_date,GETDATE())
AND mf.physical_device_name NOT IN ('Nul', 'Nul:')
AND bs.type = 'L'
AND mf.device_type = 2
)

---------------------------------------------------------------
-- Result set below is based on CTE above
---------------------------------------------------------------

SELECT
a.Command AS TSQL_RestoreCommand_CopyPaste
FROM
(

--------------------------------------------------------------------
-- Most recent full backup
--------------------------------------------------------------------

SELECT
';RESTORE DATABASE [' + d.[name] + ']' + SPACE(1) +
'FROM DISK = ' + '''' +
CASE ISNULL(@BackupDeviceFolder,'Actual')
WHEN 'Actual' THEN CTE.physical_device_name
ELSE @BackupDeviceFolder + SUBSTRING(CTE.physical_device_name,LEN(CTE.physical_device_name) - CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 1)
END + '''' + SPACE(1) +

-- Striped backup files
CASE ISNULL(Stripe2.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe2.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe2.physical_device_name,LEN(Stripe2.physical_device_name) - CHARINDEX('\',REVERSE(Stripe2.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe2.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe3.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe3.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe3.physical_device_name,LEN(Stripe3.physical_device_name) - CHARINDEX('\',REVERSE(Stripe3.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe3.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe4.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe4.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe4.physical_device_name,LEN(Stripe4.physical_device_name) - CHARINDEX('\',REVERSE(Stripe4.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe4.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe5.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe5.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe5.physical_device_name,LEN(Stripe5.physical_device_name) - CHARINDEX('\',REVERSE(Stripe5.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe5.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe6.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe6.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe6.physical_device_name,LEN(Stripe6.physical_device_name) - CHARINDEX('\',REVERSE(Stripe6.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe6.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe7.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe7.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe7.physical_device_name,LEN(Stripe7.physical_device_name) - CHARINDEX('\',REVERSE(Stripe7.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe7.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe8.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe8.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe8.physical_device_name,LEN(Stripe8.physical_device_name) - CHARINDEX('\',REVERSE(Stripe8.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe8.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe9.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe9.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe9.physical_device_name,LEN(Stripe9.physical_device_name) - CHARINDEX('\',REVERSE(Stripe9.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe9.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe10.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe10.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe10.physical_device_name,LEN(Stripe10.physical_device_name) - CHARINDEX('\',REVERSE(Stripe10.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe10.physical_device_name),1) + 1) END + ''''
END +

'WITH REPLACE, FILE = ' + CAST(CTE.Position AS VARCHAR(5)) + ',' +
CASE CTE.has_backup_checksums WHEN 1 THEN 'CHECKSUM,' ELSE ' ' END +

CASE @StandbyMode WHEN 0 THEN 'NORECOVERY,' ELSE 'STANDBY =N' + '''' + ISNULL(@BackupDeviceFolder,SUBSTRING(CTE.physical_device_name,1,LEN(CTE.physical_device_name) - CHARINDEX('\',REVERSE(CTE.physical_device_name)))) + '\' + d.name + '_ROLLBACK_UNDO.bak ' + '''' + ',' END + SPACE(1) +

'STATS=10,' + SPACE(1) +
'MOVE ' + '''' + x.LogicalName + '''' + ' TO ' +
'''' +
CASE ISNULL(@ToFileFolder,'Actual')
WHEN 'Actual' THEN x.PhysicalName
ELSE @ToFileFolder + SUBSTRING(x.PhysicalName,LEN(x.PhysicalName) - CHARINDEX('\',REVERSE(x.PhysicalName),1) + 2,CHARINDEX('\',REVERSE(x.PhysicalName),1) + 1)
END + '''' + ',' + SPACE(1) +

'MOVE ' + '''' + y.LogicalName + '''' + ' TO ' +
'''' +
CASE ISNULL(@ToLogFolder,'Actual')
WHEN 'Actual' THEN y.PhysicalName
ELSE @ToLogFolder + SUBSTRING(y.PhysicalName,LEN(y.PhysicalName) - CHARINDEX('\',REVERSE(y.PhysicalName),1) + 2,CHARINDEX('\',REVERSE(y.PhysicalName),1) + 1)
END + '''' AS Command,
1 AS Sequence,
d.name AS database_name,
CTE.physical_device_name AS BackupDevice,
CTE.backupfinishdate,
CTE.backup_size

FROM sys.databases d
JOIN
(
SELECT
DB_NAME(mf.database_id) AS name
,mf.Physical_Name AS PhysicalName
,mf.Name AS LogicalName
FROM sys.master_files mf
WHERE type_desc = 'ROWS'
AND mf.file_id = 1
) x
ON d.name = x.name

JOIN
(
SELECT
DB_NAME(mf.database_id) AS name, type_desc
,mf.Physical_Name PhysicalName
,mf.Name AS LogicalName
FROM sys.master_files mf
WHERE type_desc = 'LOG'
) y
ON d.name = y.name

JOIN CTE
ON CTE.database_name = d.name

-- Striped backup files (caters for up to 10)
LEFT OUTER JOIN CTE AS Stripe2
ON Stripe2.database_name = d.name
AND Stripe2.backupmediasetid = CTE.backupmediasetid
AND Stripe2.family_sequence_number = 2

LEFT OUTER JOIN CTE AS Stripe3
ON Stripe3.database_name = d.name
AND Stripe3.backupmediasetid = CTE.backupmediasetid
AND Stripe3.family_sequence_number = 3

LEFT OUTER JOIN CTE AS Stripe4
ON Stripe4.database_name = d.name
AND Stripe4.backupmediasetid = CTE.backupmediasetid
AND Stripe4.family_sequence_number = 4

LEFT OUTER JOIN CTE AS Stripe5
ON Stripe5.database_name = d.name
AND Stripe5.backupmediasetid = CTE.backupmediasetid
AND Stripe5.family_sequence_number = 5

LEFT OUTER JOIN CTE AS Stripe6
ON Stripe6.database_name = d.name
AND Stripe6.backupmediasetid = CTE.backupmediasetid
AND Stripe6.family_sequence_number = 6

LEFT OUTER JOIN CTE AS Stripe7
ON Stripe7.database_name = d.name
AND Stripe7.backupmediasetid = CTE.backupmediasetid
AND Stripe7.family_sequence_number = 7

LEFT OUTER JOIN CTE AS Stripe8
ON Stripe8.database_name = d.name
AND Stripe8.backupmediasetid = CTE.backupmediasetid
AND Stripe8.family_sequence_number = 8

LEFT OUTER JOIN CTE AS Stripe9
ON Stripe9.database_name = d.name
AND Stripe9.backupmediasetid = CTE.backupmediasetid
AND Stripe9.family_sequence_number = 9

LEFT OUTER JOIN CTE AS Stripe10
ON Stripe10.database_name = d.name
AND Stripe10.backupmediasetid = CTE.backupmediasetid
AND Stripe10.family_sequence_number = 10

WHERE CTE.[type] = 'D'
AND CTE.family_sequence_number = 1

--------------------------------------------------------------------
-- Most recent differential backup
--------------------------------------------------------------------
UNION

SELECT
';RESTORE DATABASE [' + d.[name] + ']' + SPACE(1) +
'FROM DISK = ' + '''' +
CASE ISNULL(@BackupDeviceFolder,'Actual')
WHEN 'Actual' THEN CTE.physical_device_name
ELSE @BackupDeviceFolder + SUBSTRING(CTE.physical_device_name,LEN(CTE.physical_device_name) - CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 1)
END + '''' + SPACE(1) +

-- Striped backup files
CASE ISNULL(Stripe2.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe2.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe2.physical_device_name,LEN(Stripe2.physical_device_name) - CHARINDEX('\',REVERSE(Stripe2.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe2.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe3.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe3.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe3.physical_device_name,LEN(Stripe3.physical_device_name) - CHARINDEX('\',REVERSE(Stripe3.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe3.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe4.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe4.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe4.physical_device_name,LEN(Stripe4.physical_device_name) - CHARINDEX('\',REVERSE(Stripe4.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe4.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe5.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe5.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe5.physical_device_name,LEN(Stripe5.physical_device_name) - CHARINDEX('\',REVERSE(Stripe5.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe5.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe6.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe6.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe6.physical_device_name,LEN(Stripe6.physical_device_name) - CHARINDEX('\',REVERSE(Stripe6.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe6.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe7.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe7.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe7.physical_device_name,LEN(Stripe7.physical_device_name) - CHARINDEX('\',REVERSE(Stripe7.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe7.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe8.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe8.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe8.physical_device_name,LEN(Stripe8.physical_device_name) - CHARINDEX('\',REVERSE(Stripe8.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe8.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe9.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe9.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe9.physical_device_name,LEN(Stripe9.physical_device_name) - CHARINDEX('\',REVERSE(Stripe9.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe9.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe10.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe10.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe10.physical_device_name,LEN(Stripe10.physical_device_name) - CHARINDEX('\',REVERSE(Stripe10.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe10.physical_device_name),1) + 1) END + ''''
END +

'WITH REPLACE, FILE = ' + CAST(CTE.Position AS VARCHAR(5)) + ',' +
CASE CTE.has_backup_checksums WHEN 1 THEN 'CHECKSUM,' ELSE ' ' END +

CASE @StandbyMode WHEN 0 THEN 'NORECOVERY,' ELSE 'STANDBY =N' + '''' + ISNULL(@BackupDeviceFolder,SUBSTRING(CTE.physical_device_name,1,LEN(CTE.physical_device_name) - CHARINDEX('\',REVERSE(CTE.physical_device_name)))) + '\' + d.name + '_ROLLBACK_UNDO.bak ' + '''' + ',' END + SPACE(1) +

'STATS=10,' + SPACE(1) +
'MOVE ' + '''' + x.LogicalName + '''' + ' TO ' +
'''' +
CASE ISNULL(@ToFileFolder,'Actual')
WHEN 'Actual' THEN x.PhysicalName
ELSE @ToFileFolder + SUBSTRING(x.PhysicalName,LEN(x.PhysicalName) - CHARINDEX('\',REVERSE(x.PhysicalName),1) + 2,CHARINDEX('\',REVERSE(x.PhysicalName),1) + 1)
END + '''' + ',' + SPACE(1) +

'MOVE ' + '''' + y.LogicalName + '''' + ' TO ' +
'''' +
CASE ISNULL(@ToLogFolder,'Actual')
WHEN 'Actual' THEN y.PhysicalName
ELSE @ToLogFolder + SUBSTRING(y.PhysicalName,LEN(y.PhysicalName) - CHARINDEX('\',REVERSE(y.PhysicalName),1) + 2,CHARINDEX('\',REVERSE(y.PhysicalName),1) + 1)
END + '''' AS Command,
32769/2 AS Sequence,
d.name AS database_name,
CTE.physical_device_name AS BackupDevice,
CTE.backupfinishdate,
CTE.backup_size

FROM sys.databases d

JOIN CTE
ON CTE.database_name = d.name

-- Striped backup files (caters for up to 10)
LEFT OUTER JOIN CTE AS Stripe2
ON Stripe2.database_name = d.name
AND Stripe2.backupmediasetid = CTE.backupmediasetid
AND Stripe2.family_sequence_number = 2

LEFT OUTER JOIN CTE AS Stripe3
ON Stripe3.database_name = d.name
AND Stripe3.backupmediasetid = CTE.backupmediasetid
AND Stripe3.family_sequence_number = 3

LEFT OUTER JOIN CTE AS Stripe4
ON Stripe4.database_name = d.name
AND Stripe4.backupmediasetid = CTE.backupmediasetid
AND Stripe4.family_sequence_number = 4

LEFT OUTER JOIN CTE AS Stripe5
ON Stripe5.database_name = d.name
AND Stripe5.backupmediasetid = CTE.backupmediasetid
AND Stripe5.family_sequence_number = 5

LEFT OUTER JOIN CTE AS Stripe6
ON Stripe6.database_name = d.name
AND Stripe6.backupmediasetid = CTE.backupmediasetid
AND Stripe6.family_sequence_number = 6

LEFT OUTER JOIN CTE AS Stripe7
ON Stripe7.database_name = d.name
AND Stripe7.backupmediasetid = CTE.backupmediasetid
AND Stripe7.family_sequence_number = 7

LEFT OUTER JOIN CTE AS Stripe8
ON Stripe8.database_name = d.name
AND Stripe8.backupmediasetid = CTE.backupmediasetid
AND Stripe8.family_sequence_number = 8

LEFT OUTER JOIN CTE AS Stripe9
ON Stripe9.database_name = d.name
AND Stripe9.backupmediasetid = CTE.backupmediasetid
AND Stripe9.family_sequence_number = 9

LEFT OUTER JOIN CTE AS Stripe10
ON Stripe10.database_name = d.name
AND Stripe10.backupmediasetid = CTE.backupmediasetid
AND Stripe10.family_sequence_number = 10

JOIN
(
SELECT
DB_NAME(mf.database_id) AS name
,mf.Physical_Name AS PhysicalName
,mf.Name AS LogicalName
FROM sys.master_files mf
WHERE type_desc = 'ROWS'
AND mf.file_id = 1
) x
ON d.name = x.name

JOIN
(
SELECT
DB_NAME(mf.database_id) AS name, type_desc
,mf.Physical_Name PhysicalName
,mf.Name AS LogicalName
FROM sys.master_files mf
WHERE type_desc = 'LOG'
) y
ON d.name = y.name

JOIN
(
SELECT
database_name,
Last_LSN,
backupfinishdate
FROM CTE
WHERE [Type] = 'D'
) z
ON CTE.database_name = z.database_name

WHERE CTE.[type] = 'I'
AND CTE.backupfinishdate > z.backupfinishdate -- Differential backup was after selected full backup
AND CTE.Last_LSN > z.Last_LSN -- Differential Last LSN > Full Last LSN
AND CTE.backupfinishdate < @StopAt
AND CTE.family_sequence_number = 1

-----------------------------------------------------------------------------------------------------------------------------
UNION -- Restore Log backups taken since most recent full, these are filtered in the CTE to those after the full backup date
-----------------------------------------------------------------------------------------------------------------------------

SELECT
';RESTORE LOG [' + d.[name] + ']' + SPACE(1) +
'FROM DISK = ' + '''' + --CTE.physical_device_name + '''' + SPACE(1) +
CASE ISNULL(@BackupDeviceFolder,'Actual')
WHEN 'Actual' THEN CTE.physical_device_name
ELSE @BackupDeviceFolder + SUBSTRING(CTE.physical_device_name,LEN(CTE.physical_device_name) - CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(CTE.physical_device_name),1) + 1)
END + '''' +

-- Striped backup files
CASE ISNULL(Stripe2.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe2.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe2.physical_device_name,LEN(Stripe2.physical_device_name) - CHARINDEX('\',REVERSE(Stripe2.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe2.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe3.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe3.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe3.physical_device_name,LEN(Stripe3.physical_device_name) - CHARINDEX('\',REVERSE(Stripe3.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe3.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe4.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe4.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe4.physical_device_name,LEN(Stripe4.physical_device_name) - CHARINDEX('\',REVERSE(Stripe4.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe4.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe5.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe5.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe5.physical_device_name,LEN(Stripe5.physical_device_name) - CHARINDEX('\',REVERSE(Stripe5.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe5.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe6.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe6.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe6.physical_device_name,LEN(Stripe6.physical_device_name) - CHARINDEX('\',REVERSE(Stripe6.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe6.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe7.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe7.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe7.physical_device_name,LEN(Stripe7.physical_device_name) - CHARINDEX('\',REVERSE(Stripe7.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe7.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe8.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe8.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe8.physical_device_name,LEN(Stripe8.physical_device_name) - CHARINDEX('\',REVERSE(Stripe8.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe8.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe9.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe9.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe9.physical_device_name,LEN(Stripe9.physical_device_name) - CHARINDEX('\',REVERSE(Stripe9.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe9.physical_device_name),1) + 1) END + ''''
END +

CASE ISNULL(Stripe10.physical_device_name,'')
WHEN '' THEN ''
ELSE ', DISK = ' + '''' + CASE ISNULL(@BackupDeviceFolder,'Actual') WHEN 'Actual' THEN Stripe10.physical_device_name ELSE @BackupDeviceFolder + SUBSTRING(Stripe10.physical_device_name,LEN(Stripe10.physical_device_name) - CHARINDEX('\',REVERSE(Stripe10.physical_device_name),1) + 2,CHARINDEX('\',REVERSE(Stripe10.physical_device_name),1) + 1) END + ''''
END +

CASE @StandbyMode WHEN 0 THEN ' WITH NORECOVERY,' ELSE ' WITH STANDBY =N' + '''' + ISNULL(@BackupDeviceFolder,SUBSTRING(CTE.physical_device_name,1,LEN(CTE.physical_device_name) - CHARINDEX('\',REVERSE(CTE.physical_device_name)))) + '\' + d.name + '_ROLLBACK_UNDO.bak ' + '''' + ',' END + SPACE(1) +

CASE CTE.has_backup_checksums WHEN 1 THEN ' CHECKSUM,' ELSE ' ' END +

+ 'FILE = ' + CAST(CTE.Position AS VARCHAR(5)) +
CASE CTE.backupfinishdate
WHEN z.backupfinishdate THEN ' ,STOPAT = ' + '''' + CONVERT(VARCHAR(21),@StopAt,120) + ''''
ELSE ' '
END
AS Command,
32769 AS Sequence,
d.name AS database_name,
CTE.physical_device_name AS BackupDevice,
CTE.backupfinishdate,
CTE.backup_size

FROM sys.databases d

JOIN CTE
ON CTE.database_name = d.name

-- Striped backup files (caters for up to 10)
LEFT OUTER JOIN CTE AS Stripe2
ON Stripe2.database_name = d.name
AND Stripe2.backupmediasetid = CTE.backupmediasetid
AND Stripe2.family_sequence_number = 2

LEFT OUTER JOIN CTE AS Stripe3
ON Stripe3.database_name = d.name
AND Stripe3.backupmediasetid = CTE.backupmediasetid
AND Stripe3.family_sequence_number = 3

LEFT OUTER JOIN CTE AS Stripe4
ON Stripe4.database_name = d.name
AND Stripe4.backupmediasetid = CTE.backupmediasetid
AND Stripe4.family_sequence_number = 4

LEFT OUTER JOIN CTE AS Stripe5
ON Stripe5.database_name = d.name
AND Stripe5.backupmediasetid = CTE.backupmediasetid
AND Stripe5.family_sequence_number = 5

LEFT OUTER JOIN CTE AS Stripe6
ON Stripe6.database_name = d.name
AND Stripe6.backupmediasetid = CTE.backupmediasetid
AND Stripe6.family_sequence_number = 6

LEFT OUTER JOIN CTE AS Stripe7
ON Stripe7.database_name = d.name
AND Stripe7.backupmediasetid = CTE.backupmediasetid
AND Stripe7.family_sequence_number = 7

LEFT OUTER JOIN CTE AS Stripe8
ON Stripe8.database_name = d.name
AND Stripe8.backupmediasetid = CTE.backupmediasetid
AND Stripe8.family_sequence_number = 8

LEFT OUTER JOIN CTE AS Stripe9
ON Stripe9.database_name = d.name
AND Stripe9.backupmediasetid = CTE.backupmediasetid
AND Stripe9.family_sequence_number = 9

LEFT OUTER JOIN CTE AS Stripe10
ON Stripe10.database_name = d.name
AND Stripe10.backupmediasetid = CTE.backupmediasetid
AND Stripe10.family_sequence_number = 10

LEFT OUTER JOIN -- Next full backup after STOPAT
(
SELECT
database_name, MIN(BackupFinishDate) AS backup_finish_date
FROM CTE
WHERE type = 'D'
AND backupfinishdate > @StopAt
GROUP BY database_name

) x
ON x.database_name = CTE.database_name

LEFT OUTER JOIN -- Highest differential backup date
(
SELECT database_name, max(backupfinishdate) AS backupfinishdate
FROM CTE
WHERE CTE.type = 'I'
AND CTE.backupfinishdate < @StandbyMode
GROUP BY database_name
) y
ON y.database_name = CTE.database_name

LEFT OUTER JOIN -- First log file after STOPAT
(
SELECT database_name, min(backupfinishdate) AS backupfinishdate
FROM CTE
WHERE CTE.type = 'L'
AND backupfinishdate > @StopAt
GROUP BY database_name
) z
ON z.database_name = CTE.database_name

JOIN
(
SELECT
database_name,
MAX(Last_LSN) AS Last_LSN
FROM CTE
WHERE CTE.backupfinishdate < ISNULL(@StopAt,GETDATE())
AND CTE.Type IN ('D','I')
GROUP BY database_name
) x1
ON CTE.database_name = x1.database_name

WHERE CTE.[type] = 'L'
AND CTE.backupfinishdate <= ISNULL(x.backup_finish_date,'31 Dec, 2199') -- Less than next full backup
AND CTE.backupfinishdate >= ISNULL(y.backupfinishdate, CTE.backupfinishdate) --Great than or equal to last differential backup
AND CTE.backupfinishdate <= ISNULL(z.backupfinishdate, CTE.backupfinishdate) -- Less than or equal to last file file in recovery chain (IE Log Backup datetime might be after STOPAT)
AND CTE.Last_LSN > x1.Last_LSN -- Differential or Full Last LSN < Log Last LSN
AND CTE.family_sequence_number = 1

--------------------------------------------------------------------
UNION -- Restore WITH RECOVERY
--------------------------------------------------------------------
SELECT
';RESTORE DATABASE [' + d.[name] + ']' + SPACE(1) + 'WITH RECOVERY' AS Command,
32771 AS Sequence,
d.name AS database_name,
'' AS BackupDevice,
CTE.backupfinishdate,
CTE.backup_size

FROM sys.databases d

JOIN CTE
ON CTE.database_name = d.name

WHERE CTE.[type] = 'D'
AND @StandbyMode = 0

--------------------------------------------------------------------
UNION -- CHECKDB
--------------------------------------------------------------------
SELECT
';DBCC CHECKDB(' + '''' + d.[name] + '''' + ') WITH NO_INFOMSGS IF @@ERROR > 0 PRINT N''CONSISTENCY PROBLEMS IN DATABASE : ' + d.name + ''' ELSE PRINT N''CONSISTENCY GOOD IN DATABASE : ' + d.name + '''' AS Command,
32772 AS Sequence,
d.name AS database_name,
'' AS BackupDevice,
CTE.backupfinishdate,
CTE.backup_size

FROM sys.databases d

JOIN CTE
ON CTE.database_name = d.name

WHERE CTE.[type] = 'D'
AND @StandbyMode = 0

---------------------------------------------------------------------------------------------------------------------------------------------------
UNION -- MOVE full backup secondary data files, allows for up to 32769/2 file groups
---------------------------------------------------------------------------------------------------------------------------------------------------

SELECT
', MOVE ' + '''' + b.name + '''' + ' TO ' +
'''' +
CASE ISNULL(@ToFileFolder,'Actual')
WHEN 'Actual' THEN b.physical_name
ELSE @ToFileFolder + SUBSTRING(b.Physical_Name,LEN(b.Physical_Name) - CHARINDEX('\',REVERSE(b.Physical_Name),1) + 2,CHARINDEX('\',REVERSE(b.Physical_Name),1) + 1)
END + '''',
b.file_id AS Sequence,
DB_NAME(b.database_id) AS database_name,
'' AS BackupDevice,
CTE.backupfinishdate,
CTE.backup_size

FROM sys.master_files b
INNER JOIN CTE
ON CTE.database_name = DB_NAME(b.database_id)

WHERE CTE.[type] = 'D'
AND b.type_desc = 'ROWS'
AND b.file_id > 2

---------------------------------------------------------------------------------------------------------------------------------------------------
UNION -- MOVE differential backup secondary data files, allows for up to 32769/2 file groups
---------------------------------------------------------------------------------------------------------------------------------------------------

SELECT
', MOVE ' + '''' + b.name + '''' + ' TO ' +
'''' +
CASE ISNULL(@ToFileFolder,'Actual')
WHEN 'Actual' THEN b.physical_name
ELSE @ToFileFolder + SUBSTRING(b.Physical_Name,LEN(b.Physical_Name) - CHARINDEX('\',REVERSE(b.Physical_Name),1) + 2,CHARINDEX('\',REVERSE(b.Physical_Name),1) + 1)
END + '''',
((b.file_id) + (32769/2)) AS Sequence,
DB_NAME(b.database_id) AS database_name,
'' AS BackupDevice,
CTE.backupfinishdate,
CTE.backup_size

FROM sys.master_files b
INNER JOIN CTE
ON CTE.database_name = DB_NAME(b.database_id)

WHERE CTE.[type] = 'I'
AND b.type_desc = 'ROWS'
AND b.file_id > 2
AND CTE.backupfinishdate < @StopAt
) a

WHERE a.database_name = ISNULL(@database,a.database_name)
AND (@IncludeSystemBackups = 1 OR a.database_name NOT IN('master','model','msdb'))

ORDER BY
database_name,
sequence,
backupfinishdate

END
[/CODE]

Link to comment
Share on other sites

[b] SQL Server Backup and Restore[/b]

Download eBook (PDF): [url="http://www.simple-talk.com/redgatebooks/ShawnMcGehee/sql-server-backup-restore.pdf"]Download here[/url]

Link to comment
Share on other sites

[b] Securing SQL Server 2012 Integration Services Packages using Digital Certificates[/b]


http://www.databasejournal.com/features/mssql/securing-sql-server-2012-integration-services-packages-using-digital-certificates.html

Link to comment
Share on other sites

You’re creating a database to contain information for a university web site: news, academic announcements, admissions, events, research, etc. Should you use the relational model or XML?

Link to comment
Share on other sites

[quote name='Kaarthikeya' timestamp='1359069064' post='1303169233']
[b] SQL Server Backup and Restore[/b]

Download eBook (PDF): [url="http://www.simple-talk.com/redgatebooks/ShawnMcGehee/sql-server-backup-restore.pdf"]Download here[/url]
[/quote]
mama nuvvu sql dba na??
naaku server side trace vs profiler trace gurichi info (performace impact) kavali..

Link to comment
Share on other sites

[quote name='stewiegriffin' timestamp='1359072202' post='1303169449']
mama nuvvu sql dba na??
naaku server side trace vs profiler trace gurichi info (performace impact) kavali..
[/quote]kaadu mama..nenu Developer...em info kaavalo ikkada adugu...mana vaallu help chestaru H&*()
[quote name='Sri0234' timestamp='1359073211' post='1303169524']
SQL DBA training kavali....help ...plzz pllzzz...z.z..
[/quote]
ekkada nundi kaavali training? s%H#

Link to comment
Share on other sites

×
×
  • Create New...