In various scenarios, a SQL Server restore a database to a point in time might be required. Typically, the following situations require this type of recovery:
· A data corruption occurred by a user malicious action. For example, a user or DBA might delete or update the contents of one or more tables by mistake, drop database objects that are still required during an update to an application, or perform a large batch update process that fails
· A database upgrading process goes awry or a database upgrade script fails
· A complete database recovery after a media failure cannot be performed, because the required transaction log and database backups are missing
The ability to perform this kind of recovery depends on a recovery model set for the database. The database must be in either the Full or Bulk-Logged recovery model. In case the Simple recovery mode was used, this recovery method is not possible.
CREATE
DATABASE SQLAuthority
GO
-- Make sure database is
in full recovery
ALTER DATABASE SQLAuthority
SET RECOVERY FULL
GO
USE SQLAuthority
GO
-- Create Table
CREATE TABLE TestTable (ID INT)
GO
-- Taking full backup
BACKUP DATABASE [SQLAuthority] TO DISK = N'D:\SQLAuthority.bak'
GO
INSERT INTO TestTable (ID)
VALUES (1)
GO
-- Selecting the data
from TestTable
SELECT *
FROM TestTable
GO
INSERT INTO TestTable (ID)
VALUES (2)
GO
-- Taking log backup
BACKUP LOG [SQLAuthority] TO
DISK = N'D:\SQLAuthority1.trn'
GO
INSERT INTO TestTable (ID)
VALUES (3)
GO
INSERT INTO TestTable (ID)
VALUES (4)
GO
BACKUP LOG [SQLAuthority] TO
DISK = N'D:\SQLAuthority2.trn'
GO
-- Selecting the data
from TestTable
SELECT *
FROM TestTable
GO
--//////////////////////Single user mode///////////////////////////////////////
ALTER DATABASE SQLAuthority
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE SQLAuthority
SET MULTI_USER;
GO
///////////////////////////////////////////
USE master
RESTORE DATABASE [SQLAuthority]
FROM DISK = N'D:\SQLAuthority.bak'
WITH
REPLACE
,NORECOVERY;
USE master
--////////////////////////////////////////
RESTORE DATABASE [SQLAuthority]
FROM DISK = N'D:\SQLAuthority1.trn'
WITH NORECOVERY;
--//////////////////////////////////////////
RESTORE DATABASE [SQLAuthority]
FROM DISK = N'D:\SQLAuthority2.trn'
WITH RECOVERY,stopat='2017-02-20 14:42:18.797';
--//////////////////////////////////////////////////////////
Backing up and restoring the tail-log end of a transaction log
USE master
GO
CREATE DATABASE sampledatabase
GO
ALTER DATABASE sampledatabase SET RECOVERY FULL
USE sampledatabase
GO
CREATE TABLE SampleData
(
id INT PRIMARY KEY ,
col1 tinyint NOT NULL ,
col2 BIT NOT NULL ,
col3 BIT NOT NULL ,
RegistrationDate DATETIME NOT NULL
)
GO
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94495, 25, 1, 1, '02-03-2008')
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94496, 26, 1, 1, '02-04-2008')
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94497, 27, 1, 1, '02-05-2008')
BACKUP DATABASE sampledatabase TO DISK ='D:\sqldata\backups\sampledata_full.bak' WITH INIT,STATS;
GO
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94498, 29, 1, 1, '02-03-2009')
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94499, 28, 1, 1, '02-04-2009')
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94500, 30, 1, 1, '02-05-2009')
SELECT * FROM sampledata
BACKUP DATABASE sampledatabase TO DISK = 'D:\sqldata\backups\sampledata_diff.bak' WITH DIFFERENTIAL
GO
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94501, 29, 1, 1, '02-03-2010')
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94502, 28, 1, 1, '02-04-2010')
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94503, 30, 1, 1, '02-05-2010')
BACKUP LOG sampledatabase TO DISK ='D:\sqldata\backups\sampledata_Log1.bak' WITH INIT,STATS;
GO
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94505, 29, 1, 1, '02-08-2010')
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94506, 28, 1, 1, '02-09-2010')
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94507, 30, 1, 1, '12-05-2010')
BACKUP LOG sampledatabase TO DISK ='D:\sqldata\backups\sampledata_Log2.bak' WITH INIT,STATS;
GO
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94508, 29, 1, 1, '02-28-2011')
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94510, 28, 1, 1, '02-27-2011')
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (94511, 30, 1, 1, '12-12-2011')
BACKUP LOG sampledatabase TO DISK ='D:\sqldata\backups\sampledata_Log3.bak' WITH INIT,STATS;
GO
INSERT [dbo].[SampleData] ([id],[col1], [col2], [col3], [RegistrationDate])
VALUES (100000, 30, 1, 1, '12-02-2012')
SELECT TOP 5*
FROM sampledata
ORDER BY id DESC
select * from sampledata
USE master
GO
ALTER DATABASE sampledatabase SET OFFLINE WITH NO_WAIT;
--simulate the disaster by deleting the .mdf file
ALTER DATABASE sampledatabase SET ONLINE
--this will result in a nasty error since there is no database file
BACKUP LOG sampledatabase TO DISK ='D:\sqldata\backups\sampledata_TailLog.bak' WITH INIT,NO_TRUNCATE;
GO
RESTORE DATABASE sampledatabase FROM DISK='D:\sqldata\backups\sampledata_full.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE sampledatabase FROM DISK='D:\sqldata\backups\sampledata_diff.bak' WITH NORECOVERY
RESTORE DATABASE sampledatabase FROM DISK='D:\sqldata\backups\sampledata_Log1.bak' WITH NORECOVERY
RESTORE DATABASE sampledatabase FROM DISK='D:\sqldata\backups\sampledata_Log2.bak' WITH NORECOVERY
RESTORE DATABASE sampledatabase FROM DISK='D:\sqldata\backups\sampledata_Log3.bak' WITH NORECOVERY
RESTORE DATABASE sampledatabase FROM DISK='D:\sqldata\backups\sampledata_TailLog.bak' WITH NORECOVERY
RESTORE DATABASE sampledatabase WITH RECOVERY
USE sampledatabase
GO
SELECT TOP 5*
FROM sampledata
ORDER BY id DESC
Let me explain what I am doing in the T-SQL script above.
1) In lines 1-21 I create a sample database,set the recovery model to FULL and create a sample table in it.
2) In lines 23-30 I insert 3 records in the table.
3) In lines 32-33 I take a full database backup.
4) In lines 35-42 I insert 3 more records in the table.
5) In lines 46-47 I take a differential backup.
6) In lines 49-56 I insert 3 more records in the table.
7) In lines 58-59 I take another transaction log backup.
8) In lines 61-68 I insert 3 more records in the table.
9) In lines 70-71 I take another transactional log backup.
10) In lines 73-80 I insert 3 more records in the table.
11) In lines 82-83 I take another transaction log backup.
12) In lines 85-86 I insert a new record in the table.
13) In lines 88-90 Ι perform a simple “Select” to see the newly inserted record.
14) In lines 92-95 I take the database offline.Then I go to the local path and delete the .mdf file.In my case the path is C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA. As you can understand I am simulating a disaster.
15) In line 99 I try to take the database online but I receive a nasty error as expected.
16) In lines 103-104 I take a tail-log backup.This is the first step in the restore process.
17) In lines 106-117 I restore with the correct sequence (full,differential,3 t-log backups,final tail-log backup). Everything works fine.
18) In lines 122-124, I am looking for the last record (id=100.000) that was inserted after the last t-log backup but was picked up from the tail-log backup. So despite the catastrophe I had, my database is back online and there is no data loss.
Please note that not everyone has the permission to backup a database.This permission is limited by default to members of the sysadmin fixed server role,members of the db_owner and db_backupoperator fixed database role.
Hope it helps!!!
Step 1: Get the current database files Logical Name and Physical Location
USE master
GO
SELECT name AS LogicalFileName, physical_name AS FileLocation
, state_desc AS Status
FROM sys.master_files
WHERE database_id = DB_ID('AdventureWorks2012');
Step 2: Take the Database offline
USE master
GO
ALTER DATABASE AdventureWorks2012 SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
Step 3: Move the database files physically in the Windows OS to the new location
Current Files Location: C:\Disk1
New\Target Files Location: C:\Disk2
Step 4: Use ALTER DATABASE to modify the FILENAME to new location for every file moved
Only one file can be moved at a time using ALTER DATABASE.
USE master
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE
( NAME = AdventureWorks2012_Data,
FILENAME = 'C:\Disk2\AdventureWorks2012_Data.mdf'); -- New file path
USE master
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE
( NAME = AdventureWorks2012_Log,
FILENAME = 'C:\Disk2\AdventureWorks2012_log.ldf'); -- New file path
Step 5: Set the database ONLINE
USE master
GO
ALTER DATABASE AdventureWorks2012 SET ONLINE;
Step 6: Now, verify the database files Physical location
USE master
GO
SELECT name AS FileName, physical_name AS CurrentFileLocation, state_desc AS Status
FROM sys.master_files
WHERE database_id = DB_ID('AdventureWorks2012');
Find N th salary (CTE)
with result as
(
select salary,DENSE_RANK() over (order by salary desc) as DENSERANK
from employee
)
select top 1 salary from result
where result.denserank=3
////////////////////////BCP////////////////////////////////////////
bcp JDE_PROD9_Rep_JDE_Reporting_Temporary.PRODDTA.F0101 out c:\Temp\F0101.csv -S SWVEDSCORSQLP05\OPTIMSTUDENT -T -n
bcp JDE_PROD9_Rep_JDE_Reporting_Temporary.PRODDTA.F0111 out c:\Temp\F0101.csv -S SWVEDSCORSQLP05\OPTIMSTUDENT -T -n
//////////////////////////Export////////////////////////////////////////////
bcp Test.dbo.emp out c:\Temp\emp.csv -S VINOD-PC -T -n
bcp Test.dbo.OLEDBDest out c:\Temp\OLEDBDest.csv -S VINOD-PC -T -n
bcp Test.dbo.sales out c:\Temp\sales.csv -S VINOD-PC -T -n
//////////////////////////Import//////////////////////////////////////////////
bcp Test2.dbo.emp IN c:\Temp\emp.csv -S VINOD-PC -T -n
bcp Test2.dbo.OLEDBDest IN c:\Temp\OLEDBDest.csv -S VINOD-PC -T -n
bcp Test2.dbo.sales IN c:\Temp\sales.csv -S VINOD-PC -T -n
-S ServerName\instanceName
-T to indicate Trusted Connection (i.e. Windows Authentication)
-n indicates native data type
-c indicates character data type
///////////////////////////////////////////////////////
Bookmark lookup, Key lookup, RID lookup Meaning and when SQL Server Retrieve it?
Bookmark lookup or RID lookup showing in the Execution plan when you select Columns not included on your index
If table has clustered index, it is called bookmark lookup (or key lookup); if the table does not have clustered index, but a non-clustered index, it is called RID lookup.
You must remove the lookup from the Execution plan to improve your Performance.
now if we need to return Small Data by Query SQl Server optimizer will go to use Non Clustered index to the Columns in the Where Condition so if this Columns in the Where Condition not in the Non-Clustered index SQL Server will go to Select from data Pages .Even if the table has a clustered index or not, the query will still have to return to the table or clustered index to retrieve the data..
How can I remove bookmark lookup, RID lookup?
By two Method
1- Non-Clustered Covered Index.
2- Included Column in Non-Clustered Index.
CREATE NONCLUSTERED INDEX IDX_Employees_Covering ON Employees (DepartmentId,PositionId)
INCLUDE (FirstName,LastName,Birthdate,ManagerId,Salary,Address,City,State,HiredDate)
Index with INCLUDE clause is the covering index.
Covering index is basically used to cover the query(include columns from Select list which are not part of index) and to avoid bookmark lookup.
1023 columns can added in the INCLUDE clause.
Remove Key Lookup
Right Click on Missing Index >> Missing Index Detail >> Take the Result and Rename the name of the index and Run this Query
Index
Cluster
Non-cluster
Column store
Spatial
Xml
Unique
Filtered
Full text
Index with included column
Index on computed column
MAXDOP
This is very simple and known tip. Query Hint MAXDOP – Maximum Degree Of Parallelism can be set to restrict query to run on a certain CPU. Please note that this query cannot restrict or dictate which CPU to be used, but for sure, it restricts the usage of number of CPUs in a single batch.
Now the same query can be ran on a single core with the usage of MAXDOP query hint. Let us see the query for the same.
USE AdventureWorks
GO
SELECT *FROM Sales.SalesOrderDetail
ORDER BY ProductID
OPTION (MAXDOP 1)
GO