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

sql-server

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:

How to: Back Up a Database (SQL Server Management Studio)
How to: Create a Full Database Backup (Transact-SQL)

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.

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

Related

Recent Posts