Running a script to Backup SQL Database

Asked By beverlynavarro 0 points N/A Posted on - 06/04/2012

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?

• Status: Open
• Question Views: 1002
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: MS SQL Server

Answered By siritech 0 points N/A #97620

Running a script to Backup SQL Database

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

 

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.

About siritech

Questions
0

Answers
1

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 06/07/2012
• Question Category: MS SQL Server