Creating New Database from a Backup of Another Database on the Same Server

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

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)

Create a new database from a restore file and change primary database file location

If you want to "move" your DB to another server first you need to DETACH your DB.
Instruction is :

• Open SSMS
• Choose your db which you want to move.
• Right Click --> Tasks --> Detach
• Detach Database pop-up will be shown.
• Before you detach your database you need to sure no one connected to database. Check the "Message" column. It will show you if there are connections. If there are connections but you still want to Detach your database you can click "Drop Connections" check box.
• Press Ok

Now you can move your database files (MDF,LDF,FDF) to another location.

To ATTACH your database to new server :

• Connect to new server using SSMS
• Right Click on Databases and Click Attach
• Press Add button in middle right.
• Choose the MDF (Main DB File) file which you moved and press ok.
• Press ok.

If you want to restore database backup to new server as NEW database :

• Connect to new server using SSMS
• Right Click on Databases and Click Restore Database
• Click Device on top right under the Source and add your backup file
• If you want to change your db name you can update it on Destination --> Database section.
• Go to Files tab on top left
• You can see file locations there. if you want to change the location simply you can update it on "Restore As" column.
• Press Ok.

or use following script :

USE [master]
RESTORE DATABASE [A]
FROM  DISK = N'<yourbackuplocation\A_BKP.bak>' 
WITH  FILE = 1,  
MOVE N'A_Main' TO N'<new location for MAIN database file\<file name>.mdf>',  
MOVE N'A_Log' TO N'<new location for LOG database file\<file name>.ldf>',  
NOUNLOAD,  
STATS = 5
GO

If you want to restore database backup to new server to EXISTING database :

ALTER DATABASE [A] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [A] 
    FROM  DISK = '<yourbackuplocation\A_BKP.bak>' WITH  FILE = 1,  
    MOVE N'A_Main' TO N'<new location for MAIN database file\<file name>.mdf>',  
    MOVE N'A_Log' TO N'<new location for LOG database file\<file name>.ldf>',  
    NOUNLOAD,  
    STATS = 5
ALTER DATABASE [A] SET MULTI_USER

How can I clone an SQL Server database on the same server in SQL Server 2008 Express?

It turns out that I had attempted to restore from a backup incorrectly.

Initially I created a new database and then attempted to restore the backup here.
What I should have done, and what worked in the end, was to bring up the restore dialog and type the name of the new database in the destination field.

So, in short, restoring from a backup did the trick.

Thanks for all the feedback and suggestions guys

How can I create a new database from a backup of another database, keeping the original in tact?

when you restore the database you have the possibility to write a name, you are not forced to restore on an existing database. You can change the name of the file too....

If you have special rules, you can still create an empty database then restore your database on that empty one.

How to restore to a different database in SQL Server?

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

View the contents of the backup file:

RESTORE FILELISTONLY FROM DISK='c:\your.bak'

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

RESTORE DATABASE MyTempCopy FROM DISK='c:\your.bak'
WITH 
   MOVE 'LogicalNameForTheMDF' TO 'c:\MyTempCopy.mdf',
   MOVE 'LogicalNameForTheLDF' TO 'c:\MyTempCopy_log.ldf'

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'):

RESTORE FILELISTONLY FROM DISK='e:\mssql\backup\creditline.bak'

>LogicalName
>--------------
>CreditLine
>CreditLine_log

RESTORE DATABASE MyTempCopy FROM DISK='e:\mssql\backup\creditline.bak'
WITH 
   MOVE 'CreditLine' TO 'e:\mssql\MyTempCopy.mdf',
   MOVE 'CreditLine_log' TO 'e:\mssql\MyTempCopy_log.ldf'

>RESTORE DATABASE successfully processed 186 pages in 0.010 seconds (144.970 MB/sec).

How to create copy of database using backup and restore

When you restore a database from a backup it will use the same file names as the original database.
You need to change these file names during the restore.

On the restore window go to the Files tab. On this window you have a column called Restore As.
Change the file names at the end of the path in the column Restore As for each of the files you see.

Create database from backup file

The backup was made on a server running SQL Server 2008 R2 Service Pack 1 (10.50.2550), but you are trying to restore it to an instance running SQL Server 2008 Service Pack 3 (10.00.55xx).

Upgrade your local instance to be SQL Server 2008 R2 and everything should work.

Source: http://sqlserverbuilds.blogspot.com/

---

Related Topics