Running a script to Backup SQL Database

Asked By 0 points N/A Posted on -
qa-featured

When you run a script or program to backup SQL database from SQL server ,

Is it necessary to close all applications accessing the database?

If not, is the last record modification and changes made by the user will be included on the backup?

SHARE
Answered By 0 points N/A #97620

Running a script to Backup SQL Database

qa-featured

Hi Beverlynavarro,

When you run a script or program to backup SQL database from SQL server, it is necessary to close the applications accessing the database. The database needs to be set to single user mode and then start the restoration. If not, error might thrown as "Exclusive access could not be obtained because the database is in use". 

Hence while taking backup please follow below steps:

1. Kill the processes using the database through activity monitor though SQL Management Studio > Connect to the SQL Server > Activity Monitor

Activity Monitor

 

2. Detach and attach the database

3. Run the query below to set your database to single user mode and then do restoration.

ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

The last record stored in the backup database will be the last record updated before running the above query.

Regards,

Siri.

Login/Register to Answer

Related Questions