Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

enter image description here

I am trying to find an efficient way of being able to physically save and open a SQL Server database like how it's done with Access. I would like to be able to save my entire database into a file and open it on some other server. I have already tried backup/restore but they're very tricky and it does not work all the time. Is there a way to do it other than the backup/restore procedure?

The following is what I get when I try to do the restore. I tried every possible approach that I could think of, but it simply doesn't restore.

share|improve this question

migrated from stackoverflow.com May 8 '13 at 15:08

This question came from our site for professional and enthusiast programmers.

2  
You can move the MDF file and attach it on the other SQL instance, but I'm afraid that is no simpler than backup restore. Just bite the bullet and go with it. More complexity is the price you pay for using a more enterprise database backend. Check out the DBA stackexchange site or the MSDN documentation for how to do this. –  JohnFx May 8 '13 at 14:48
2  
What is tricky about backup and restore? You could copy files and then attach, but I don't see that as any simpler. –  paul May 8 '13 at 14:49
5  
What does "does not work all the time" mean? Why don't you try to solve that problem instead of trying to invent an "easier" way? –  Aaron Bertrand May 8 '13 at 14:50
    
Please have a look at the above. –  Sam May 8 '13 at 15:05
add comment

2 Answers 2

up vote 7 down vote accepted

Backup and restore are the simplest/safest ways to do this. There are a couple of things that you need to watch out for, or you'll get errors like the one you're seeing now.

  1. SQL Server will default to restoring the files to their original physical locations. If you're restoring to a different server, or a different database name, these paths probably aren't correct. You will have to specify the correct new database name, and file locations (WITH MOVE...).

  2. If you're overwriting an existing database, you'll need to tell SQL Server that you specifically intend to do this (WITH REPLACE). This is a safety feature; don't get into the habit of blindly using this option unless you know you need it.

The restore GUI tool in Management Studio supports both of these options.

To specify the name of the database you want to restore as, go to the "General" page, and enter the correct name in the "Database" field (under the "Destination" subhead).

To specify where you want the database files to be restored to, go to the "Files" page, and specify the new paths in the "Restore As" column.

If you're overwriting an existing database, you'll need to go to the "Options" tab and check the "Overwrite the existing database" option.

If this is a procedure you plan to repeat regularly, you can generate a script for the RESTORE command by clicking the "Script" button on the Restore Database window toolbar.

share|improve this answer
    
Many thanks for your knowledgeable answer. This is what I needed to do. –  Sam May 8 '13 at 15:41
add comment

Backup/Restore is the right process to use. You are getting the error because you are trying to restore over a DB that already exists.

As in any scenario, be sure that overwriting the DB is what you want to do. Three ways of doing this are:

  1. Restore with another name (SSMS): In the restore DB window, provide another DB name (eg: TRS_COPY).
  2. Restore with overwrite (SSMS): In the restore DB window, under the options panel, check the Overwrite the existing database (WITH REPLACE) option.
  3. Delete the TRS DB and then perform the restore from the backup file.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.