Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 7 down vote accepted

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:

share|improve this answer
    
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 2 '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 2 '11 at 12:08
add comment

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.

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.