2

I'm struggling to find a suitable solution to this. I have a fairly large SQL Server 2008 Express database containing 60+ tables (many with key constraints) and a whole bunch of data.

I need to essentially copy all of these tables and the data and the constraints exactly from one database to another. I'm basically duplicating website A - to produce an exact copy (website B) on a different domain so we end up with two completely identical websites running in parallel, each with their own identical database to begin with.

Database A is up and running on website A. Database B is set up and has it's own user. I just need to get the tables and the data intact from A to B. I can them modify my web.config connection to use the log-in credentials for database B and it should work.

I've tried backing up database A and restoring to database B via Management Studio Express, but it tells me:

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

I've also tried right clicking database A in Management Studio Express and going to Tasks > Generate scripts. But when I do this and run the SQL scripts on database B I get a whole load of errors to do with foreign keys etc as it imports the content. It seems like it's doing the right thing, but can't handle the different keys/relationships.

So does anyone know of a simple, sure-fire way of getting my data 100% exact and intact from database A to database B?

I think I used SQL Server Database Publishing Wizard to do something like this about 5 years ago, but that product seems to be defunct now - I tried to install it and it wanted me to regress my version of SQL Server to 2005, so I'm not going there!

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Dan
  • 5,836
  • 22
  • 86
  • 140

2 Answers2

9

Don't use the UI for this. If you're not familiar with the various aspects of BACKUP/RESTORE the UI is just going to lead you down the wrong path for a lot of options. The simplest backup command would be:

BACKUP DATABASE dbname TO DISK = 'C:\some folder\dbname.bak' WITH INIT;

Now to restore this as a different database, you need to know the file names because it will try to put the same files in the same place. So if you run the following:

EXEC dbname.dbo.sp_helpfile;

You should see output that contains the names and paths of the data and log files. When you construct your restore, you'll need to use these, but replace the paths with the name of the new database, e.g.:

RESTORE DATABASE newname FROM DISK = 'C\some folder\dbname.bak'
  WITH MOVE 'dbname' TO 'C:\path_from_sp_helpfile_output\newname_data.mdf',
  MOVE 'dbname_log' TO 'C:\path_from_sp_helpfile_output\newname_log.ldf';

You'll have to replace dbname and newname with your actual database names, and also some folder and C:\path_from_sp_helpfile_output\ with your actual paths. I can't get more specific in my answer unless I know what those are.

** EDIT **

Here is a full repro, which works completely fine for me:

CREATE DATABASE [DB-A];
GO

EXEC [DB-A].dbo.sp_helpfile;

Partial results:

name     fileid filename
-------- ------ ---------------------------------
DB-A     1      C:\Program Files\...\DB-A.mdf
DB-A_log 2      C:\Program Files\...\DB-A_log.ldf

Now I run the backup:

BACKUP DATABASE [DB-A] TO DISK = 'C:\dev\DB-A.bak' WITH INIT;

Of course if the clone target (in this case DB-B) already exists, you'll want to drop it:

USE [master];
GO
IF DB_ID('DB-B') IS NOT NULL
BEGIN
  ALTER DATABASE [DB-B] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE [DB-B];
END
GO

Now this restore will run successfully:

RESTORE DATABASE [DB-B] FROM DISK = 'C:\dev\DB-A.bak'
  WITH MOVE 'DB-A'     TO 'C:\Program Files\...\DB-B.mdf',
       MOVE 'DB-A_log' TO 'C:\Program Files\...\DB-B_log.ldf';

