3

I have a lot of SharePoint 2013 databases to backup and if update fail to restore, so I want to automate this operations. 

ALTER DATABASE [TEST_1] SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

BACKUP DATABASE [TEST_1] 
TO DISK = 'C:\path\test_1.bak' WITH NOINIT, STATS = 10
GO

ALTER DATABASE [TEST_1] SET ONLINE WITH ROLLBACK IMMEDIATE

But there is and error msg:

Msg 942, Level 14, State 4, Line 1
Database 'TEST_1' cannot be opened because it is offline.

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Is it possbile to switch database offline, make backup and turn it in online mode after this?

Improve this question
2
  • SharePoint has this functionality that will do the backups of the database for you via the SharePoint Administrator Console (or whatever it is called in 2013). It will do this via PowerShell scripts I do believe.
    – user507
    Commented Jun 22, 2015 at 17:35
  • 1
    Why would you want to take the database offline during the backup ?
    – Spörri
    Commented Jun 22, 2015 at 23:20

4 Answers 4

Reset to default
2

Two things. First if you are going to do that then make your script this:

 ALTER DATABASE [TEST_1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 GO
 USE [TEST_1]
 GO
 BACKUP DATABASE [TEST_1] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\test\test_1.bak' WITH NOINIT, STATS = 10
 GO
 ALTER DATABASE [TEST_1] SET MULTI_USER WITH ROLLBACK IMMEDIATE

Single user will kick everyone off but then you will be the first one back on with the backup. Then when you are done set it back to multi_user.

Second you might consider using Minion Backup. It's a free automated backup script that is supposed to be both very powerful and easy to use. The writers may very well have a config specifically for SharePoint or at the very least you can ask them. http://minionware.net/#miniontabs|2

Improve this answer
0
3

It is not possible, because you cannot create a backup of an offline database.

From MSDN:

Without the NO_TRUNCATE option, the database must be in the ONLINE state. If the database is in the SUSPENDED state, you might be able to create a backup by specifying NO_TRUNCATE. But if the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE

Improve this answer
3

I write Minion Backup and this kind of thing would be very easy to do. We allow you to run Pre and Post code before each DB, so for your SharePoint DB, you can just put it into single-user mode and change it back when it's done. However, we can't guarantee that you'll be the one to get that connection.

But you can download Minion Backup here and give it a shot. http://www.MinionWare.net

Improve this answer
2

SQL does not allow to backup a database when in offline mode. You can also create maintenance plans in order to automate the job. So the first step of you job can be setting the offline databases to online mode and then the maintenance plan will do is work and then the third step will be to set your databases back to online.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.