# Attach an Unlimited Storage Database Using Point-in-Time Recovery (PITR)

> **📝 Note**: Unlimited storage databases are not available in all editions of SingleStore. For more information, see [SingleStore Editions](https://docs.singlestore.com/db/v9.1/introduction/singlestore-editions.md).

The following example demonstrates how to restore an unlimited storage database. The Setup section contains the prerequisite steps needed to create the database and milestones, prior to performing the restore.

## Setup

1\. Create the unlimited storage database `bottomless_db` in the object storage bucket `bottomless_db_bucket` in the folder `bottomless_db_folder`.

The following definition assumes you are using Amazon S3 as the object storage provider.

Note that `aws_session_token` is required only if your credentials in the `CREDENTIALS` clause are temporary.

```sql
CREATE DATABASE bottomless_db ON S3 "bottomless_db_bucket/bottomless_db_folder"
  CONFIG '{"region":"us-east-1"}'
  CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key","aws_session_token":"your_session_token"}';
```

The following definition assumes you are using Azure as the object storage provider.

```sql
CREATE DATABASE bottomless_db ON AZURE "bottomless_db_bucket/bottomless_db_folder"  
    CONFIG ''  
    CREDENTIALS '{"account_name":"your_account_name","account_key":"your_account_key"}';
```

The following definition assumes you are using GCS as the object storage provider.

```sql
CREATE DATABASE bottomless_db ON GCS "bottomless_db_bucket/bottomless_db_folder"
  CONFIG '' 
  CREDENTIALS'{"access_id":"your_access_key_id", "secret_key":"your_secret_access_key"}';
```

2\. Make some updates to `bottomless_db`. In this example, you update the database by creating a table and inserting some data:

```sql
USE bottomless_db;
CREATE TABLE t(a INT);
INSERT INTO t(a) VALUES (10);
INSERT INTO t(a) VALUES (20);
```

3\. Create a milestone (a restore point):

```sql
CREATE MILESTONE "after_second_insert" FOR bottomless_db;
```

4\. Make more updates to `bottomless_db`:

```sql
INSERT INTO t(a) VALUES (30);
INSERT INTO t(a) VALUES (40);
```

Create a second milestone:

```sql
CREATE MILESTONE "after_fourth_insert" FOR bottomless_db;
```

## Perform the Restore

Suppose the values `30` and `40` were inserted into `t` in error. You now want to restore the database to the milestone `after_second_insert`. Detach the database to bring it offline:

```sql
DETACH DATABASE bottomless_db;
```

Then attach the database at the restore point:

For S3:

```sql
ATTACH DATABASE bottomless_db ON S3 "bottomless_db_bucket/bottomless_db_folder"
  CONFIG '{"region":"us-east-1"}'
  CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}'
  AT MILESTONE "after_second_insert";
```

For Azure:

```sql
ATTACH DATABASE bottomless_db ON AZURE "bottomless_db_bucket/bottomless_db_folder"
 CONFIG ''  
 CREDENTIALS '{"account_name":"your_account_name","account_key":"your_account_key"}'
 AT MILESTONE "after_second_insert";
```

For GCS:

```sql
ATTACH DATABASE bottomless_db ON GCP "bottomless_db_bucket/bottomless_db_folder"
 CONFIG ''  
 CREDENTIALS '{"access_id":"your_access_key_id", "secret_key":"your_secret_access_key"}';
 AT MILESTONE "after_second_insert";
```

You can restore to any point in time for which all the data is available on an object store. It is not necessary to restore to a named milestone.

A database cannot be restored to a PITR milestone taken before the database was dropped. This is because [DROP DATABASE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/drop-database.md) removes the PITR history.

## Daylight Saving Time Ambiguity in PITR

When you do a PITR using the following timestamp format,

```sql
ATTACH DATABASE x_db AT TIME 'YYYY-MM-DD HH:MM:SS';
```

the engine uses the server timezone. This can create ambiguity when daylight saving time (DST) changes time by one hour. For example, if you input the time as 1:05AM, was that before or after DST?  The time used by the engine and the user scripts may not match.

The solution is to use Unix time stamps or alternatively, use milestones to avoid this problem.

***

Modified at: July 31, 2024

Source: [/db/v9.1/manage-data/unlimited-data-storage/attach-an-unlimited-storage-database-using-point-in-time-recovery-pitr/](https://docs.singlestore.com/db/v9.1/manage-data/unlimited-data-storage/attach-an-unlimited-storage-database-using-point-in-time-recovery-pitr/)

(An index of the documentation is available at /llms.txt)