Export and import for Azure SQL database with a sequence

A few scenarios where sequencing is useful:

Generating Unique Identifiers:

1. When you need a unique sequential number for each new row (e.g., order numbers, invoice numbers).
2. Unlike identity columns, sequences can be shared across multiple tables.

Custom Auto-Increment Logic:

1. When auto-increment needs to be independent of a specific table. 
2. When multiple tables need to use the same numbering sequence.

Permissions required:

• Requires CREATE SEQUENCE, ALTER, or CONTROL permission on the SCHEMA. 
• Members of the db_owner and db_ddladmin fixed database roles can create, alter, and drop sequence objects.
• Members of the db_owner and db_datawriter fixed database roles can update sequence objects by causing them to generate numbers.

Steps to Export and Import an Azure SQL Database with a Sequence

The following steps create a table and a sequence, export that database to a bacpac file and then import the bacpac file with sequence continuation:

Step 1: Run this T-SQL to create table [Test.Orders] and a sequence that increases by 1:

CREATE TABLE Test.Orders
(
    OrderID INT PRIMARY KEY,
    Name VARCHAR (20) NOT NULL,
    Qty INT NOT NULL
);
GO
CREATE SEQUENCE Test.CountBy1
    START WITH 1
    INCREMENT BY 1;
GO

Step 2: Run these T-SQL commands to insert some data into table [Test.Orders]:

INSERT Test.Orders (OrderID, Name, Qty) VALUES ( NEXT VALUE FOR Test.CountBy1, Tire, 2); 
INSERT Test.Orders (OrderID, Name, Qty) VALUES ( NEXT VALUE FOR Test.CountBy1, Seat, 1); 
INSERT Test.Orders (OrderID, Name, Qty) VALUES ( NEXT VALUE FOR Test.CountBy1, Brake, 1); 
INSERT Test.Orders (OrderID, Name, Qty) VALUES ( NEXT VALUE FOR Test.CountBy1, Rim, 2);

Validate the data in the table by running T-SQL command:

SELECT * FROM Test.Orders; 
GO 

Screenshot of SSMS showing inserted rows in the Test.Orders table with sequence-generated OrderID values.

Step 3: Export the database to a .bacpac file Using Sqlpackage.  

Run the following command to export the database to a .bacpac file:

SqlPackage /Action:Export /TargetFile:"C:\filename.bacpac" /SourceConnectionString:"Server=tcp:server_name.database.windows.net,1433;Initial Catalog=database_name;Persist Security Info=False;User ID=user_id;Password=xxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;

Step 4: Import the database from the .bacpac File Using Sqlpackage.

Run the following command to import the database from the .bacpac file:

SqlPackage /Action:Import /SourceFile:"C:\filename.bacpac" /TargetConnectionString:"Server=tcp:server_name.database.windows.net,1433;Initial Catalog=database_name;Persist Security Info=False;User ID=sql ;Password=xxxxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Step 5: Validate the imported sequence Once the import is completed, on the target database, run this T-SQL command to validate the sequence:

SELECT * FROM Test.Orders; 
GO

Screenshot of sys.sequences query results displaying current_value and increment settings for Test.CountBy1.

Step 6: Perform an insert of data and then view the properties of the sequence

Run the following T-SQL to insert additional data:

INSERT test.Orders (OrderID, Name, Qty) VALUES ( NEXT VALUE FOR Test.CountBy1, 'chain', 1); 
INSERT test.Orders (OrderID, Name, Qty) VALUES ( NEXT VALUE FOR Test.CountBy1, 'wire', 1); 
GO

Execute the following T-SQL statement to view the properties of the sequence:

SELECT * FROM sys.sequences

Expected Behavior

After importing the database, the sequence continues from its last value instead of restarting from the beginning.

Additional information

If cycling is enabled, the sequence restarts when it reaches its maximum or minimum value.

Example of a cycling sequence T-SQL statement:

CREATE SEQUENCE dbo.MySequence 
START WITH 1 
INCREMENT BY 1 
MINVALUE 1 
MAXVALUE 100 
CYCLE;

Reference Article:

CREATE SEQUENCE (Transact-SQL) - SQL Server | Microsoft Learn 

Sequence Numbers - SQL Server | Microsoft Learn