Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Creating new database from a backup of another Database on the same server?

Tags:

sql

database

sql-server-2008

I am trying to create a new database from an old backup of database on the same server. When using SQL server management studio and trying to restore to the new DB from the backup I get this error

System.Data.SqlClient.SqlError: The backup set holds a backup of a database  other than the existing 'test' database. (Microsoft.SqlServer.Smo)

after googling around I found this piece of code

    RESTORE DATABASE myDB   FROM DISK = 'C:\myDB.bak'   WITH MOVE 'myDB_Data' TO 'C:\DATA\myDB.mdf',  MOVE 'myDB_Log' TO 'C:\DATA\myDB_log.mdf' GO

I was wondering will the move statements mess with the database that the backup came from on that server?

Thanks, all help appreciated.

like image 474
Flywheel Avatar asked Apr 24 '12 14:04

Flywheel

People also ask

How do I clone a database on the same server?

On either the source or destination SQL Server instance, launch the Copy Database Wizard in SQL Server Management Studio from Object Explorer and expand Databases. Then right-click a database, point to Tasks, and then select Copy Database.

How to create a database from backup on SQL Server?

Create Database from Backup on SQL Server 1 Restore Database... . 2 General . 3 Select backup devices . Keep default option File for Backup Media Type unchanged. Point to backup file on File Explorer... More ...

How do I backup multiple databases at the same time?

In Step 1, click Add Computers to detect all the controlled computers with SQL instances, select the correct IP and click OK. In Step 2, click Add and you can select multiple databases you want to backup. In Step 3, click Add Storage to add a Share or NAS Device as a storage end, select a path.

How to backup and restore the database backup of any version?

Note: You can choose to backup and restore the database backup of any SQL Server version. Step 1: Open SSMS and connect to another SQL Server instance from where you want to restore the backed up database copy of the old server. Step 2: Create a database with the same name that you want to restore. In our example, we have created a ‘Test’ database.

Is it possible to migrate database from one server to another?

And yes, you can do this through several methods - including but not limited to SSMS backup and restore For large volumes of data, the most secure way is to backup SQL database and restore to another server. It is less error prone than other methods, but also requires a downtime when migrating database.

1 Answers

What I should to do:

  • Click on 'Restore Database ...' float menu that appears right clicking the "Databases" node on SQL Server Management Studio.
  • Fill wizard with the database to restore and the new name.
  • Important If database still exists change the "Restore As" file names in the "Files" tab to avoid "files already in use, cannot overwrite" error message.

What I do

IDk why I prefer to do this:

  • I create a blank target database with my favorite params.
  • Then, in "SQL Server Management Studio" restore wizard, I look for the option to overwrite target database. It is in the 'Options' tab and is called 'Overwrite the existing database (WITH REPLACE)'. Check it.
  • Remember to select target files in 'Files' page.

You can change 'tabs' at left side of the wizard (General, Files, Options)

like image 88
dani herrera Avatar answered Oct 04 '22 00:10

dani herrera
Sign in to Comment

Related questions

Move SQL data from one table to another
Query to get only numbers from a string
Difference between drop table and truncate table?
How do you check if IDENTITY_INSERT is set to ON or OFF in SQL Server?
Can I parameterize the table name in a prepared statement?
What does the "@" symbol do in SQL?
How to do IF NOT EXISTS in SQLite
A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)
NULL in MySQL (Performance & Storage)
Discard millisecond part from timestamp
how to extract only the year from the date in sql server 2008?
Perform insert for each row taken from a select?
Count distinct values
How can I convert an MDB (Access) file to MySQL (or plain SQL file)?
Column calculated from another column?
Aggregate function in SQL WHERE-Clause
Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column
MySQL - Selecting data from multiple tables all with same structure but different data
SQL NOT IN not working
What is "Advanced" SQL?

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!