Copy an Azure SQL Database
You can use the Azure SQL Database automated backups to create a copy of your SQL database. The copy operation copies the tail of the transaction log and then uses the full, differential, and transaction log backups that are part of the automated backups to create that is transactionally consistent with the source database as of the time of the final transaction log backup.
You can create the database copy on either the same server or a different server. The service tier and performance level (pricing tier) of the database copy are the same as the source database. After the copy is complete, the copy becomes a fully functional, independent database. The logins, users, and permissions can be managed independently.
When you copy a database to the same logical server, the same logins can be used on both databases. The security principal you use to copy the database becomes the database owner (DBO) on the new database. All database users, their permissions, and their security identifiers (SIDs) are copied to the database copy.
When you copy a database to a different logical server, the security principal on the new server becomes the database owner on the new database. Database users that are contained users can be used in the copied database. However, when you copy the database to a new server, users based on logins will generally not work because the logins will not exist on the new server, and if they do their SIDs may not match. After the new database is online on the destination server, use the ALTER USER statement to remap the users from the new database to logins on the destination server. To resolve orphaned users, see Troubleshoot Orphaned Users.
To copy a SQL database you need the following:
- An Azure subscription. If you need an Azure subscription simply click FREE TRIAL at the top of this page, and then come back to finish this article.
- A SQL database to copy. If you do not have a SQL database, create one following the steps in this article: Create your first Azure SQL Database.
- See Copy an Azure SQL database using the Azure Portal to copy a database using the Azure portal.
- See Copy an Azure SQL database using PowerShell to copy a database using PowerShell.
- See Copy an Azure SQL database using T-SQL to copy a database using Transact-SQL.
- See How to manage Azure SQL database security after disaster recovery to learn about managing users and logins when copying a database to a different logical server.