deals2buy Posted January 23, 2013 Report Posted January 23, 2013 [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...
deals2buy Posted January 24, 2013 Report Posted January 24, 2013 [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.
deals2buy Posted January 24, 2013 Report Posted January 24, 2013 [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.
deals2buy Posted January 24, 2013 Report Posted January 24, 2013 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]
mtkr Posted January 24, 2013 Report Posted January 24, 2013 [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
loveindia Posted January 24, 2013 Report Posted January 24, 2013 @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....
deals2buy Posted January 24, 2013 Report Posted January 24, 2013 [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
deals2buy Posted January 24, 2013 Report Posted January 24, 2013 [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.
deals2buy Posted January 24, 2013 Report Posted January 24, 2013 [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]
deals2buy Posted January 24, 2013 Report Posted January 24, 2013 [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]
deals2buy Posted January 24, 2013 Report Posted January 24, 2013 [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
deals2buy Posted January 24, 2013 Report Posted January 24, 2013 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?
stewiegriffin Posted January 25, 2013 Report Posted January 25, 2013 [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..
Sri0234 Posted January 25, 2013 Report Posted January 25, 2013 SQL DBA training kavali....help ...plzz pllzzz...z.z..
deals2buy Posted January 25, 2013 Report Posted January 25, 2013 [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 [quote name='Sri0234' timestamp='1359073211' post='1303169524'] SQL DBA training kavali....help ...plzz pllzzz...z.z.. [/quote] ekkada nundi kaavali training?
Recommended Posts