Home » Database

Category Archives: Database

Cumulative Update #2 for SQL Server 2016 SP1

The 2nd cumulative update release for SQL Server 2016 SP1 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates.

To learn more about the release or servicing model, please visit:

Previous CUs :

Those who are facing severe issues with their environment, they can plan to test CU2 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP2 final release to deploy on your production environment, to have consolidate build.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

Always on\Database Mirroring Automatic Page Repair

A special thanks to Daniel Jones [daniel.jones.0543@gmail.com] for showing up interest and writing informative article for our blog (http://mssqlfun.com/).

 

Always on\Database Mirroring Automatic Page Repair

One of the most useful as well as hottest feature in SQL Server is mirroring of database. It is helpful for numerous of organizations that implements the successful as well as relatively high-availability of inexpensive strategies. However, in all the latest versions of SQL Server its service is being updated in numerous of ways such as database mirroring automatic page repair. It repairs the corrupt pages of the database. The automatic page repair is maintained by the database mirroring and Always on Availability Groups. After some specific types of errors, which had occurred in the database further make it in non-readable form. An availability replica tries to automatically repair the pages. The replica, which is unable to tread the page from another replica. If the request is implemented then, all the non-readable pages are replaced by the readable copies. It mainly resolves the occurrence of error.

Note: The automatic repair is completely differing from DBCC repair as all the database is stored by an automatic recovery of page.

Errors Occurred by Automatic Page-Repair

Database Mirroring of Automatic page repair efforts to repair in the data file through which an operation is failed as mentioned below:

  • Error 883

Description: Action is executed only if the OS implement CRC (cyclic redundancy check), which fails on the data.

Instances: ERROR_CRC. The operating system value for this error is 23

  • Error 824

Description: Logical

Instances: Logical data errors like checksum of torn write or bad page.

 

Techniques to Resolve Errors

  • Primary Database

The primary database of automatic page repair is tried when the state of database is SYNCHRONIZED. Moreover, the principal is still transferring log records for the database to the secondary. The elementary sequence of actions in an automatic attempt of page-repair are as mentioned:

  1. When an error of read comes up on a data page in the primary database then, the primary inserts row in the suspect_pages table with the correct status of an error. For database mirroring, the principal then, requests a copy of page from the mirror. For the Always On Availability Groups, the primary broadcasts the appeal to all the secondary’s and acquires the page from the first to react. The request identifies the ID and LSN, which is presently at end of flushed log. The page is marked as pending restore. This marks it inaccessible throughout the automatic page-repair effort. Attempts to utilize this page in the course of the repair attempt will fail with an error code 829.
  2. After getting page request, the secondary gaps till it has recreated the log up to the LSN stated in the request. After that, the mirror efforts to utilize the page in its database copy. If the page can be used, the mirror directs the copy of page to the principal. Otherwise, the secondary proceeds an error to the principal and the automatic page-repair effort fails.
  3. The primary methods the response, which holds the fresh copy of page.
  4. After the automatic page-repair effort fixes a suspect page, the page is noticeable in the suspect_pages table as reinstated.

 

  • Secondary Database

The input and output errors on pages, which happen on the secondary database are held in generally the similar way by mirroring and Always On Availability Groups.

  1. With mirroring of database, if the mirror comes upon one or more page errors of I/O when it rebuilds a log record, the session of mirroring arrives with the SUSPENDED state. Through Always On Availability Groups, if a replica of secondary meets one or more page errors of I/O when it recreates a log record, the secondary database enters the state of SUSPENDED. At that point, the mirror insets a row in the suspect_pages table with the suitable error status. The secondary then, requests a copy of page from the primary.
  2. The principal attempts to use the page in its database copy. If the page can be edited, the primary directs the copy of page to the secondary.
  3. If the mirror obtains copies of each page it has demanded, the secondary efforts to restart the mirroring session. If an automatic page-repair go fixes a suspect page, the page is clear in the suspect_pages table as reinstated.

If secondary does not obtain a page, which is requested from the principal, the automatic page-repair effort fails. With mirroring of database, the mirroring session leftovers suspended. With Always On Availability Groups, the secondary database leftovers suspended. If the mirroring session or secondary database is restarted manually, the corrupted pages will be again hit at the time of synchronization phase.

View Attempts of Automatic Page-Repair

The mentioned dynamic management previews return rows for the up-to-date automatic page-repair efforts on a given availability database, with maximum of 100 rows per database.

  • Always On Availability Groups:

sys.dm_hadr_auto_page_repair

Gives a row for each automatic page-repair effort on any database availability on availability replica, which is hosted for any group availability by server instance.

  • Database mirroring:

sys.dm_db_mirroring_auto_page_repair

Returns row for all automatic page-repair try on any database mirrored on the server instance.

Conclusion

It is important to repair the page to maintain the continuity of the work. Therefore, we have discussed Database mirroring automatic page repair in the above discussion that makes easy for users to remove the hurdle and understand it clearly.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

Cumulative Update #11 for SQL Server 2014 SP1

The 11th cumulative update release for SQL Server 2014 SP1 is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates.

Please visit:

(Source : https://blogs.msdn.microsoft.com/sqlreleaseservices/)

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

How Row versioning impact tempDB ?

Scenario: DBA found that tempdDBdatabase usage is getting high and most of the size is consumed by row versioning. DBA raised the issue with application team running that query. Once Application team close the session, tempDB usage comes normal.

Question: Application tea raise concern “How can a select statement on table consume huge amount of resources in tempDB?”

Answer:

As per BOL (https://technet.microsoft.com/en-us/library/ms175492%28v=sql.105%29.aspx) : Row versioning-based isolation levels (RCSI – Read Committed Snapshot Isolation) reduce the number of locks acquired by transaction by eliminating the use of shared locks on read operations. This increases system performance by reducing the resources used to manage locks. Performance is also increased by reducing the number of times a transaction is blocked by locks acquired by other transactions. Row versioning-based isolation levels increase the resources needed by data modifications.

While updating the row in a table or index, the new row is marked with a value called the transaction sequence number (XSN), XSN is unique number and keeps updating with upcoming new command \ transactions. When updating a row, New version is generated. If DB is already having previous version of this row, previous version of the row is stored in the version store and the new version of the row contains a pointer to the old version of the row in the version store. New row version stores corresponding XSN value for reflecting the time the row was modified.

All version stores are linked in a chain. SQL Server keep running clean up task to remove old versions which are not in use. As long as a transaction is open, all versions of rows that have been modified by that transaction must be kept in the version store. This is the reason due to which long running open transaction can cause huge tempDB row versioning space.

Here, Row X is getting updated by multiple transactions (Tv,Tx,Ty,Tz) and each time new version store is created. When DB engine tries to clear old unused version stores, it found open transaction using version X. DB engine not able to clear any row version from version store after version X. Due to which TempDB size keeps increasing due to row versioning.

–Check Size of tempDB consumed by Version Store

SELECT SUM(VERSION_STORE_RESERVED_PAGE_COUNT) AS [VERSION STORE PAGES USED],

(SUM(VERSION_STORE_RESERVED_PAGE_COUNT)*1.0/128) AS [VERSION STORE SPACE IN MB],

SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT) AS [INTERNAL OBJECT PAGES USED],

(SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT)*1.0/128) AS [INTERNAL OBJECT SPACE IN MB],

SUM(USER_OBJECT_RESERVED_PAGE_COUNT) AS [USER OBJECT PAGES USED],

(SUM(USER_OBJECT_RESERVED_PAGE_COUNT)*1.0/128) AS [USER OBJECT SPACE IN MB],

SUM(UNALLOCATED_EXTENT_PAGE_COUNT) AS [FREE PAGES],

(SUM(UNALLOCATED_EXTENT_PAGE_COUNT)*1.0/128) AS [FREE SPACE IN MB]

FROM SYS.DM_DB_FILE_SPACE_USAGE;

–Find session using version store

SELECT A.*,B.KPID,B.BLOCKED,B.LASTWAITTYPE,B.WAITRESOURCE,B.DBID,B.CPU,B.PHYSICAL_IO,B.MEMUSAGE,B.LOGIN_TIME,B.LAST_BATCH,

B.OPEN_TRAN,B.STATUS,B.HOSTNAME,B.PROGRAM_NAME,B.CMD,B.LOGINAME,REQUEST_ID

FROM SYS.DM_TRAN_ACTIVE_SNAPSHOT_DATABASE_TRANSACTIONS A

INNER JOIN SYS.SYSPROCESSES B

ON A.SESSION_ID = B.SPID

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

Error while loading Excel data into database

Issue: I am trying to load data from Excel 97 – 2003 sheet into a database table. I have written a simple SSIS package to do that, but unfortunately, it fails with error: –

 

Microsoft (R) SQL Server Execute Package Utility  Version 11.0.6020.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  2:02:37 AM  Error: 2016-12-19 02:02:37.91     Code: 0xC0209303     Source: ExcelImpot Connection manager “SourceConnectionExcel”     Description: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.  An OLE DB record is available.  Source: “Microsoft OLE DB Service Components”  Hresult: 0x80040154  Description: “Class not registered”.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC001002B     Source: MAPTImpot Connection manager “SourceConnectionExcel”     Description: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC020801C     Source: Data Flow Task 1 Source – Sheet1$ [38]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “SourceConnectionExcel” failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC0047017     Source: Data Flow Task 1 SSIS.Pipeline     Description: Source – Sheet1$ failed validation and returned error code 0xC020801C.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC004700C     Source: Data Flow Task 1 SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC0024107     Source: Data Flow Task 1      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:02:37 AM  Finished: 2:02:37 AM  Elapsed:  0.733 seconds.  The package execution failed.  The step failed.

Cause: This error occurs when you try to execute 32-bit environment package in 64-bit system.

Solution 1: If you have project file of SSIS package or If you are running package other than SQL Agent jobs like calling from CMD.

Step 1: Navigate to Project-> [PROJECT_NAME] Properties.

Step 2: Navigate to “Debugging” option from left panel and from Right panel change Run64BitRuntime value to false.

Solution 2: If you need to execute package from SQL agent job only then you have readymade option available. Go to Job step and check below option.

 

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

Restrict user to login from single Host

Restricting user to login from single host may be sometimes required from security point and other business requirements. We can achieve it through SERVER LEVEL LOGON TRIGGER.

ALTER TRIGGER TR_CHECK_LOGIN_TEST_HOST
ON ALL SERVER
FOR LOGON
AS
BEGIN

DECLARE @HOSTNAME VARCHAR(48)
DECLARE @PROGNAME VARCHAR(100)

SELECT @HOSTNAME = HOST_NAME FROM SYS.DM_EXEC_SESSIONS
WHERE SESSION_ID = @@SPID

IF ORIGINAL_LOGIN() = ‘TEST’ AND @HOSTNAME =’HostName’
ROLLBACK;

END

Once you create above trigger, Login “TEST” will be able to connect from specified host only. Connection from any other host will be failed and face below error: –

You can also find related errors in SQL Server Error log as well.

Error: 17892, Severity: 20, State: 1.

Logon failed for login ‘TEST’ due to trigger execution. [CLIENT: <local machine>]

Error: 3609, Severity: 16, State: 2.

The transaction ended in the trigger. The batch has been aborted.

 

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

Error While running PowerShell code from SQL Agent job – “Path Does Not Exist”

Issue: When I am running code from PowerShell console its running fine but when I execute it through SQL agent job step, I am getting below error: –

The job script encountered the following errors. These errors did not stop the script:  A job step received an error at line 14 in a PowerShell script. The corresponding line is ‘$files = Get-ChildItem “\\$comp\D$\*.xls”‘. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘An object at the specified path \\ServerName\D$ does not exist.  ‘  A job step received an error at line 43 in a PowerShell script. The corresponding line is ‘    $str = Get-Content $i.FullName | Where-Object { ($_ -match ‘Alive’) }’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot bind argument to parameter ‘Path’ because it is null.  ‘.  Process Exit Code -1.  The step failed.

Solution: SQL Server Agent allows users to directly run PowerShell scripts in SQL Server Agent. Internally this is implemented by reusing the SQLPS.EXE shell. When SQL Server Agent starts SQLPS.EXE, It cannot access file system.

To access file system, you need to change the scope from SQL to filesystem.

You can do it by adding “cd C:” before executing filesystem commands.

 

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

What’s new in SQL Server 2016 installation?

1) As MS is kept increasing number of option while installation. To reduce no. of click, if setup pass Rules it will automatically moves to Next window.

