Restore SQL DB under different name(s).
This process can be used to restore one or more databases from an existing SQL backup file. This is an efficient way to make copies of a database under separate names for testing purposes.
Prerequisites and Recommendations
To restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. Without the certificate or asymmetric key, the database cannot be restored. As a result, the certificate that is used to encrypt the database encryption key must be retained as long as the backup is needed. For more information, see SQL Server Certificates and Asymmetric Keys.
For security purposes, we recommend that you do not attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.
Syntax for restoring the database:
RESTORE DATABASE
K7_Target FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\
K7_Source.bak'
WITH
MOVE '
K7_Source' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\
K7_Target.mdf',
MOVE '
K7_Source_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\
K7_Target_log.ldf'
K7_Source is the original backup file,
K7_Target is the
new name.
Syntax explained:
RESTORE DATABASE K7_Target FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\K7_Source.bak'
This line specifies that the K7_Target database should be created from the K7_Source backup file.
WITH
MOVE 'K7_Source' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\K7_Target.mdf',
MOVE 'K7_Source_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\K7_Target_log.ldf'
The remaining lines of code are renaming the K7_Source .mdf and .ldf files to the new database name, K7_Target. If you simply restore then rename a database, the .mdf and .ldf files do not change and as a result, two databases would be using the same files.
Possible issues:
Invalid source location (path to .bak file)
Insufficient permissions in the source location.
Insufficient SQL permissions.
Incorrect database, mdf or ldf file names.
See also:
Restore a database backup
Kentico CMS Database Reference
Database tables
Applies to: Kentico CMS 7.x