Is there any way to replace sql server file_guid with new GUID?

Posted on

Question :

I have two databases with same file_guid. Getting following error when trying to restore second database in Amazon RDS.

Aborted the task because of a task failure or a concurrent RESTORE_DB
request. Task ID 8 (RESTORE_DB) exception: Database DATABASENAME
cannot be restored because there is already an existing database with
the same file_guids on the instance.

This is because I already restored another database with same file_guid but different physical_name), which is an Amazon RDS limitation. If interested find details in here and here.

Query result on sys.database_files

How can I update sys.database_files record?

Answer :

We had to do the same – as we wanted to copy a database to a new database, changing the name and file IDs at the same time.

We found the original process posted here to not to be repeatable, I guess it depends on how the original file ids were created. In any event, I have updated the following procedure, which is repeatable.

  1. Backup database from RDS to S3 using standard RDS database backup.

    exec msdb.dbo.rds_backup_database
    @source_db_name = '<database-name>',
    @s3_arn_to_backup_to = 'arn:aws:s3:::<bucket-name>/<backup-filename>'
    
  2. Restore .bak file into SQL Server on a local machine. We have MSDN account, so we just downloaded SQL 2016 and installed, giving us full access to SQL on a local machine. But, I believe you can download SQL Express and do the same, some functionality may be lost depending on the source version of the SQL Engine.

  3. Perform a Export Data Tier Application for the database. This will create a .bacpac file which includes the schema, data and code (stored procedures, etc.).

  4. Delete the original data imported into SQL Server in step 3.

  5. Perform an Import Data Tier Application and point to the .bacpac file created in step 3. This step will set the file guids = _Data and _Logs, making them unique.

  6. Do a full backup to get a new .bak file.

  7. Upload new .bak file to S3 and do standard RDS database import.

    exec msdb.dbo.rds_restore_database
    @restore_db_name = '<database-name>',
    @s3_arn_to_restore_from = 'arn:aws:s3:::<bucket-name>/<backup-filename>'
    

Finally I have ended up with creating new RDS instance and restore there. There are others work around for small database are 1. Creating .bacpac and import into new database, then take backup (.bak). See here. 2. Generate script with both schema and data and run on new database.

.bacpac or generating script ways did not work for me because of bigger database size ( over 10 GB), RDS is cost effective but a pain for DBA.

This no longer appears to be an issue. I have restored a database to the same server with a different name. It shares the same file_guid however and both databases are online without issue. You can use the following query to quickly see the files and locations of the databases on your rds server.

SELECT DB_NAME(database_id) AS DatabaseName, name AS LogicalFileName, physical_name AS PhysicalFileName, file_guid AS File_Guid
FROM sys.master_files

Leave a Reply

Your email address will not be published. Required fields are marked *