This page describes how to use point-in-time recovery (PITR) to retain andrecover data in Spanner for GoogleSQL-dialect databases and PostgreSQL-dialect databases.

To learn more, seePoint-in-time recovery.

Prerequisites

This guide uses the database and schema as defined in theSpannerquickstart. You can either runthrough the quickstart to create the database and schema, or modify the commandsfor use with your own database.

Set the retention period

To set your database's retention period:

gcloudspannerdatabasesddlupdateexample-db\--instance=test-instance\--ddl='ALTER DATABASE `example-db` \    SET OPTIONS (version_retention_period="7d");'

gcloudspannerdatabasesddldescribeexample-db\--instance=test-instance

ALTERDATABASEexample-dbSETOPTIONS(version_retention_period='7d');...

usingGoogle.Cloud.Spanner.Data;usingSystem.Threading.Tasks;publicclassCreateDatabaseWithRetentionPeriodAsyncSample{publicasyncTaskCreateDatabaseWithRetentionPeriodAsync(stringprojectId,stringinstanceId,stringdatabaseId){stringconnectionString=$"Data Source=projects/{projectId}/instances/{instanceId}";usingvarconnection=newSpannerConnection(connectionString);varversionRetentionPeriod="7d";varcreateStatement=$"CREATE DATABASE `{databaseId}`";varalterStatement=@$"ALTER DATABASE `{databaseId}` SET OPTIONS(version_retention_period='{versionRetentionPeriod}')";// The retention period cannot be set as part of the CREATE DATABASE statement,// but can be set using an ALTER DATABASE statement directly after database creation.usingvarcreateDbCommand=connection.CreateDdlCommand(createStatement,alterStatement);awaitcreateDbCommand.ExecuteNonQueryAsync();}}

voidCreateDatabaseWithVersionRetentionPeriod(google::cloud::spanner_admin::DatabaseAdminClientclient,std::stringconst&project_id,std::stringconst&instance_id,std::stringconst&database_id){google::cloud::spanner::Databasedatabase(project_id,instance_id,database_id);google::spanner::admin::database::v1::CreateDatabaseRequestrequest;request.set_parent(database.instance().FullName());request.set_create_statement("CREATE DATABASE `"+database.database_id()+"`");request.add_extra_statements("ALTER DATABASE `"+database.database_id()+"` "+"SET OPTIONS (version_retention_period='2h')");request.add_extra_statements(R"""(      CREATE TABLE Singers (          SingerId   INT64 NOT NULL,          FirstName  STRING(1024),          LastName   STRING(1024),          SingerInfo BYTES(MAX),          FullName   STRING(2049)              AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED      ) PRIMARY KEY (SingerId))""");request.add_extra_statements(R"""(      CREATE TABLE Albums (          SingerId     INT64 NOT NULL,          AlbumId      INT64 NOT NULL,          AlbumTitle   STRING(MAX)      ) PRIMARY KEY (SingerId, AlbumId),          INTERLEAVE IN PARENT Singers ON DELETE CASCADE)""");autodb=client.CreateDatabase(request).get();if(!db)throwstd::move(db).status();std::cout <<"Database " <<db->name() <<" created.\n";autoddl=client.GetDatabaseDdl(db->name());if(!ddl)throwstd::move(ddl).status();std::cout <<"Database DDL is:\n" <<ddl->DebugString();}

import("context""fmt""io""regexp"database"cloud.google.com/go/spanner/admin/database/apiv1"adminpb"cloud.google.com/go/spanner/admin/database/apiv1/databasepb")funccreateDatabaseWithRetentionPeriod(ctxcontext.Context,wio.Writer,dbstring)error{matches:=regexp.MustCompile("^(.+)/databases/(.+)$").FindStringSubmatch(db)ifmatches==nil||len(matches)!=3{returnfmt.Errorf("createDatabaseWithRetentionPeriod: invalid database id %q",db)}adminClient,err:=database.NewDatabaseAdminClient(ctx)iferr!=nil{returnfmt.Errorf("createDatabaseWithRetentionPeriod.NewDatabaseAdminClient: %w",err)}deferadminClient.Close()// Create a database with a version retention period of 7 days.retentionPeriod:="7d"alterDatabase:=fmt.Sprintf("ALTER DATABASE `%s` SET OPTIONS (version_retention_period = '%s')",matches[2],retentionPeriod,)req:=adminpb.CreateDatabaseRequest{Parent:matches[1],CreateStatement:"CREATE DATABASE `"+matches[2]+"`",ExtraStatements:[]string{`CREATE TABLE Singers (SingerId   INT64 NOT NULL,FirstName  STRING(1024),LastName   STRING(1024),SingerInfo BYTES(MAX)) PRIMARY KEY (SingerId)`,`CREATE TABLE Albums (SingerId     INT64 NOT NULL,AlbumId      INT64 NOT NULL,AlbumTitle   STRING(MAX)) PRIMARY KEY (SingerId, AlbumId),INTERLEAVE IN PARENT Singers ON DELETE CASCADE`,alterDatabase,},}op,err:=adminClient.CreateDatabase(ctx,&req)iferr!=nil{returnfmt.Errorf("createDatabaseWithRetentionPeriod.CreateDatabase: %w",err)}if_,err:=op.Wait(ctx);err!=nil{returnfmt.Errorf("createDatabaseWithRetentionPeriod.Wait: %w",err)}fmt.Fprintf(w,"Created database [%s] with version retention period %q\n",db,retentionPeriod)returnnil}

importcom.google.cloud.spanner.Spanner;importcom.google.cloud.spanner.SpannerException;importcom.google.cloud.spanner.SpannerExceptionFactory;importcom.google.cloud.spanner.SpannerOptions;importcom.google.cloud.spanner.admin.database.v1.DatabaseAdminClient;importcom.google.common.collect.Lists;importcom.google.spanner.admin.database.v1.CreateDatabaseRequest;importcom.google.spanner.admin.database.v1.Database;importcom.google.spanner.admin.database.v1.InstanceName;importjava.util.concurrent.ExecutionException;publicclassCreateDatabaseWithVersionRetentionPeriodSample{staticvoidcreateDatabaseWithVersionRetentionPeriod(){// TODO(developer): Replace these variables before running the sample.StringprojectId="my-project";StringinstanceId="my-instance";StringdatabaseId="my-database";StringversionRetentionPeriod="7d";createDatabaseWithVersionRetentionPeriod(projectId,instanceId,databaseId,versionRetentionPeriod);}staticvoidcreateDatabaseWithVersionRetentionPeriod(StringprojectId,StringinstanceId,StringdatabaseId,StringversionRetentionPeriod){try(Spannerspanner=SpannerOptions.newBuilder().setProjectId(projectId).build().getService();DatabaseAdminClientdatabaseAdminClient=spanner.createDatabaseAdminClient()){CreateDatabaseRequestrequest=CreateDatabaseRequest.newBuilder().setParent(InstanceName.of(projectId,instanceId).toString()).setCreateStatement("CREATE DATABASE `"+databaseId+"`").addAllExtraStatements(Lists.newArrayList("ALTER DATABASE "+"`"+databaseId+"`"+" SET OPTIONS ( version_retention_period = '"+versionRetentionPeriod+"' )")).build();Databasedatabase=databaseAdminClient.createDatabaseAsync(request).get();System.out.println("Created database ["+database.getName()+"]");System.out.println("\tVersion retention period: "+database.getVersionRetentionPeriod());System.out.println("\tEarliest version time: "+database.getEarliestVersionTime());}catch(ExecutionExceptione){// If the operation failed during execution, expose the cause.throw(SpannerException)e.getCause();}catch(InterruptedExceptione){// Throw when a thread is waiting, sleeping, or otherwise occupied,// and the thread is interrupted, either before or during the activity.throwSpannerExceptionFactory.propagateInterrupt(e);}}}

// Imports the Google Cloud client libraryconst{Spanner}=require('@google-cloud/spanner');/** * TODO(developer): Uncomment the following lines before running the sample. */// const projectId = 'my-project-id';// const instanceId = 'my-instance';// creates a clientconstspanner=newSpanner({projectId:projectId,});// Gets a reference to a Cloud Spanner Database Admin Client objectconstdatabaseAdminClient=spanner.getDatabaseAdminClient();try{// Create a new database with an extra statement which will alter the// database after creation to set the version retention period.console.log(`Creating database${databaseAdminClient.instancePath(projectId,instanceId,)}.`,);constversionRetentionStatement=`    ALTER DATABASE \`${databaseId}\`    SET OPTIONS (version_retention_period = '1d')`;const[operation]=awaitdatabaseAdminClient.createDatabase({createStatement:'CREATE DATABASE `'+databaseId+'`',extraStatements:[versionRetentionStatement],parent:databaseAdminClient.instancePath(projectId,instanceId),});console.log(`Waiting for operation on${databaseId} to complete...`);awaitoperation.promise();console.log(`      Created database${databaseId} with version retention period.`);const[metadata]=awaitdatabaseAdminClient.getDatabase({name:databaseAdminClient.databasePath(projectId,instanceId,databaseId),});console.log(`Version retention period:${metadata.versionRetentionPeriod}`);constmilliseconds=parseInt(metadata.earliestVersionTime.seconds,10)*1000+parseInt(metadata.earliestVersionTime.nanos,10)/1e6;constdate=newDate(milliseconds);console.log(`Earliest version time:${date.toString()}`);}catch(err){console.error('ERROR:',err);}

use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;use Google\Cloud\Spanner\Admin\Database\V1\CreateDatabaseRequest;use Google\Cloud\Spanner\Admin\Database\V1\GetDatabaseRequest;/** * Creates a database with data retention for Point In Time Restore. * Example: * ``` * create_database_with_version_retention_period($projectId, $instanceId, $databaseId, $retentionPeriod); * ``` * * @param string $projectId The Google Cloud project ID. * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. * @param string $retentionPeriod The data retention period for the database. */function create_database_with_version_retention_period(    string $projectId,    string $instanceId,    string $databaseId,    string $retentionPeriod): void {    $databaseAdminClient = new DatabaseAdminClient();    $instance = $databaseAdminClient->instanceName($projectId, $instanceId);    $databaseFullName = $databaseAdminClient->databaseName($projectId, $instanceId, $databaseId);    $operation = $databaseAdminClient->createDatabase(        new CreateDatabaseRequest([            'parent' => $instance,            'create_statement' => sprintf('CREATE DATABASE `%s`', $databaseId),            'extra_statements' => [                'CREATE TABLE Singers (' .                'SingerId     INT64 NOT NULL,' .                'FirstName    STRING(1024),' .                'LastName     STRING(1024),' .                'SingerInfo   BYTES(MAX)' .                ') PRIMARY KEY (SingerId)',                'CREATE TABLE Albums (' .                    'SingerId     INT64 NOT NULL,' .                    'AlbumId      INT64 NOT NULL,' .                    'AlbumTitle   STRING(MAX)' .                ') PRIMARY KEY (SingerId, AlbumId),' .                'INTERLEAVE IN PARENT Singers ON DELETE CASCADE',                "ALTER DATABASE `$databaseId` SET OPTIONS(version_retention_period='$retentionPeriod')"            ]        ])    );    print('Waiting for operation to complete...' . PHP_EOL);    $operation->pollUntilComplete();    $request = new GetDatabaseRequest(['name' => $databaseFullName]);    $databaseInfo = $databaseAdminClient->getDatabase($request);    print(sprintf(        'Database %s created with version retention period %s',        $databaseInfo->getName(), $databaseInfo->getVersionRetentionPeriod()    ) . PHP_EOL);}

defcreate_database_with_version_retention_period(instance_id,database_id,retention_period):"""Creates a database with a version retention period."""fromgoogle.cloud.spanner_admin_database_v1.typesimportspanner_database_adminspanner_client=spanner.Client()database_admin_api=spanner_client.database_admin_apiddl_statements=["CREATE TABLE Singers ("+"  SingerId   INT64 NOT NULL,"+"  FirstName  STRING(1024),"+"  LastName   STRING(1024),"+"  SingerInfo BYTES(MAX)"+") PRIMARY KEY (SingerId)","CREATE TABLE Albums ("+"  SingerId     INT64 NOT NULL,"+"  AlbumId      INT64 NOT NULL,"+"  AlbumTitle   STRING(MAX)"+") PRIMARY KEY (SingerId, AlbumId),"+"  INTERLEAVE IN PARENT Singers ON DELETE CASCADE","ALTER DATABASE `{}`"" SET OPTIONS (version_retention_period = '{}')".format(database_id,retention_period),]operation=database_admin_api.create_database(request=spanner_database_admin.CreateDatabaseRequest(parent=database_admin_api.instance_path(spanner_client.project,instance_id),create_statement="CREATE DATABASE `{}`".format(database_id),extra_statements=ddl_statements,))db=operation.result(30)print("Database{} created with version retention period{} and earliest version time{}".format(db.name,db.version_retention_period,db.earliest_version_time))database_admin_api.drop_database(spanner_database_admin.DropDatabaseRequest(database=db.name))

# project_id  = "Your Google Cloud project ID"# instance_id = "Your Spanner instance ID"# database_id = "Your Spanner database ID"require"google/cloud/spanner"require"google/cloud/spanner/admin/database"database_admin_client=Google::Cloud::Spanner::Admin::Database.database_admininstance_path=database_admin_client.instance_pathproject:project_id,instance:instance_idversion_retention_period="7d"db_path=database_admin_client.database_pathproject:project_id,instance:instance_id,database:database_idjob=database_admin_client.create_databaseparent:instance_path,create_statement:"CREATE DATABASE `#{database_id}`",extra_statements:["CREATE TABLE Singers (    SingerId     INT64 NOT NULL,    FirstName    STRING(1024),    LastName     STRING(1024),    SingerInfo   BYTES(MAX)  ) PRIMARY KEY (SingerId)","CREATE TABLE Albums (    SingerId     INT64 NOT NULL,    AlbumId      INT64 NOT NULL,    AlbumTitle   STRING(MAX)  ) PRIMARY KEY (SingerId, AlbumId),  INTERLEAVE IN PARENT Singers ON DELETE CASCADE","ALTER DATABASE `#{database_id}`    SET OPTIONS ( version_retention_period = '#{version_retention_period}' )"]puts"Waiting for create database operation to complete"job.wait_until_done!database=database_admin_client.get_databasename:db_pathputs"Created database#{database_id} on instance#{instance_id}"puts"\tVersion retention period:#{database.version_retention_period}"puts"\tEarliest version time:#{database.earliest_version_time}"

Usage notes:

• The retention period must be between 1 hour and 7 days, and can be specifiedin days, hours, minutes, or seconds. For example, the values1d,24h,1440m, and86400s are equivalent.
• If you haveenabledloggingfor the Spanner API in your project, the event is logged asUpdateDatabaseDdl and is visible in theLogsExplorer.
• To revert to the default retention period of 1 hour, you can set theversion_retention_period database option toNULL for GoogleSQLdatabases orDEFAULT for PostgreSQL databases.
• When you extend the retention period, the system doesn't backfill previousversions of data. For example, if you extend the retention period from onehour to 24 hours, then you must wait 23 hours for the system to accumulateold data before you can recover data from 24 hours in the past.

Get the retention period and earliest version time

TheDatabase resourceincludes two fields:

• version_retention_period:the period in which Spanner retains all versions of data forthe database.

• earliest_version_time:the earliest timestamp at which earlier versions of the data can be readfrom the database. This value is continuously updated bySpanner and becomes stale the moment it's queried. If youare using this value to recover data, make sure to account for the time fromthe moment when the value is queried to the moment when you initiate therecovery.

gcloudspannerdatabasesdescribeexample-db\--instance=test-instance

createTime: '2020-09-07T16:56:08.285140Z'earliestVersionTime: '2020-10-07T16:56:08.285140Z'name: projects/my-project/instances/test-instance/databases/example-dbstate: READYversionRetentionPeriod: 3d

Recover a portion of your database

1. Perform astale read andspecify the needed recovery timestamp. Make sure that the timestamp youspecify is more recent than the database'searliest_version_time.

   gcloud

   Useexecute-sqlFor example:

   gcloudspannerdatabasesexecute-sqlexample-db\--instance=test-instance--read-timestamp=2020-09-11T10:19:36.010459-07:00\--sql='SELECT * FROM SINGERS'

   Client libraries

   Seeperform stale read.

2. Store the results of the query. This is required because you can't writethe results of the query back into the database in the same transaction. Forsmall amounts of data, you can print to console or store in-memory. Forlarger amounts of data, you may need to write to a local file.

3. Write the recovered data back to the table that needs to be recovered. Forexample:

   gcloud

   gcloudspannerrowsupdate--instance=test-instance--database=example-db--table=Singers\--data=SingerId=1,FirstName='Marc'

   For more information, seeupdating data using gcloud.

   Client libraries

   For more information, seeupdating data using DML orupdating data using mutations.

   Optionally, if you want to do some analysis on the recovered data beforewriting back, you can manually create a temporay table in the same database,write the recovered data to this temporary table first, do the analysis, andthen read the data you want to recover from this temporary table and writeit to the table that needs to be recovered.

Recover an entire database

You can recover the entire database using eitherBackup and RestoreorImport and Export and specifying a recoverytimestamp.

Backup and restore

1. Create a backup and set theversion_timeto the needed recovery timestamp.

   Console

   1. Go to theDatabase details page in the Cloud console.

      Go to the Cloud console

   2. In theBackup/Restore tab, clickCreate.

   3. Check theCreate backup from an earlier point in time box.

      

   gcloud

   gcloudspannerbackupscreateexample-db-backup-1\--instance=test-instance\--database=example-db\--retention-period=1y\--version-time=2021-01-22T01:10:35Z--async

   For more information, seeCreate a backup using gcloud.

   Client libraries

   C#

   // Copyright 2020 Google Inc.//// Licensed under the Apache License, Version 2.0 (the "License");// you may not use this file except in compliance with the License.// You may obtain a copy of the License at////     http://www.apache.org/licenses/LICENSE-2.0//// Unless required by applicable law or agreed to in writing, software// distributed under the License is distributed on an "AS IS" BASIS,// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.// See the License for the specific language governing permissions and// limitations under the License.usingGoogle.Cloud.Spanner.Admin.Database.V1;usingGoogle.Cloud.Spanner.Common.V1;usingGoogle.LongRunning;usingGoogle.Protobuf.WellKnownTypes;usingSystem;publicclassCreateBackupSample{publicBackupCreateBackup(stringprojectId,stringinstanceId,stringdatabaseId,stringbackupId,DateTimeversionTime){// Create the DatabaseAdminClient instance.DatabaseAdminClientdatabaseAdminClient=DatabaseAdminClient.Create();// Initialize request parameters.Backupbackup=newBackup{DatabaseAsDatabaseName=DatabaseName.FromProjectInstanceDatabase(projectId,instanceId,databaseId),ExpireTime=DateTime.UtcNow.AddDays(14).ToTimestamp(),VersionTime=versionTime.ToTimestamp(),};InstanceNameinstanceName=InstanceName.FromProjectInstance(projectId,instanceId);// Make the CreateBackup request.Operation<Backup,CreateBackupMetadata>response=databaseAdminClient.CreateBackup(instanceName,backup,backupId);Console.WriteLine("Waiting for the operation to finish.");// Poll until the returned long-running operation is complete.Operation<Backup,CreateBackupMetadata>completedResponse=response.PollUntilCompleted();if(completedResponse.IsFaulted){Console.WriteLine($"Error while creating backup: {completedResponse.Exception}");throwcompletedResponse.Exception;}Console.WriteLine($"Backup created successfully.");// GetBackup to get more information about the created backup.BackupNamebackupName=BackupName.FromProjectInstanceBackup(projectId,instanceId,backupId);backup=databaseAdminClient.GetBackup(backupName);Console.WriteLine($"Backup {backup.Name} of size {backup.SizeBytes} bytes "+$"was created at {backup.CreateTime} from {backup.Database} "+$"and is in state {backup.State} "+$"and has version time {backup.VersionTime}");returnbackup;}}

   C++

   voidCreateBackup(google::cloud::spanner_admin::DatabaseAdminClientclient,std::stringconst&project_id,std::stringconst&instance_id,std::stringconst&database_id,std::stringconst&backup_id,google::cloud::spanner::Timestampexpire_time,google::cloud::spanner::Timestampversion_time){google::cloud::spanner::Databasedatabase(project_id,instance_id,database_id);google::spanner::admin::database::v1::CreateBackupRequestrequest;request.set_parent(database.instance().FullName());request.set_backup_id(backup_id);request.mutable_backup()->set_database(database.FullName());*request.mutable_backup()->mutable_expire_time()=expire_time.get<google::protobuf::Timestamp>().value();*request.mutable_backup()->mutable_version_time()=version_time.get<google::protobuf::Timestamp>().value();autobackup=client.CreateBackup(request).get();if(!backup)throwstd::move(backup).status();std::cout      <<"Backup " <<backup->name() <<" of " <<backup->database()      <<" of size " <<backup->size_bytes() <<" bytes as of "      <<google::cloud::spanner::MakeTimestamp(backup->version_time()).value()      <<" was created at "      <<google::cloud::spanner::MakeTimestamp(backup->create_time()).value()      <<".\n";}

   Go

   import("context""fmt""io""regexp""time"database"cloud.google.com/go/spanner/admin/database/apiv1"adminpb"cloud.google.com/go/spanner/admin/database/apiv1/databasepb"pbt"github.com/golang/protobuf/ptypes/timestamp")funccreateBackup(ctxcontext.Context,wio.Writer,db,backupIDstring,versionTimetime.Time)error{// versionTime := time.Now().AddDate(0, 0, -1) // one day agomatches:=regexp.MustCompile("^(.+)/databases/(.+)$").FindStringSubmatch(db)ifmatches==nil||len(matches)!=3{returnfmt.Errorf("createBackup: invalid database id %q",db)}adminClient,err:=database.NewDatabaseAdminClient(ctx)iferr!=nil{returnfmt.Errorf("createBackup.NewDatabaseAdminClient: %w",err)}deferadminClient.Close()expireTime:=time.Now().AddDate(0,0,14)// Create a backup.req:=adminpb.CreateBackupRequest{Parent:matches[1],BackupId:backupID,Backup:&adminpb.Backup{Database:db,ExpireTime:&pbt.Timestamp{Seconds:expireTime.Unix(),Nanos:int32(expireTime.Nanosecond())},VersionTime:&pbt.Timestamp{Seconds:versionTime.Unix(),Nanos:int32(versionTime.Nanosecond())},},}op,err:=adminClient.CreateBackup(ctx,&req)iferr!=nil{returnfmt.Errorf("createBackup.CreateBackup: %w",err)}// Wait for backup operation to complete.backup,err:=op.Wait(ctx)iferr!=nil{returnfmt.Errorf("createBackup.Wait: %w",err)}// Get the name, create time, version time and backup size.backupCreateTime:=time.Unix(backup.CreateTime.Seconds,int64(backup.CreateTime.Nanos))backupVersionTime:=time.Unix(backup.VersionTime.Seconds,int64(backup.VersionTime.Nanos))fmt.Fprintf(w,"Backup %s of size %d bytes was created at %s with version time %s\n",backup.Name,backup.SizeBytes,backupCreateTime.Format(time.RFC3339),backupVersionTime.Format(time.RFC3339))returnnil}

   Java

   staticvoidcreateBackup(DatabaseAdminClientdbAdminClient,StringprojectId,StringinstanceId,StringdatabaseId,StringbackupId,TimestampversionTime){// Set expire time to 14 days from now.TimestampexpireTime=Timestamp.newBuilder().setSeconds(TimeUnit.MILLISECONDS.toSeconds((System.currentTimeMillis()+TimeUnit.DAYS.toMillis(14)))).build();BackupNamebackupName=BackupName.of(projectId,instanceId,backupId);Backupbackup=Backup.newBuilder().setName(backupName.toString()).setDatabase(DatabaseName.of(projectId,instanceId,databaseId).toString()).setExpireTime(expireTime).setVersionTime(versionTime).build();// Initiate the request which returns an OperationFuture.System.out.println("Creating backup ["+backupId+"]...");try{// Wait for the backup operation to complete.backup=dbAdminClient.createBackupAsync(InstanceName.of(projectId,instanceId),backup,backupId).get();System.out.println("Created backup ["+backup.getName()+"]");}catch(ExecutionExceptione){throwSpannerExceptionFactory.asSpannerException(e);}catch(InterruptedExceptione){throwSpannerExceptionFactory.propagateInterrupt(e);}// Reload the metadata of the backup from the server.backup=dbAdminClient.getBackup(backup.getName());System.out.println(String.format("Backup %s of size %d bytes was created at %s for version of database at %s",backup.getName(),backup.getSizeBytes(),java.time.OffsetDateTime.ofInstant(Instant.ofEpochSecond(backup.getCreateTime().getSeconds(),backup.getCreateTime().getNanos()),ZoneId.systemDefault()),java.time.OffsetDateTime.ofInstant(Instant.ofEpochSecond(backup.getVersionTime().getSeconds(),backup.getVersionTime().getNanos()),ZoneId.systemDefault())));}

   Note: The old client library interface code samples for Java are archived inGitHub.

   Node.js

   // Imports the Google Cloud client library and precise date libraryconst{Spanner,protos}=require('@google-cloud/spanner');const{PreciseDate}=require('@google-cloud/precise-date');/** * TODO(developer): Uncomment the following lines before running the sample. */// const projectId = 'my-project-id';// const instanceId = 'my-instance';// const databaseId = 'my-database';// const backupId = 'my-backup';// const versionTime = Date.now() - 1000 * 60 * 60 * 24; // One day ago// Creates a clientconstspanner=newSpanner({projectId:projectId,});// Gets a reference to a Cloud Spanner Database Admin Client objectconstdatabaseAdminClient=spanner.getDatabaseAdminClient();// Creates a new backup of the databasetry{console.log(`Creating backup of database${databaseAdminClient.databasePath(projectId,instanceId,databaseId,)}.`,);// Expire backup 14 days in the futureconstexpireTime=Date.now()+1000*60*60*24*14;// Create a backup of the state of the database at the current time.const[operation]=awaitdatabaseAdminClient.createBackup({parent:databaseAdminClient.instancePath(projectId,instanceId),backupId:backupId,backup:(protos.google.spanner.admin.database.v1.Backup={database:databaseAdminClient.databasePath(projectId,instanceId,databaseId,),expireTime:Spanner.timestamp(expireTime).toStruct(),versionTime:Spanner.timestamp(versionTime).toStruct(),name:databaseAdminClient.backupPath(projectId,instanceId,backupId),}),});console.log(`Waiting for backup${databaseAdminClient.backupPath(projectId,instanceId,backupId,)} to complete...`,);awaitoperation.promise();// Verify backup is readyconst[backupInfo]=awaitdatabaseAdminClient.getBackup({name:databaseAdminClient.backupPath(projectId,instanceId,backupId),});if(backupInfo.state==='READY'){console.log(`Backup${backupInfo.name} of size `+`${backupInfo.sizeBytes} bytes was created at `+`${newPreciseDate(backupInfo.createTime).toISOString()} `+'for version of database at '+`${newPreciseDate(backupInfo.versionTime).toISOString()}`,);}else{console.error('ERROR: Backup is not ready.');}}catch(err){console.error('ERROR:',err);}finally{// Close the spanner client when finished.// The databaseAdminClient does not require explicit closure. The closure of the Spanner client will automatically close the databaseAdminClient.spanner.close();}

   Note: The old client library interface code samples for Node.js are archived inGitHub.

   PHP

   use Google\Cloud\Spanner\Admin\Database\V1\Backup;use Google\Cloud\Spanner\Admin\Database\V1\GetBackupRequest;use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;use Google\Cloud\Spanner\Admin\Database\V1\CreateBackupRequest;use Google\Protobuf\Timestamp;/** * Create a backup. * Example: * ``` * create_backup($projectId, $instanceId, $databaseId, $backupId, $versionTime); * ``` * * @param string $projectId The Google Cloud project ID. * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. * @param string $backupId The Spanner backup ID. * @param string $versionTime The version of the database to backup. Read more * at https://cloud.google.com/spanner/docs/reference/rest/v1/projects.instances.backups#Backup.FIELDS.version_time */function create_backup(    string $projectId,    string $instanceId,    string $databaseId,    string $backupId,    string $versionTime = '-1hour'): void {    $databaseAdminClient = new DatabaseAdminClient();    $databaseFullName = DatabaseAdminClient::databaseName($projectId, $instanceId, $databaseId);    $instanceFullName = DatabaseAdminClient::instanceName($projectId, $instanceId);    $timestamp = new Timestamp();    $timestamp->setSeconds((new \DateTime($versionTime))->getTimestamp());    $expireTime = new Timestamp();    $expireTime->setSeconds((new \DateTime('+14 days'))->getTimestamp());    $request = new CreateBackupRequest([        'parent' => $instanceFullName,        'backup_id' => $backupId,        'backup' => new Backup([            'database' => $databaseFullName,            'expire_time' => $expireTime,            'version_time' => $timestamp        ])    ]);    $operation = $databaseAdminClient->createBackup($request);    print('Waiting for operation to complete...' . PHP_EOL);    $operation->pollUntilComplete();    $request = new GetBackupRequest();    $request->setName($databaseAdminClient->backupName($projectId, $instanceId, $backupId));    $info = $databaseAdminClient->getBackup($request);    printf(        'Backup %s of size %d bytes was created at %d for version of database at %d' . PHP_EOL,        basename($info->getName()),        $info->getSizeBytes(),        $info->getCreateTime()->getSeconds(),        $info->getVersionTime()->getSeconds());}

   Note: The old client library interface code samples for PHP are archived inGitHub.

   Python

   defcreate_backup(instance_id,database_id,backup_id,version_time):"""Creates a backup for a database."""fromgoogle.cloud.spanner_admin_database_v1.typesimportbackupasbackup_pbspanner_client=spanner.Client()database_admin_api=spanner_client.database_admin_api# Create a backupexpire_time=datetime.utcnow()+timedelta(days=14)request=backup_pb.CreateBackupRequest(parent=database_admin_api.instance_path(spanner_client.project,instance_id),backup_id=backup_id,backup=backup_pb.Backup(database=database_admin_api.database_path(spanner_client.project,instance_id,database_id),expire_time=expire_time,version_time=version_time,),)operation=database_admin_api.create_backup(request)# Wait for backup operation to complete.backup=operation.result(2100)# Verify that the backup is ready.assertbackup.state==backup_pb.Backup.State.READYprint("Backup{} of size{} bytes was created at{} for version of database at{}".format(backup.name,backup.size_bytes,backup.create_time,backup.version_time))

   Note: The old client library interface code samples for Python are archived inGitHub.

   Ruby

   # project_id  = "Your Google Cloud project ID"# instance_id = "Your Spanner instance ID"# database_id = "Your Spanner database ID"# backup_id = "Your Spanner backup ID"# version_time = Time.now - 60 * 60 * 24 # 1 day agorequire"google/cloud/spanner"require"google/cloud/spanner/admin/database"database_admin_client=Google::Cloud::Spanner::Admin::Database.database_admininstance_path=database_admin_client.instance_pathproject:project_id,instance:instance_iddb_path=database_admin_client.database_pathproject:project_id,instance:instance_id,database:database_idbackup_path=database_admin_client.backup_pathproject:project_id,instance:instance_id,backup:backup_idexpire_time=Time.now+(14*24*3600)# 14 days from nowjob=database_admin_client.create_backupparent:instance_path,backup_id:backup_id,backup:{database:db_path,expire_time:expire_time,version_time:version_time}puts"Backup operation in progress"job.wait_until_done!backup=database_admin_client.get_backupname:backup_pathputs"Backup#{backup_id} of size#{backup.size_bytes} bytes was created at#{backup.create_time} for version of database at#{backup.version_time}"

2. Restore from the backup to a new database. Note that Spannerpreserves the retention period setting from the backup to the restoreddatabase.

   Console

   1. Go to theInstance details page in the Cloud console.

      Go to the Cloud console

   2. In theBackup/Restore tab, select a backup and clickRestore.

   gcloud

   gcloudspannerdatabasesrestore--async\--destination-instance=destination-instance--destination-database=example-db-restored\--source-instance=test-instance--source-backup=example-db-backup-1

   For more information, seeRestoring a database from a backup.

   Client libraries

   C#

   usingGoogle.Cloud.Spanner.Admin.Database.V1;usingGoogle.Cloud.Spanner.Common.V1;usingGoogle.LongRunning;usingSystem;publicclassRestoreDatabaseSample{publicRestoreInfoRestoreDatabase(stringprojectId,stringinstanceId,stringdatabaseId,stringbackupId){// Create the DatabaseAdminClient instance.DatabaseAdminClientdatabaseAdminClient=DatabaseAdminClient.Create();InstanceNameparentAsInstanceName=InstanceName.FromProjectInstance(projectId,instanceId);BackupNamebackupAsBackupName=BackupName.FromProjectInstanceBackup(projectId,instanceId,backupId);// Make the RestoreDatabase request.Operation<Database,RestoreDatabaseMetadata>response=databaseAdminClient.RestoreDatabase(parentAsInstanceName,databaseId,backupAsBackupName);Console.WriteLine("Waiting for the operation to finish");// Poll until the returned long-running operation is complete.varcompletedResponse=response.PollUntilCompleted();if(completedResponse.IsFaulted){Console.WriteLine($"Database Restore Failed: {completedResponse.Exception}");throwcompletedResponse.Exception;}RestoreInforestoreInfo=completedResponse.Result.RestoreInfo;Console.WriteLine($"Database {restoreInfo.BackupInfo.SourceDatabase} was restored "+$"to {databaseId} from backup {restoreInfo.BackupInfo.Backup} "+$"with version time {restoreInfo.BackupInfo.VersionTime}");returnrestoreInfo;}}

   C++

   voidRestoreDatabase(google::cloud::spanner_admin::DatabaseAdminClientclient,std::stringconst&project_id,std::stringconst&instance_id,std::stringconst&database_id,std::stringconst&backup_id){google::cloud::spanner::Databasedatabase(project_id,instance_id,database_id);google::cloud::spanner::Backupbackup(database.instance(),backup_id);autorestored_db=client.RestoreDatabase(database.instance().FullName(),database.database_id(),backup.FullName()).get();if(!restored_db)throwstd::move(restored_db).status();std::cout <<"Database";if(restored_db->restore_info().source_type()==google::spanner::admin::database::v1::BACKUP){autoconst&backup_info=restored_db->restore_info().backup_info();std::cout <<" " <<backup_info.source_database() <<" as of "              <<google::cloud::spanner::MakeTimestamp(backup_info.version_time()).value();}std::cout <<" restored to " <<restored_db->name();std::cout <<" from backup " <<backup.FullName();std::cout <<".\n";}

   Go

   import("context""fmt""io""regexp"database"cloud.google.com/go/spanner/admin/database/apiv1"adminpb"cloud.google.com/go/spanner/admin/database/apiv1/databasepb")funcrestoreBackup(ctxcontext.Context,wio.Writer,db,backupIDstring)error{adminClient,err:=database.NewDatabaseAdminClient(ctx)iferr!=nil{returnerr}deferadminClient.Close()matches:=regexp.MustCompile("^(.*)/databases/(.*)$").FindStringSubmatch(db)ifmatches==nil||len(matches)!=3{returnfmt.Errorf("Invalid database id %s",db)}instanceName:=matches[1]databaseID:=matches[2]backupName:=instanceName+"/backups/"+backupID// Start restoring backup to a new database.restoreOp,err:=adminClient.RestoreDatabase(ctx,&adminpb.RestoreDatabaseRequest{Parent:instanceName,DatabaseId:databaseID,Source:&adminpb.RestoreDatabaseRequest_Backup{Backup:backupName,},})iferr!=nil{returnerr}// Wait for restore operation to complete.dbObj,err:=restoreOp.Wait(ctx)iferr!=nil{returnerr}// Newly created database has restore information.backupInfo:=dbObj.RestoreInfo.GetBackupInfo()ifbackupInfo!=nil{fmt.Fprintf(w,"Source database %s restored from backup %s\n",backupInfo.SourceDatabase,backupInfo.Backup)}returnnil}

   Java

   staticvoidrestoreBackup(DatabaseAdminClientdbAdminClient,StringprojectId,StringinstanceId,StringbackupId,StringrestoreToDatabaseId){BackupNamebackupName=BackupName.of(projectId,instanceId,backupId);Backupbackup=dbAdminClient.getBackup(backupName);// Initiate the request which returns an OperationFuture.System.out.println(String.format("Restoring backup [%s] to database [%s]...",backup.getName(),restoreToDatabaseId));try{RestoreDatabaseRequestrequest=RestoreDatabaseRequest.newBuilder().setParent(InstanceName.of(projectId,instanceId).toString()).setDatabaseId(restoreToDatabaseId).setBackup(backupName.toString()).build();OperationFuture<com.google.spanner.admin.database.v1.Database,RestoreDatabaseMetadata>op=dbAdminClient.restoreDatabaseAsync(request);// Wait until the database has been restored.com.google.spanner.admin.database.v1.Databasedb=op.get();// Get the restore info.RestoreInforestoreInfo=db.getRestoreInfo();BackupInfobackupInfo=restoreInfo.getBackupInfo();System.out.println("Restored database ["+db.getName()+"] from ["+restoreInfo.getBackupInfo().getBackup()+"] with version time ["+backupInfo.getVersionTime()+"]");}catch(ExecutionExceptione){throwSpannerExceptionFactory.newSpannerException(e.getCause());}catch(InterruptedExceptione){throwSpannerExceptionFactory.propagateInterrupt(e);}}

   Note: The old client library interface code samples for Java are archived inGitHub.

   Node.js

   // Imports the Google Cloud client library and precise date libraryconst{Spanner}=require('@google-cloud/spanner');const{PreciseDate}=require('@google-cloud/precise-date');/** * TODO(developer): Uncomment the following lines before running the sample. */// const projectId = 'my-project-id';// const instanceId = 'my-instance';// const databaseId = 'my-database';// const backupId = 'my-backup';// Creates a clientconstspanner=newSpanner({projectId:projectId,});// Gets a reference to a Cloud Spanner Database Admin Client objectconstdatabaseAdminClient=spanner.getDatabaseAdminClient();// Restore the databaseconsole.log(`Restoring database${databaseAdminClient.databasePath(projectId,instanceId,databaseId,)} from backup${backupId}.`,);const[restoreOperation]=awaitdatabaseAdminClient.restoreDatabase({parent:databaseAdminClient.instancePath(projectId,instanceId),databaseId:databaseId,backup:databaseAdminClient.backupPath(projectId,instanceId,backupId),});// Wait for restore to completeconsole.log('Waiting for database restore to complete...');awaitrestoreOperation.promise();console.log('Database restored from backup.');const[metadata]=awaitdatabaseAdminClient.getDatabase({name:databaseAdminClient.databasePath(projectId,instanceId,databaseId),});console.log(`Database${metadata.restoreInfo.backupInfo.sourceDatabase} was restored `+`to${databaseId} from backup${metadata.restoreInfo.backupInfo.backup} `+'with version time '+`${newPreciseDate(metadata.restoreInfo.backupInfo.versionTime,).toISOString()}.`,);

   Note: The old client library interface code samples for Node.js are archived inGitHub.

   PHP

   use Google\Cloud\Spanner\Admin\Database\V1\Client\DatabaseAdminClient;use Google\Cloud\Spanner\Admin\Database\V1\RestoreDatabaseRequest;/** * Restore a database from a backup. * Example: * ``` * restore_backup($projectId, $instanceId, $databaseId, $backupId); * ``` * @param string $projectId The Google Cloud project ID. * @param string $instanceId The Spanner instance ID. * @param string $databaseId The Spanner database ID. * @param string $backupId The Spanner backup ID. */function restore_backup(    string $projectId,    string $instanceId,    string $databaseId,    string $backupId): void {    $databaseAdminClient = new DatabaseAdminClient();    $backupName = DatabaseAdminClient::backupName($projectId, $instanceId, $backupId);    $instanceName = DatabaseAdminClient::instanceName($projectId, $instanceId);    $request = new RestoreDatabaseRequest([        'parent' => $instanceName,        'database_id' => $databaseId,        'backup' => $backupName    ]);    $operationResponse = $databaseAdminClient->restoreDatabase($request);    $operationResponse->pollUntilComplete();    $database = $operationResponse->operationSucceeded() ? $operationResponse->getResult() : null;    $restoreInfo = $database->getRestoreInfo();    $backupInfo = $restoreInfo->getBackupInfo();    $sourceDatabase = $backupInfo->getSourceDatabase();    $sourceBackup = $backupInfo->getBackup();    $versionTime = $backupInfo->getVersionTime()->getSeconds();    printf(        'Database %s restored from backup %s with version time %s' . PHP_EOL,        $sourceDatabase, $sourceBackup, $versionTime    );}

   Note: The old client library interface code samples for PHP are archived inGitHub.

   Python

   defrestore_database(instance_id,new_database_id,backup_id):"""Restores a database from a backup."""fromgoogle.cloud.spanner_admin_database_v1importRestoreDatabaseRequestspanner_client=spanner.Client()database_admin_api=spanner_client.database_admin_api# Start restoring an existing backup to a new database.request=RestoreDatabaseRequest(parent=database_admin_api.instance_path(spanner_client.project,instance_id),database_id=new_database_id,backup=database_admin_api.backup_path(spanner_client.project,instance_id,backup_id),)operation=database_admin_api.restore_database(request)# Wait for restore operation to complete.db=operation.result(1600)# Newly created database has restore information.restore_info=db.restore_infoprint("Database{} restored to{} from backup{} with version time{}.".format(restore_info.backup_info.source_database,new_database_id,restore_info.backup_info.backup,restore_info.backup_info.version_time,))

   Note: The old client library interface code samples for Python are archived inGitHub.

   Ruby

   # project_id  = "Your Google Cloud project ID"# instance_id = "Your Spanner instance ID"# database_id = "Your Spanner database ID of where to restore"# backup_id = "Your Spanner backup ID"require"google/cloud/spanner"require"google/cloud/spanner/admin/database"database_admin_client=Google::Cloud::Spanner::Admin::Database.database_admininstance_path=database_admin_client.instance_pathproject:project_id,instance:instance_iddb_path=database_admin_client.database_pathproject:project_id,instance:instance_id,database:database_idbackup_path=database_admin_client.backup_pathproject:project_id,instance:instance_id,backup:backup_idjob=database_admin_client.restore_databaseparent:instance_path,database_id:database_id,backup:backup_pathputs"Waiting for restore backup operation to complete"job.wait_until_done!database=database_admin_client.get_databasename:db_pathrestore_info=database.restore_infoputs"Database#{restore_info.backup_info.source_database} was restored to#{database_id} from backup#{restore_info.backup_info.backup} with version time#{restore_info.backup_info.version_time}"

Import and export

1. Export the database, specifying thesnapshotTime parameter to the neededrecovery timestamp.

   Console

   1. Go to theInstance details page in the Cloud console.

      Go to the Cloud console

   2. In theImport/Export tab, clickExport.

   3. Check theExport database from an earlier point in time box.

      

   For detailed instructions, seeexport a database.

   gcloud

   Use theSpanner to Avro Dataflow template to export the database.

   gclouddataflowjobsrunJOB_NAME\--gcs-location='gs://cloud-spanner-point-in-time-recovery/Import Export Template/export/templates/Cloud_Spanner_to_GCS_Avro'--region=DATAFLOW_REGION\--parameters='instanceId=test-instance,databaseId=example-db,outputDir=YOUR_GCS_DIRECTORY,snapshotTime=2020-09-01T23:59:40.125245Z'

   Usage notes:

• You can track the progress of your import and export jobs in theDataflow Console.
• Spanner guarantees that the exported data is externallyand transactionally consistent at the specified timestamp.
• Specify the timestamp inRFC 3339 format.For example, 2020-09-01T23:59:30.234233Z.
• Make sure that the timestamp you specify is more recent than the database'searliest_version_time. If data no longer exists at the specifiedtimestamp, you get an error.

1. Import to a new database.

   Console

   1. Go to theInstance details page in the Cloud console.

      Go to the Cloud console

   2. In theImport/Export tab, clickImport.

   For detailed instructions, seeImporting Spanner Avro Files.

   gcloud

   Use theCloud Storage Avro to Spanner Dataflow template to import the Avro files.

   gclouddataflowjobsrunJOB_NAME\--gcs-location='gs://cloud-spanner-point-in-time-recovery/Import Export Template/import/templates/GCS_Avro_to_Cloud_Spanner'\--region=DATAFLOW_REGION\--staging-location=YOUR_GCS_STAGING_LOCATION\--parameters='instanceId=test-instance,databaseId=example-db,inputDir=YOUR_GCS_DIRECTORY'

Estimate the storage increase

Before increasing a database's version retention period, you can estimate theexpected increase in database storage utilization by totaling the transactionbytes for the needed period of time. For example the following query calculatesthe number of GiB written in the past 7 days (168h) by reading from thetransaction statisticstables.

SELECTSUM(bytes_per_hour)/(1024*1024*1024)asGiBFROM(SELECT((commit_attempt_count-commit_failed_precondition_count-commit_abort_count)*avg_bytes)ASbytes_per_hour,interval_endFROMspanner_sys.txn_stats_total_hourORDERBYinterval_endDESCLIMIT168);

SELECTbph/(1024*1024*1024)asGiBFROM(SELECTSUM(bytes_per_hour)asbphFROM(SELECT((commit_attempt_count-commit_failed_precondition_count-commit_abort_count)*avg_bytes)ASbytes_per_hour,interval_endFROMspanner_sys.txn_stats_total_hourORDERBYinterval_endDESCLIMIT168)sub1)sub2;

Note that the query provides a rough estimate and can be inaccurate for a fewreasons:

• The query doesn't account for thetimestampthat must be stored for each version of old data. If your database consistsof many smalldata types, thequery may underestimate the storage increase.
• The query includes all write operations, but only update operations createprevious versions of data. If your workload includes a lot of insertoperations, the query may overestimate the storage increase.