> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Use Userfile Storage

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

To put files on your CockroachDB cluster without external servers, use `userfile`, a per-user file storage. To interact with `userfile`, use the following <InternalLink path="cockroach-commands">commands</InternalLink>:

* [`cockroach userfile upload`](#upload-a-file)
* [`cockroach userfile list`](#list-files)
* [`cockroach userfile get`](#get-files)
* [`cockroach userfile delete`](#delete-files)

Once a userfile is uploaded, you can run [`IMPORT INTO`](#import-from-userfile).

You can create an external connection to represent an external storage or sink URI. This allows you to specify the external connection's name in statements rather than the provider-specific URI. For detail on using external connections, see the <InternalLink path="create-external-connection">`CREATE EXTERNAL CONNECTION`</InternalLink> page.

## Upload a file

<Note>
  A userfile uses storage space in the cluster, and is replicated with the rest of the cluster's data. We recommend using <InternalLink path="cockroach-userfile-upload">`cockroach userfile upload`</InternalLink> for quick uploads from your client (about 15MB or smaller).
</Note>

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach userfile upload /Users/maxroach/Desktop/test-data.csv /test-data.csv --certs-dir=certs
```

```
successfully uploaded to userfile://defaultdb.public.userfiles_root/test-data.csv
```

For more information, see <InternalLink path="cockroach-userfile-upload">`cockroach userfile upload`</InternalLink>.

## List files

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach userfile list '*.csv' --certs-dir=certs
```

```
userfile://defaultdb.public.userfiles_root/test-data-2.csv
userfile://defaultdb.public.userfiles_root/test-data.csv
```

For more information, see <InternalLink path="cockroach-userfile-list">`cockroach userfile list`</InternalLink>.

## Get files

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach userfile get test-data.csv --certs-dir=certs
```

For more information, see <InternalLink path="cockroach-userfile-get">`cockroach userfile get`</InternalLink>.

## Delete files

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach userfile delete test-data.csv --certs-dir=certs
```

```
deleted userfile://defaultdb.public.userfiles_root/test-data.csv
```

For more information, see <InternalLink path="cockroach-userfile-delete">`cockroach userfile delete`</InternalLink>.

## Import from `userfile`

To import from `userfile`, first create the table that you would like to import into:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE customers (
  id INT,
  dob DATE,
  first_name STRING,
  last_name STRING,
  joined DATE
);
```

Then, use `IMPORT INTO` to import data into the table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IMPORT INTO customers (id, dob, first_name, last_name, joined)
   CSV DATA ('userfile:///test-data.csv');
```

`userfile:///` references the default path (`userfile://defaultdb.public.userfiles_$user/`).

```
        job_id       |  status   | fraction_completed |  rows  | index_entries |  bytes
---------------------+-----------+--------------------+--------+---------------+-----------
  599865027685613569 | succeeded |                  1 | 300024 |             0 | 13389972
(1 row)
```

For more import options, see <InternalLink path="import-into">`IMPORT INTO`</InternalLink>.

## Backup and restore with `userfile`

We recommend starting backups from a time at least 10 seconds in the past using <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink>. Read our guidance in the <InternalLink path="backup#performance">Performance</InternalLink> section on the <InternalLink path="backup">`BACKUP`</InternalLink> page.

<Note>
  Only database and table-level backups are possible when using `userfile` as storage. Restoring cluster-level backups will not work because `userfile` data is stored in the `defaultdb` database, and you cannot restore a cluster with existing table data.
</Note>

When working on the same cluster, `userfile` storage allows for database and table-level backups.

First, run the following statement to backup a database to a directory in the default `userfile` space:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BACKUP DATABASE bank INTO 'userfile://defaultdb.public.userfiles_$user/bank-backup' AS OF SYSTEM TIME '-10s';
```

This directory will hold the files that make up a backup; including the manifest file and data files.

<Note>
  When backing up from a cluster and restoring a database or table that is stored in your `userfile` space to a different cluster, you can run <InternalLink path="cockroach-userfile-get">`cockroach userfile get`</InternalLink> to download the backup files to a local machine and <InternalLink path="cockroach-userfile-upload#upload-a-directory-recursively">`cockroach userfile upload -r <location/of/file> <userfile destination/of/file> --url {CONNECTION STRING}`</InternalLink> to upload to the `userfile` of the restoring cluster.
</Note>

In cases when your database needs to be restored, run the following:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
RESTORE DATABASE bank FROM LATEST IN 'userfile://defaultdb.public.userfiles_$user/bank-backup';
```

It is also possible to run `userfile:///bank-backup` as `userfile:///` refers to the default path `userfile://defaultdb.public.userfiles_$user/`.

Once the backup data is no longer needed, delete from the `userfile` storage with the following command:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
cockroach userfile delete bank-backup --url {CONNECTION STRING}
```

If you use `cockroach userfile delete {file}`, it will take as long as the <InternalLink path="configure-replication-zones">garbage collection</InternalLink> to be removed from disk.

## See also

* <InternalLink path="cockroach-userfile-upload">`cockroach userfile upload`</InternalLink>
* <InternalLink path="cockroach-userfile-list">`cockroach userfile list`</InternalLink>
* <InternalLink path="cockroach-userfile-get">`cockroach userfile get`</InternalLink>
* <InternalLink path="cockroach-userfile-delete">`cockroach userfile delete`</InternalLink>
* <InternalLink path="cockroach-commands">`cockroach` Commands Overview</InternalLink>
* <InternalLink path="import-into">`IMPORT INTO`</InternalLink>