Sometimes data is deleted by accident. As Long as you have a comprehensive backup strategy in place, you should be able to recover your data. SQL Server stores backups of the database in .BAK files. These files contain a complete backup of all the data in the database at the time that the backup file is created.

To Create a backup copy of a database, use this command.

BACKUP DATABASE MyDatabase TO DISK = 'C:\MyDatabase.BAK'
GO

I find that it works well to create one .bak file per day, and then to keep these files for as long as you would ever need to recover data from back in time. Keep in mind all transactions that occur between backups are not backed up with this method until the next .bak file backup is taken.

If you need the ability to restore the database to a specific time or transaction, such as right before an accidental delete or data failure, you will need to use a transaction log backup in addition to the .bak file. The transaction log records all transactions in the database, and these logs must also be backed up if you need this ability to recover to a point in time.

You could do this with bak files if you were able to create a bak file after every transaction, however, this would consume a huge amount of storage and an enormous duplication of data, as all the data in each bak file would be the same except for the data that has changed in the most recent transaction, which may be as small as a single bit changing from 1 to 0.

Restoring Specific Rows in Multiple Tables

Sometimes, instead of rolling back the entire database using a bak file or the transaction log, you only need to restore a few select records from a couple tables. If your database consists of hundreds of tables and millions of records , this technique may be an easier way to recover your data.

First, you will still need to restore the entire database in order to access the deleted data. The difference is we will restore the database as a copy, not overwriting our target database.

Lets say we have a database with the following customers and orders tables

CREATE TABLE [dbo].[Customers](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](200) NULL,
	[LastName] [nvarchar](600) NULL,
	CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([ID] ASC))

GO

CREATE TABLE [dbo].[Orders](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [int] NULL,
	[Product] [nvarchar](500) NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED (	[ID] ASC))
GO

We restore a backup of this database using the backup dialog in SSMS. Since we are going to be extracting only the rows that we need for this restore process, be sure to name the database you are restoring to something different than the name of your original database. Here we have appended _Backup to the original database name.

In this method, since we are not going to use the transaction log, make sure to uncheck the Tail Log backup option on the options page. The bar at the top should say “Ready”

Restoring To The Same Server

Let’s say by some mistake we had accidently deleted all customers with last names starting with the letter Z
Here is how to restore only those records using the MyDatabase_Backup

If your source and destination databases are accessible from the same server, you can restore the data like this by turning identity insert on to insert into any identity columns, and then using an insert statement to transfer the data from on database to another.

SET IDENTITY_INSERT MyDatabase.dbo.Customers ON
Insert into MyDatabase.dbo.Customers (ID,FirstName,LastName)
SELECT ID,FirstName,LastName from MyDatabase_Backup.dbo.Customers where LastName like 'Z%'
SET IDENTITY_INSERT MyDatabase.dbo.Customers OFF

Export and Restoring to Another Server

If you aren’t restoring too much data, and you need to be able to quickly transfer between servers/environments, scripting just the data you need may be an efficient way of exporting the rows you need.
Let’s use the same example as above.

First, isolate the data you need into new tables prefixed with export_. The Select * Into structure works great for this.

select * into export_Customers from Customers where lastname like 'Z%'
select * into export_Orders from Orders where product like '%TEST%'

Now that we have our export tables, use the Database -> Generate Scripts command to export data from the export tables. To export data only and not the schema, make sure data only is selected in the advanced options

Select Data Only

A script will be generated that looks something like this. Copy this script to any server with a matching data schema, and quickly restore missing records.

USE [MyDatabase]
GO
SET IDENTITY_INSERT [dbo].[export_Customers] ON 
GO
INSERT [dbo].[export_Customers] ([ID], [FirstName], [LastName]) VALUES (6, N'Z_First', N'Z_Last2')
GO
INSERT [dbo].[export_Customers] ([ID], [FirstName], [LastName]) VALUES (7, N'Z_xx', N'Z_yy')
GO
SET IDENTITY_INSERT [dbo].[export_Customers] OFF
GO