4

I am copying a database from a live environment to test environment. I've made a .bak file from the live database and copied the file to the test server.

Question is, should I use "Restore Database" to add it to the test server or "Restore Files and Filegroups"?

(sometimes the database will already exist, sometimes it won't. In either case I want to just have a clone of the original and don't care about overwriting)

After a bit of playing around it appears both achieve what I want - so what is the real difference, and does it matter which method I use for my situation?

2 Answers 2

5

Short answer - unless it's a huge SQL database and you're doing clever things with it then just ignore the filegroup backup, and stick with the database backup.

Long answer - With SQL server you have databases and filegroups, a database contains all of the data within that database and is nice and straight forward. A filegroup on the other hand contains either all or some of the data within the database. Filegroups hold the physical files (eg the mdf files) which make up the database, and in most cases there will only be one of them, normally called Primary, which will contain the one mdf used.

Now in a large installation you might find that your database mdf files are too large to fit onto one disk, so you might want to split the database across multiple mdf files, and at this point filegroups come into play. If you split all the tables which are regularly used and updated into one mdf on a fast HDD and put it in the Primary filegroup, and then put the less used tables used more for reference onto a slower HDD in a secondary filegroup, you could make best use of the system, however you might not want to bother backing up the secondary mdf, since the data within that file very rarely gets changed. You can therefore choose to do a filegroup backup instead of a complete database backup, and only backup the Primary filegroup, therefore reducing the amount of storage space required for your backup.

0
2

Use Restore Database if you do not need to change the file location or add other files like changing the specific location of the log files.

For what you are doing most likely a simple restore database would do the trick.

1
  • Restore Database allows to change the location of files. I don't see such an option in Restore Filegroups. Sep 3, 2020 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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