Ms sql как изменить restore as

Restore SQL Server database and overwrite existing database

The RESTORE . WITH REPLACE option allows you to overwrite an existing database when doing a restore. In some cases when you try to do a restore you may get an error that says «The tail of the log for the database .. has not been backed up».

Explanation

The RESTORE . WITH REPLACE allows you to write over an existing database when doing a restore without first backing up the tail of the transaction log. The WITH REPLACE basically tells SQL Server to just throw out any active contents in the transaction log and move forward with the restore.

If you try to restore using T-SQL commands you will get this error message:

If you try to restore using SQL Server Management Studio you will see this error message:

Restore full backup using WITH REPLACE
The command below will restore the database and disregard any active data in the current transaction log.

SQL Server Management Studio

To restore using SSMS do the following, on the options page for the restore select «Overwrite the existing database».

Restore a database with a different name on the same server

I need a copy of a live database called Carers on the same server for testing purposes. The test database name will be Carers_Test .

I took a backup of Carers using SSMS. And tried to restore it using the GUI. When I followed the steps, the only thing I changed was the name of the database.

But the restore fails with the error:

Exclusive access could not be obtained because the database is in use.

I do not want to do anything with the current live database, why is that matter that the database is in use? I have tried to restore the same backup on my local host and it restored successfully. When I try to restore it on the same server with the live database then I get this error.

Please someone help me. I’m going crazy!

5 Answers 5

I think it also depends on which version of Management Studio you’re using. In 2016+ when you change the name of the Destination Database, SSMS changes the name of the Restore As files (see the screenshots). I seem to remember that in older versions of SSMS you had to manually change the names of the files, or else they would conflict with the existing files.

Also, be very careful of the Options «Overwrite the existing database» and «Take a tail-log backup before restore». Both of those should be Unchecked!

Lastly, if I were you, I would take a backup of the production database immediately before trying a restore of that production database.

Here I’ve changed the name of the new database:

Here the names of the new files are changed by SSMS. You might need to manually change these. In any case, check what SSMS is trying to do:

Here the two important Options are Unchecked:

It’s really simple to solve. You are trying to override the original files you need to you use the move option like this:

By the way to discover the file name from backup use this command:

It has been suggested that the reason for your error is because of the database files (use the same files as your current/existing database). That is not the reason for your error. If that were the reson, you would have seen a different error message. Below is from trying to restore a database named TSQL into the name TSQLTest, using the same file names as the existing TSQL database:

The file ‘C:\DemoDatabases\DbFiles\a\TSQL.mdf’ cannot be overwritten. It is being used by database ‘TSQL’.

SSMS is pretty good at changing the database files names for you when you type something for the name of the database to be restored. Old SSMS did this in a pretty stupid way, and then came one version which didn’t change the names for you, and then in the next version came the «intelligent» file changing that we see today. I don’t recall the version history, as for when those changes were made, but it was a while ago.

The reason for your error is that the restore GUI will by default try to do a tail log backup using the NORECOVERY option. That will fail if there are users in that database.

How to Restore Same Database with Different Name on SQL Server?

• 

Written By Charanjeet Kaur

Approved By Raja Jegan R

Updated on May 18, 2023

Min Reading 5 Min

Summary: This article discusses instances of restoring SQL database with a different name. We have also explained some methods to “restore the same database with a different name on SQL Server.” Also, we will talk about using a SQL database repair tool if issues such as database corruption or database restore failure occur.

Often, SQL users find themselves in situations that call for restoring SQL database with a different name on the same PC or server. Let’s take a look at a few user instances:

User Instance 1: “I have a backup of Database1 from a week ago. The backup is done weekly in the scheduler, and I get a .bak file. Now I want to work on some data, so I need to restore it to a different database — Database2.”

User Instance 2: “I am using SQL Server 2014. I have a database, but as the user deleted some records, I need to create another database with a different name and then transfer the data from the restored database to the current one.”

Methods to Restore Same Database with Different Name in SQL Server

Here, we will discuss two methods on how to restore a SQL Server database with a different name from a backup (.bak) file:

Tip: Make sure that the database backup file that you want to restore is updated, as running obsolete backup can result in data loss. If the backup file is not up to date, use Stellar Repair for MS SQL software to restore SQL database to a new db.

Method 1 – Using SQL Server Management Studio (SSMS)

In this method, we will share an example showing how to restore the database backup to a different name in SQL Server using SSMS. In the following example, we will restore backup of ‘A2Z’ db and save it to the same database with a different name ‘A2Z_2’.

Step 1: Open SSMS and connect to the SQL Server instance.

Step 2: Under Object Explorer, right-click Databases, and then select the Restore Database option.

Step 3: In the Restore Database window, select the Device option under Source, and then click the Browse button.

Step 4: When the Select backup devices window is displayed, click the Add button.

Step 5: Locate and select the SQL database backup file that you want to restore, and then click OK.

Step 6: Once the backup file is selected, click OK.

Step 7: Next, change the destination database name to the database name to which you want to restore the backup file. For example, we have changed the existing database name ‘A2Z’ to ‘A2Z_2’.

