Home / server / Questions / 84353
Accepted
laurens
Asked: 2009-11-14 02:41:09 +0800 CST

Cloning SQL Database via Backup-Restore: 'Restoring...' takes a looong time

  • 772

I needed an exact copy of a database under another name. I choose for cloning SQL Database via Backup-Restore, using SQL Mgt Studio 2008. Firstly, when restoring the database; I received an error.

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

Then I found out I had to choose for the option "Overwrite the existing database" Seen at various sites, for example here: http://www.nikhedonia.com/notebook/entry/solving-the-sql-server-restoring-database-error/

Now next to my database name I see "(restoring...)" and it's already for a half an hour. I can't even view the properties of the database, most options say "unavailable".

Can someone explain? Or is this normal?(the database is like 20MB..) Thanks in advance!

sql-server

3 Answers

  1. Best Answer

    What you want to do is delete all that and follow these steps:

    1. Create a full backup of your database, make sure you check “verify after backup” to make sure the BAK is ok. Name it ORIGINAL.BAK. Remember its location. (you can also use “T-SQL”)

      BACKUP DATABASE ‘YourDBName’ TO DISK = ‘C:\ORIGINAL.BAK’; // or similar

    2. Now back to Management Studio, right click databases, and click “Restore Database…”

    3. In the emerging dialog, Destination for restore: To Database: MyNewDatabase

    4. In the Source for Restore, select “From Device” and click the […] button.

    5. A new popup (what’s with popups Microsoft?), Leave Backup media as “File” but click the Add button and find your ORIGINAL.BAK. If you backed up using Management Studio, you’ll probably be in the same location. Select it and click OK. Then OK again.

    6. Now back at the 1st Restore screen, Click on the Checkbox (Restore) from the box that reads: “Select the backups sets to restore” (you should have your DB listed there). Don’t click OK yet!

    7. Now on the left of this popup, there’s a listbox that says: Select a page. And two “pages”: General and Options. Click on Options.

    8. Click Overwrite the existing database.

    9. On the “Restore the database files as”, make sure that the .mdf and the .ldf are not in conflict with others. You can change its location if you wish.

    10. Click ok. You database 20mb should be restored in less than 10 seconds.

    What if that doesn’t work?

    On rare occasions you may run into problems. That is usually not a good sign and you should run a DBCC CHECKDB (read the help files for more info about that) to make sure that your master database is ok.

    You can also try to “Attach” the DB directly:

    1. Pause the SQL Server (to make sure nobody uses the DB, dunno if necessary but…)
    2. Right click Databases (in Management STudio), and select Attach, then Add.
    3. Find the .MDF of the Database you want to duplicate, click OK and now on the “database details” (below the add button), check and change the Current File Path and/or rename the .MDF and .LDF (it can’t be the same as the other DB).
    4. Click OK.

    note: if that won’t work, you might want to DEATACH the original DB first. Do the 4 steps and then RENAME the database. Then proceed to reatach the ORIGINAL DB.

    Hope this helps.

    • 2

  2. Your problem is that you need to specify a new file location for all the data files when restoring with a new database name. If you do this in T-SQL it's called WITH MOVE. It does not make sense that you can restore a database with a different name to the same location. You'll need to specify a new location/name for the files.

    it seems that your database may have crashed half way through a restore. Have a look at activity monitor to see if there are any RESTORES occuring (or sysprocesses) there probably isn't. If this is the case, then you should be able to delete (DROP) the database and start again.

    • 2

  3. Another option is you could snapshot the database. This would be instant but only provides a static read-only copy of the database (usually used for reporting).

    Only available with the Enterprise version.

    http://msdn.microsoft.com/en-us/library/ms175158.aspx

    • 1