Sql-server – SQL Server 2008 backup script

backupsharepointsql serversql-server-2008

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?

Best Answer

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

Related Solutions

Sql-server – SQL Server 2008 – Restore Database With RESTART

Has this ever worked?

RESTART is only specified when you want to continue an interrupted restore. If you can't change the SQL, then you can only somehow break a first restore to generate an error condition.

Because of this, you can't be sure that it's the same on SQL 2005 unless it happens every time. Or they changed behaviour.

Sql-server – SQL Server 2008 Restore

Try this:

CREATE DATABASE foo;
GO
CREATE DATABASE bar;
GO
BACKUP DATABASE foo TO DISK = 'c:\temp\x.bak';
BACKUP DATABASE bar TO DISK = 'c:\temp\x.bak';
GO
RESTORE HEADERONLY FROM DISK = 'c:\temp\x.bak';

Relevant columns for this scenario:

Position  DatabaseName
--------  ------------
1         foo
2         bar

To restore foo, whether it exists or not:

RESTORE DATABASE foo FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 1, REPLACE;

To restore bar, whether it exists or not:

RESTORE DATABASE bar FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 2, REPLACE;

If you don't specify WITH FILE, you will get a slew of errors, depending on whether the database in Position = 1 already exists.

The lessons here:

do not use common and generic names for backup files - name them for the database they represent and keep them separate.
use WITH INIT if you expect a backup file to only ever contain one copy of a backup.
understand what is in your backup by first running RESTORE HEADERONLY and/or RESTORE FILELISTONLY.

If you show us those two columns from the output of RESTORE HEADERONLY and what database you are actually trying to restore, we can provide you with a specific command that should work.

Best Answer

Related Solutions

Sql-server – SQL Server 2008 – Restore Database With RESTART

Sql-server – SQL Server 2008 Restore

Related Question