Step 8: On the Files page, change names of the existing database files (.mdf and .ldf) name. In our case, we have changed ‘A2Z_Backup.mdf’ and ‘A2Z_log.ldf’ file names to ‘A2Z_2_Backup.mdf’ and ‘A2Z_2_log.ldf’.

Step 9: Next, change the directory name to any folder on SQL Server where you want to save the restored database.

Note: Avoid adding database files in the same directory as the existing database to avoid collisions. Change the directory name as desired. But make sure that you are going to a drive that has sufficient space.

Step 10: Once you have changed the directory name, click the Options tab under Select a page.

Step 11: On the Options page, check the ‘Overwrite the existing database (WITH REPLACE)’ checkbox under Restore Options.

Step 12: Click OK when the ‘database restored successfully’ message box pops-up. Click the OK button again.

Step 13: The same database with a different name will be added under Databases.

Method 2 – Using Transact SQL (T-SQL)

Use T-SQL to restore SQL database with different name by following these steps:

Step 1: Determine the logical file names of the database, from the backup file, along with their physical paths by executing the RESTORE FILELISTONLY command:

RESTORE FILELISTONLY FROM DISK = ‘C:\A2Z_Delete_Me.bak’

Step 2: Once you have obtained the logical and physical names of the database files, perform SQL restore to different database using RESTORE command with MOVE option. To demonstrate this, let’s take an example. In the example, we will restore data from A2Z database backup file to A2Z_2 database along with .mdf and .ndf files.

RESTORE DATABASE A2Z_2 FROM DISK = ‘C:\A2Z_Delete_Me.bak’

WITH REPLACE, RECOVERY,

MOVE N’A2Z_data’ TO ‘c:\. \A2Z_2.mdf’,

MOVE N’A2Z_log’ TO ‘c:\. \A2Z_2.ldf’;

Problem Associated with Restoring Database using SSMS and T-SQL

Both the above-discussed methods to restore database with different name in SQL Server are efficient. However, you can face the following issues when using these methods:

• Not having SQL permissions to run the T-SQL command
• Insufficient disk space to restore the database
• Database inconsistency errors

Besides these issues, incorrect implementation of a single step to restore the database can corrupt the database and result in potential data loss. Also, you may encounter ‘SQL Database Restore Failed, Database in Use’ error message.

You can, however, overcome such issues by using the Stellar Repair for MS SQL software. The software can repair a corrupted SQL database (MDF/LDF) files and restore the database to a ‘New’ or ‘Live’ database. Also, it helps to maintain data integrity and precision when performing the restore operation.

Conclusion

This article serves as a helpful guide on ‘how to restore same database with different name on SQL server’. It discusses step-wise instructions to restore a database with a different name from the backup file, by using SSMS and T-SQL commands to perform the restoration. Be wary though, a wrong step can turn the database corrupt and render the data inaccessible. Also, restoring the db from a good known recent backup is crucial to avoid data loss. If the database is corrupted or the backup file is not available, using an SQL database repair software such as Stellar Repair for MS SQL can help.

How to restore to a different database in SQL Server?

I have a backup of Database1 from a week ago. The backup is done weekly in the scheduler and I get a .bak file. Now I want to fiddle with some data so I need to restore it to a different database — Database2.

I have seen this question: Restore SQL Server database in same pc with different name and the recommended step is to rename the original db, but I am out of that option as I am in the production server and I cant really do it.

Is there any other way of restoring it to Database2 , or atleast, how do I browse through the data of that .bak file?

ps: the second answer from the above link looked promising but it keeps terminating with error:

Restore Filelist is terminating abnormally

10 Answers 10

You can create a new db then use the "Restore Wizard" enabling the Overwrite option or:

View the contents of the backup file:

note the logical names of the .mdf & .ldf from the results, then:

This will create the database MyTempCopy with the contents of your.bak .

(Don’t create the MyTempCopy, it’s created during the restore)

Example (restores a backup of a db called ‘creditline’ to ‘MyTempCopy’):

SQL Server 2008 R2:

For an existing database that you wish to «restore: from a backup of a different database follow these steps:

1. From the toolbar, click the Activity Monitor button.
2. Click processes. Filter by the database you want to restore. Kill all running processes by right clicking on each process and selecting «kill process».
3. Right click on the database you wish to restore, and select Tasks—>Restore—>From Database.
4. Select the «From Device:» radio button.
5. Select . and choose the backup file of the other database you wish to restore from.
6. Select the backup set you wish to restore from by selecting the check box to the left of the backup set.
7. Select «Options».
8. Select Overwrite the existing database (WITH REPLACE)
9. Important: Change the «Restore As» Rows Data file name to the file name of the existing database you wish to overwrite or just give it a new name.
10. Do the same with the log file file name.
11. Verify from the Activity Monitor Screen that no new processes were spawned. If they were, kill them.
12. Click OK.

For SQL Server 2012, using Sql Server Management Studio, I found these steps from the Microsoft page useful to restore to a different database file and name: (ref: http://technet.microsoft.com/en-us/library/ms175510.aspx)

Note steps 4 and 7 are important to set so as not to overwrite the existing database.