2) On Server Configuration page, MS specifically ask to GRANT PERFORM VOLUME MAINTENANCE TASK permission to service account. SQL Services needs this access to perform instant file initialization for data files instead of zero file initialization.  (For information read – https://msdn.microsoft.com/library/ms175935)

3) 2 New Tabs in Database Engine Configuration page. One is TempDB configuration & other is User Instance feature.

4) Separate Acceptance page for Microsoft R.

Step  by Step Installation :-

1. Setup Page, Choose First option

2. Setup checks for Global Rules and moves to next page, if all rules passed

3. Option to choose, if you want setup to download latest update and install

4. Initial Setup files installed

5. Check for installation rules and moves to next page, if all rules passed

6. Accept terms and condition, Click to Next

7. Select features you want to install

8. Check feature rule and moves to next page, if all rules passed

9. Select Instance name

10. Specify service accounts, you can also select collation from collation tab.

Here, MS specifically ask to GRANT PERFORM VOLUME MAINTENANCE TASK permission to service account. (For information read – https://msdn.microsoft.com/library/ms175935)

https://msdn.microsoft.com/library/ms175935

11. Select Authentication type

12. Select Data Directories

13. New Tab introduced to provide TempDB configuration at time of installation only

14. New Tab introduced for “User Instance” feature. Although, MS marked this feature to be removed. After this special tab, It seems MS changed his mind.

Refer – https://technet.microsoft.com/en-us/library/ms143684

15. Option to enable FileStream

16. Option for SSRS installation and configuration

17. New Page added for Microsoft R acceptance

18. Installation on the way

19. Installation Completed Successfully

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Sequence – Long awaited feature

As per BOL (https://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx) : A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.

This feature is really a good to have and this feature is same as Oracle’s sequence objects.  A sequence object generates sequence of unique numeric values as per specifications mentioned.

A sequence can be defined as any integer data type. If the data type is not specified, a sequence defaults to bigint. A sequence value is not effected by SQL server restart. Many people think sequence is similar to identity. But big difference is sequence object is independent of a table while identity columns are attached to a table.

Create Sequence

CREATE SEQUENCE TEMPSEQ AS INT

START WITH 1 — START WITH VALUE 1

INCREMENT BY 1– INCREMENT WITH VALUE 1

MINVALUE 0 — MINIMUM VALUE TO START IS 0

MAXVALUE 5 — MAXIMUM IT CAN GO TO 5

NO CYCLE — DO NOT GO ABOVE 5

CACHE 2 — INCREMENT 2 VALUES IN MEMORY RATHER THAN INCREMENTING FROM IO

Generate value from Sequence

SELECT NEXT VALUE FOR TEMPSEQ AS seq_no;

Error when Sequence reach to MAX value

Msg 11728, Level 16, State 1, Line 18

The sequence object ‘TEMPSEQ’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Alter Sequence to RESTART it from 1 or desired value

ALTER SEQUENCE TEMPSEQ RESTART WITH 1

 

Create Sequence with CYCLE mode, where value will be reset to MIN value when it reaches to MAX value

CREATE SEQUENCE TEMPSEQ_CYCLE AS INT

START WITH 1 — START WITH VALUE 1

INCREMENT BY 1– INCREMENT WITH VALUE 1

MINVALUE 0 — MINIMUM VALUE TO START IS 0

MAXVALUE 5 — MAXIMUM IT CAN GO TO 5

CYCLE — RESET to 0 once reached to MAX VALUE 5

CACHE 2 — INCREMENT 2 VALUES IN MEMORY RATHER THAN INCREMENTING FROM IO

Output of Sequence with CYCLE

SELECT NEXT VALUE FOR TEMPSEQ_CYCLE AS ID, NAME FROM SYS.OBJECTS

Sequence

Alter existing Sequence to set it to CYCLE mode

ALTER SEQUENCE TEMPSEQ CYCLE — RESET to 0 once reached to MAX VALUE 5

Use Sequence in Table as Default constraint.

Scenario: We need to generate token for each customer coming to meet him with HR & finance personal.

  1. Create Sequence

CREATE SEQUENCE TOKENNO AS INT

START WITH 1 — START WITH VALUE 1

INCREMENT BY 1– INCREMENT WITH VALUE 1

MINVALUE 0 — MINIMUM VALUE TO START IS 0

MAXVALUE 5000 — MAXIMUM IT CAN GO TO 5000

NO CYCLE — RESET to 0 once reached to MAX VALUE 5000

CACHE 200 — INCREMENT 200 VALUES IN MEMORY RATHER THAN INCREMENTING FROM IO

  1. Create Table with Sequence

CREATE TABLE HR_EXECUTIVE_MEETING

(TOKENNO INT DEFAULT (NEXT VALUE FOR TOKENNO), DESKNO INT)

 

CREATE TABLE FINANCE_EXECUTIVE_MEETING

(TOKENNO INT DEFAULT (NEXT VALUE FOR TOKENNO), DESKNO INT)

  1. Insert into table having Sequence

INSERT INTO HR_EXECUTIVE_MEETING(TOKENNO,DESKNO) VALUES(DEFAULT,22)

INSERT INTO FINANCE_EXECUTIVE_MEETING(TOKENNO,DESKNO) VALUES(DEFAULT,7)

  1. Check the table values

SELECT * FROM HR_EXECUTIVE_MEETING

SELECT * FROM FINANCE_EXECUTIVE_MEETING

Sequence

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

 

 

Creation & Deletion of Database Snapshot by SQL Agent Job

Some days back, I received one query on my facebook page that user want to create Database Snapshot frequently & want to delete the oldest with same frequency. Quite interesting scenario. I did not go in deep why he has such requirement but I am clear that It can be done very easily.

So let’s do it. I have setup 2 scripts one for creation of snapshot & another is for deletion of snapshot. We can schedule a SQL agent jobs with 2 steps using these scripts.

Step 1 :-

DECLARE @CREATE_SS VARCHAR(MAX)

DECLARE @DT VARCHAR(100)

SET @DT = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),’-‘,’_’),’ ‘,’_’),’:’,’_’)

SET @CREATE_SS =

‘CREATE DATABASE TEST_’+@DT+’ ON

( NAME = TEST, FILENAME =

”C:\TEST_’+@DT+’.SS” )

AS SNAPSHOT OF TEST’

EXEC (@CREATE_SS)

Step 2 :-

IF(SELECT COUNT(*) FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’))>1

BEGIN

DECLARE @DROP_SS VARCHAR(MAX)

DECLARE @SS_NAME VARCHAR(100)

SELECT TOP 1 @SS_NAME = NAME FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’)

ORDER BY CREATE_DATE ASC

SET @DROP_SS = ‘DROP DATABASE ‘+ @SS_NAME

EXEC (@DROP_SS)

END

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

%d bloggers like this: