2

We have a handful of SQL Server databases. Probably not relevant but just for info:

  • OS: Windows Web Server 2008 R2
  • RDBMS: SQL Server 2012 Express

My question is: When using SQL Server Management Studio to back up a database, is the resulting .bak file all one would need to fully restore the database on another machine?

I've been doing this for years in development environments, so to my mind the answer is yes, but we are migrating a production server so I wouldn't want to risk anything. Are there any log files, config files, [?], that might hinder performance/integrity/[?] if omitted from a restoration?

Improve this question

2 Answers 2

Reset to default
9

Basically yes, all you'd need is to backup and restore the databases. However, there's a bunch of stuff that won't do.

  • Server level settings (MAXDOP/CTFP, etc.)
  • Agent Jobs
  • Maintenance plans
  • Linked Servers
  • Users/Logins
  • Any other customization

It's kind of a pain in the butt to find all this stuff, and even if it's a one time move, scripting it all out can be a hassle. I'd recommend something like the free dba tools to help you with that.

Improve this answer
2

You are absolutely correct! When using SQL SMS to back to a data, .bak file needs to be reloaded again and restored on the other machine in order to configure rightly so.

Improve this answer

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.