patternsqlMinor
Is there any way to replace sql server file_guid with new GUID?
Viewed 0 times
guidfile_guidnewsqlwithanyreplacewayserverthere
Problem
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.
How can I update sys.database_files record?
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.
How can I update sys.database_files record?
Solution
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.
-
Backup database from RDS to S3 using standard RDS database backup.
-
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.
-
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.).
-
Delete the original data imported into SQL Server in step 3.
-
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.
-
Do a full backup to get a new .bak file.
-
Upload new .bak file to S3 and do standard RDS database import.
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.
-
Backup database from RDS to S3 using standard RDS database backup.
exec msdb.dbo.rds_backup_database
@source_db_name = '',
@s3_arn_to_backup_to = 'arn:aws:s3:::/'-
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.
-
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.).
-
Delete the original data imported into SQL Server in step 3.
-
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.
-
Do a full backup to get a new .bak file.
-
Upload new .bak file to S3 and do standard RDS database import.
exec msdb.dbo.rds_restore_database
@restore_db_name = '',
@s3_arn_to_restore_from = 'arn:aws:s3:::/'Code Snippets
exec msdb.dbo.rds_backup_database
@source_db_name = '<database-name>',
@s3_arn_to_backup_to = 'arn:aws:s3:::<bucket-name>/<backup-filename>'exec msdb.dbo.rds_restore_database
@restore_db_name = '<database-name>',
@s3_arn_to_restore_from = 'arn:aws:s3:::<bucket-name>/<backup-filename>'Context
StackExchange Database Administrators Q#158959, answer score: 7
Revisions (0)
No revisions yet.