Effect on renaming the database, log and datafiles

Question

Restore database in restoring status:

USE [master]
RESTORE DATABASE test_PROD
FROM DISK = N'I:\Backup\test_QA.bak'
WITH FILE = 1,
MOVE N'test_QA' TO N'G:\Data\test_Prod.mdf',
MOVE N'test_QA_Log' TO N'H:\TLog\test_PROD_log.ldf',
----MOVE N'FileStreamDocuments2008R2' TO N'C:\MyNewFileStreamLocation\Documents2008R2',
NORECOVERY,
NOUNLOAD, REPLACE, STATS = 1
GO

RESTORE DATABASE test_PROD
FROM DISK = N'I:\Data\test\test_backup_2022_07_26_094534_2116657.bak'
WITH FILE = 1,
NORECOVERY;

Restore Transactional Log:

RESTORE LOG test_PROD
FROM DISK = N'I:\Data\test\test_backup_2022_07_26_094902_2879321.trn'
WITH NORECOVERY

After restoring the databases with new name, data and logfile will the stored procedure run into any issue with Schema?

Answer 1

If you change the names of the data files, that should not affect any application code including stored procedures. (But someone who is too smart for him- or herself might have written some code that breaks. Very unlikely, but I will not guarantee.)

Changing the database name... It is almost the same, but if there is cross-database access, or some people have written queries with three-part notation with hard-coded database names, they will still refer to the original database. I would say that this is not best practice, but it is a small risk that is not entirely ignorable.

If the database name is distinctive enough you can run this query to rule out this risk:

   SELECT object_name(object_id) FROM sys.sql_modules  
   WHERE definition LIKE '%dbname%'  

Answer 2

Hi @Avyayah ,

Suggest you using with RECOVERY (the default) option to restore transaction log. The database is in restoring state when you restore database or log with NO Recovery option.

Quote from MS document;

• NORECOVERY specifies that rollback doesn't occur. This allows rollforward to continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them forward.

• RECOVERY (the default) indicates that rollback should be performed after rollforward is completed for the current backup. No further backups can be restored. Select this option once you have restored all of the necessary backups.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Useful answer