Home / server / Questions / 296627
Accepted
JL.
Asked: 2011-08-03 03:46:22 +0800 CST

How can I backup a SQL Server 2008 R2 DB without taking it offline?

  • 772

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?

sql-server

2 Answers

  1. Best Answer

    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:

    • 10

  2. 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.

    • 6