7

I am familiar with the detach/attach process. But I cannot take the database offline.

Is there a way to back a database, and end up with a usable mdf database file, without taking it offline?

jscott
  • 24,484
  • 8
  • 79
  • 100
JL.
  • 1,283
  • 10
  • 22
  • 35

2 Answers2

10

You don't need to take a database offline to back it up. The backup process will create a .BAK file, which you can use in a restore operation.

If you're using SQL Server Management Studio (SSMS), right-click the database, select "Tasks", then "Back up".

enter image description here

The next window will prompt for some details such as location, back up type, etc. Select the appropriate options for your environment and click "OK".

enter image description here

You may also use T-SQL to script a backup:

USE exampleDB;
GO
BACKUP DATABASE exampleDB
TO DISK = 'C:\exampleDB.BAK'
   WITH FORMAT,
      MEDIANAME = 'SQLbackups',
      NAME = 'exampleDB-Full Database Backup';
GO

See the following MSDN articles for more details:

jscott
  • 24,484
  • 8
  • 79
  • 100
  • Wow, thank you for the great answer. so the .bak is as useful as a detached mdf? Admins won't frown seeing one? – JL. Aug 02 '11 at 12:04
  • @JL I can't image why they would frown on using `.BAK` instead of `.MDF`. Using the `.BAK` is more flexible, as @squillman notes, because you can also restore it to a different database name on the same server. Have a look at the SQL Server Agent and Maintenance Plans. You can easily schedule automatic backups, among other many other options. – jscott Aug 02 '11 at 12:08
6

You can take a normal full backup of the database and restore it as a different database on the same SQL Server instance or as the same database on a different SQL Server intance. This will create a new .mdf file as of the point in time of the backup.

Other than that, no. The database must be stopped in order for the files to be finalized and closed.

squillman
  • 37,883
  • 12
  • 92
  • 146