If you are getting errors about the contents of the BAK file, then I suggest you validate that you really are generating a new file and that you are pointing to the right file in your RESTORE command. Please try the above and let me know if it works, and try to pinpoint any part of the process that you're doing differently.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks! Trying this out. Just checking though, should it be `RESTORE DATABASE newname FROM DISK = 'C\some folder\dbname.bak' WITH MOVE 'dbname_data' TO 'C:\path_from_sp_helpfile_output\newname_data.mdf', MOVE 'dbname_log' TO 'C:\path_from_sp_helpfile_output\newname_log.ldf';` i.e. WITH MOVE 'dbname_data' rather than WITH MOVE 'dbname'? – Dan Apr 25 '12 at 16:48
  • Like I said, that depends on the output of sp_helpfile. You shouldn't be typing what I wrote - if the output of sp_helpfile says `dbname` then type `dbname`, if the output says `dbname_data` then type `dbname_data`. I've seen both formats in use and I don't know what your output looks like. – Aaron Bertrand Apr 25 '12 at 16:49
  • Yep, get the name changes. Think I did it correctly, but it's giving the same error as the GUI: The backup set holds a backup of a database other than the existing 'database-B' database. – Dan Apr 25 '12 at 16:51
  • Can you show the actual backup command that ran successfully? And I assume you backed up to a new filename that didn't already exist on your hard drive? – Aaron Bertrand Apr 25 '12 at 16:53
  • Maybe I just detach database B and copy the .mdf and .ldf files from location A to B, then re-attach? That's essentially what this is trying to do isn't it? – Dan Apr 25 '12 at 16:54
  • 1
    No, that is more dangerous, I don't recommend it. If something goes wrong between the detach and the attach, you now have exactly ZERO copies of your database. The BACKUP/RESTORE approach is creating a new copy of your database from a backup, while leaving the original copy of the database alone. – Aaron Bertrand Apr 25 '12 at 16:54
  • Okay, won't do that then. The back-up which ran was: `BACKUP DATABASE [database-A] TO DISK = 'D:\Backup\DailyMSSQLbackup\MSSQLBackups\database-A.bak' WITH INIT;` – Dan Apr 25 '12 at 16:57
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/10508/discussion-between-dan-and-aaron-bertrand) – Dan Apr 25 '12 at 17:00
  • I've tried the update, but it's the same - I just get the same message 'The backup set holds a backup of a database other than the existing 'database-B' database' when restoring. I just found this http://www.mssqltips.com/sqlservertutorial/122/retore-sql-server-database-to-different-filenames-and-locations/ which suggests that because database-B already exists, this approach of moving .mdf and .ldf files won't work. So I'm still very much stuck :( – Dan Apr 25 '12 at 17:25
  • Oh, well you didn't mention that database-B already existed (or I missed it) :-) So before you run the RESTORE command, first issue `DROP DATABASE [Database-B];` – Aaron Bertrand Apr 25 '12 at 17:27
  • Thanks Aaron - tried that but get another error: Cannot drop database "database-B" because it is currently in use. Argh! – Dan Apr 25 '12 at 17:32
  • 1
    Well, are you in the database? Please see yet another update. You need to set the database to single user mode (and make sure you are not in it yourself, because that's a catch-22, right?). – Aaron Bertrand Apr 25 '12 at 17:34
  • 1
    @Dan thanks for the accept. But you should also be in the habit of up-voting helpful answers (whether or not they ended up being the ultimate solution). I noticed in your profile that you've submitted fewer votes than questions. Up-voting is what helps encourage users to help you solve your problems, especially in cases where there are multiple answers and more than one was helpful (since you can only accept one). I encourage you to re-visit past questions and cast some votes. – Aaron Bertrand Apr 25 '12 at 17:47
2

I realize this is an old question, but I was facing the same problem and I found that the UI was easier and faster than creating scripts to do this.

I believe Dan's problem was that he created the new database first and then tried to restore another database into it. I tried this as well and got the same error. The trick is to not create the database first and name the database during the "Restore Database" process.

The following article is somewhat useful in guiding you through the process: http://msdn.microsoft.com/en-us/library/ms186390(v=sql.105).aspx

ThisGuy
  • 2,335
  • 1
  • 28
  • 34
  • 1
    Thanks for updating this. To be honest, the simple trick I was missing in the Management Studio UI was that on the options tab when you select the .bak file to restore, there's a checkbox saying something like 'overwrite destination database'. Checking this prevents the error which says that the database can't be restored because it already exists or has a different name etc. I use this all the time now - it was the missing link! – Dan Jul 30 '13 at 11:12