Sql Server Error Message: Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed
- Posted by Sqltimes
- On June 7, 2014
- 0 Comments
Quick one today:
Ran into an interesting error message today. On one of our lab servers, I was making some changes to test a deployment and I was running FULL database backup and adding/removing some data files on the same database.
Boom !! I got this error.
Msg 3023, Level 16, State 2, Line 1 Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
---- Run backup job--BACKUP DATABASE SampleDBTO DISK = N'M:\MSSQL\Backup\MSSQLSERVER\XS and RT\test.BAK'WITH COMPRESSIONGO---- While backup is running, I attempted to create a new data file.--ALTER DATABASE SampleDBADD FILE ( NAME = N'StoredValue_FG' , FILENAME = N'I:\MSSQL\Data\MSSQLSERVER\StoredValue_FG\StoredValue_FG.NDF' , SIZE = 100 MB , FILEGROWTH = 10%)TO FILEGROUP [SV_FG]GO |
The message is very self descriptive. Essentially it boils down to: “Do not run any file manipulations, when running database backup“. Which makes sense. Looks like when database backup runs, Sql Server does not allow data files changes to the same database. Since database backup is responsible for copying (backingup) data in all the data files into a backup file, it may want to restrict and changes to data files.
After the backup completed, the data file changes are successful.
Hope this helps,
_Sqltimes
0 Comments