Question: I am a SQL Server DBA. I recently had the misfortune of losing a database due to drive failure. I was able to restore the data but for whatever reason, it stopped at a certain time and not all of the data was restored. 

I restored the database again to a test server only to find and restore ALL of the data but the problem is, I now have to get the rest of the data that did not restore on to the server where it is supposed to be. Can that be done? 

Given all the primary key contraints and uniqueness of those keys involved, imagine we will have to renumber some of the rows that were affected because those keys have been reused once the database came back up and was operating.

Answer: 
To subract a month from a date you can use [`dateadd()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-2017).

For example

```
SET @MaxDate = dateadd(month, -1, @MaxDate);

```

would subtract one month from your `@MaxDate`. That would keep the day of the month though. (Unless this day doesn't exists for the month, then it's the last day of the month, so e.g. the 31th day might become the 30th.)

To get the date of the last day of a month a date is in [`eomonth()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-2017) can be used.

```
SET @MaxDate = eomonth(@MaxDate);

```

would set `@MaxDate` to the last day of the month `@MaxDate` was previously in.

`eomonth()` optionally takes a second argument, which is an integer to add to the month. So to set `@MaxDate` to the last day of the previous month of `@MaxDate` you might use

```
SET @MaxDate = eomonth(@MaxDate, -1);

```
I upvoted @StickyBits answer, but wanted to suggest the usage of a *Date Dimension* table (*if you don't already have one*) as another option. In addition to providing you the ability to determine the last day of any month, there is a wealth of other information that could also be used once you find a particular row in the *Date Dimension*. 

Check out [Aaron Bertrand's](https://dba.stackexchange.com/users/1186/aaron-bertrand) post regarding [Creating a date dimension or calendar table in SQL Server](https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/). Aaron's example *Date Dimension* starts off with a basic set of columns and then he shows you how to add other attributes.

```
CREATE TABLE dbo.DateDimension
(
  --DateKey           INT         NOT NULL PRIMARY KEY,
  [Date]              DATE        NOT NULL,
  [Day]               TINYINT     NOT NULL,
  DaySuffix           CHAR(2)     NOT NULL,
  [Weekday]           TINYINT     NOT NULL,
  WeekDayName         VARCHAR(10) NOT NULL,
  IsWeekend           BIT         NOT NULL,
  IsHoliday           BIT         NOT NULL,
  HolidayText         VARCHAR(64) SPARSE,
  DOWInMonth          TINYINT     NOT NULL,
  [DayOfYear]         SMALLINT    NOT NULL,
  WeekOfMonth         TINYINT     NOT NULL,
  WeekOfYear          TINYINT     NOT NULL,
  ISOWeekOfYear       TINYINT     NOT NULL,
  [Month]             TINYINT     NOT NULL,
  [MonthName]         VARCHAR(10) NOT NULL,
  [Quarter]           TINYINT     NOT NULL,
  QuarterName         VARCHAR(6)  NOT NULL,
  [Year]              INT         NOT NULL,
  MMYYYY              CHAR(6)     NOT NULL,
  MonthYear           CHAR(7)     NOT NULL,
  FirstDayOfMonth     DATE        NOT NULL,
  LastDayOfMonth      DATE        NOT NULL,
  FirstDayOfQuarter   DATE        NOT NULL,
  LastDayOfQuarter    DATE        NOT NULL,
  FirstDayOfYear      DATE        NOT NULL,
  LastDayOfYear       DATE        NOT NULL,
  FirstDayOfNextMonth DATE        NOT NULL,
  FirstDayOfNextYear  DATE        NOT NULL
);
GO

```

Using `DateAdd` to maneuver forward and backwards, you can find a particular row in the *Date Dimension* table and have access to many additional attributes about a particular date.

```
SELECT *
FROM dbo.DateDimension
WHERE [Date] = dateadd(month, - 2, convert(DATE, getdate()));

```