:::: MENU ::::

Posts Categorized / Backup and Restore

  • Dec 06 / 2013
  • 0
Backup and Restore, dbDigger, SQL Server Agent scheduled Jobs, SQL Server Error messages, Xp_CmdShell

Executed as user: NT AUTHORITYSYSTEM. xp_create_subdir() returned error 5, ‘Access is denied.’

Recently i got an job failure email alert. This job is involved in backup of few databases and as a part of process it was required to create directories for each database as well. When i explored the job history to get the failure reason following message was found in log

Message
Executed as user: NT AUTHORITYSYSTEM. xp_create_subdir() returned error 5, ‘Access is denied.’ [SQLSTATE 42000] (Error 22048). The step failed.

sa is owner of this job and there is no apparent role of NT AUTHORITYSYSTEM as mentioned in the message. However there is a by design flow that involved NT AUTHORITYSYSTEM.
Point to note is that SQL Server service was configured to run under local system account.

Reason:

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. In this case xp_cmdshell was being invoked by sa i.e. sysadmin so it is being executed in context of local system account. Local account was NT AUTHORITYSYSTEM that has no access to create the directories on backup location.

Solution:

To solve the issue i changed the SQL Server service login to a valid domain account that has access to create folders and files on network location for backup.

  • Oct 11 / 2013
  • 0
Backup and Restore, Consultancy, dbDigger, T-SQL Tips and Tricks

Create directories through T-SQL

In a recent task i was connected to SQL Server through SSMS and had no OS access. To configure the automatic backups i was required to create couple of directories on the disk. So without having OS access i had to create directories by using T-SQL. Following is the command that i used to create ‘D:DBBackupsDBDigger’

EXEC master.dbo.xp_create_subdir 'D:DBBackups_DBDIgger'
GO
  • Nov 17 / 2012
  • 0
Backup and Restore, dbDigger, Disaster Recovery, SQL Server Training

Implementing retention span for SQL Server backup device

SQL Server uses logical devices or media to perform database backups. Backups may be append or overwrite to existing backups in these media sets or devices. It may be a serious issue when a backup in device is silently overwritten without your notice. This scenario may occur if you repeat a previous backup device name with INIT option. Consider the following example for this scenario

-- Create a backup  
 BACKUP DATABASE DBDIGGER   
 TO DISK = 'E:DBD-BackupDemo.bak'  
 GO  
 /*   
 Create another backup with same device name.  
 By default backup would be performed by using  
 WITH NOINIT option and would be appended  
 */  
 BACKUP DATABASE DBDIGGER   
 TO DISK = 'E:DBD-BackupDemo.bak'  
 GO  
 -- Verify the two backups on backup device  
 RESTORE HEADERONLY FROM DISK = 'E:DBD-BackupDemo.bak'  
 GO  

Here we are shown two backups on the device. It shows that second backup was also appended to the device. Going further we would issue another backup command by using WITH INIT. This time backup would overwrite the existing backups on the device.

 /*   
 Create another backup with same device name and INIT option.  
 It would overwrite the existing backups on device.  
 */  
 BACKUP DATABASE DBDIGGER   
 TO DISK = 'E:DBD-BackupDemo.bak'  
 WITH INIT  
 GO  

Now verify that only last backup exists on the device and all previous backups were overwritten.

/*   
 Verify that previous backups are overwritten   
 leaving behind the last backup  
 */  
 RESTORE HEADERONLY FROM DISK = 'E:DBD-BackupDemo.bak'  
 GO  

RETAINING THE BACKUPS ON THE DEVICE

At this point we require some way to ensure that backups on the device would not be overwritten even by accidentally using the WITH INIT option. There may be two approaches to make sure the retention of backups on the device for a specified time.
  • Use WITH EXPIREDATE or WITH RETAINDAYS
  • Set retention days server wide for all databases

Following is a brief description of these two options

WITH EXPIREDATE | WITH RETAINDAYS days

This option operates along with backup command for a specific device of a database. We may specify a date or days along with WITH EXPIREDATE or WITH RETAINDAYS option respectively.  Here we would create a backup device with retention span of two days

-- Create a backup with retention span 2 days  
 BACKUP DATABASE DBDIGGER   
 TO DISK = 'E:DBD-BackupDemo2.bak'  
 WITH RETAINDAYS = 2  
 GO  

Now create a backup in same device along with using WITH INIT option. This time error would be generated because backups in the device are retained for two days.

 /*   
 Create another backup with same device name and INIT option.  
 It would overwrite the existing backups on device.  
 */  
 BACKUP DATABASE DBDIGGER   
 TO DISK = 'E:DBD-BackupDemo2.bak'  
 WITH INIT  
 GO  

If next backups are created in the device without any retention parameter even then device would maintain the backups with retention parameter and could not be overwritten for next two days. We may get the expiration date or retention span of files by using RESTORE HEADERONLY.
Similarly EXPIREDATE option may be used to specify an expiration date to which backups on the device would be retained. Date provided should be according to system configured date format.

SET RETENTION DAYS AT SERVER LEVEL

We  have another option to include the fixed retention days for every backup device of databases on that very server. For using this method

  • Right click on instance
  • Go to properties
  • Go to database settings in the left panel
  • Set default media retention span in days here. By default it is zero

The retention days span here would automatically be implemented for every backup device of that server without explicit use of EXPIREDATE or RETAINDAYS.

Note:

Although WITH INIT would not be able to overwrite the retained media but WITH FORMAT may overwrite the header and content of device.

  • Feb 24 / 2012
  • 0
Backup and Restore, dbDigger, SQL Server Error messages, SQL Server IO, System Administration, Windows Server

Database backup and restore failed on mapped network drive

During my job at National Database and Registration Authority of Pakistan (NADRA), i was accustomed to create database backups directly on mapped network drives. We have a domain configured at NSRCs with SQL Server 2000. Fore servers with single disk, it becomes very efficient by preventing the local server disk IO. Now while working with SQL Server 2005 installed on windows server 2003 work group. i was required to create an adhoc backup during peak hours. For optimized IO i tried to use mapped network drive for creating backup on it directly. But surprisingly mapped network drive was neither available in SSMS backup location explorer nor through T-SQL backup statement. Error message was being generated.

Database backup and restore on mapped network drive

Same statement was working successfully for any location other than mapped network drive. Keeping in view my previous experience with SQL Server 2000 on domain i was not expecting any problem in this task. However after some time i realized that network drive is not available as backup device.

Next option was BOL and Google. There i came to know that mapped network drives are available for backups only when your SQL Server service is running under domain account that has access to mapped network drive.

Conclusion

Without a privileged domain account as start up account of SQL Server service, mapped network drives cannot be accessed from within SQL Server context for backup or restore process.

  • Dec 27 / 2011
  • 0
Backup and Restore, DBA Interview questions, dbDigger, Disaster Recovery

WITH Format option for mirrored backup devices

WITH clause when used in backup statement has various associated options. I have discussed effect of couple of these associated options

  • INIT/NOINIT
  • FORMAT/NOFORMAT

In most of the cases both of above options do not make major difference when used while creating backups on disk rather than tape drive. However i got error when using these options for mirror backups. For mirror backups INIT was not producing same results as that of FORMAT. Consider following scenarios
Try to create full backup of AdventureWorks along with a mirror backup. In following script INIT option is provided

 
BACKUP DATABASE AdventureWorks  
 TO DISK = 'C:AdventureWorks.bak'  
 MIRROR TO DISK = 'C:AdventureWorks1.bak'  
 WITH INIT  
 GO  

Previously backup device is not present but mirror backup script would produce following error even with or without INIT option.

Msg 3215, Level 16, State 1, Line 1
Use WITH FORMAT to create a new mirrored backup set.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

By providing WITH FORMAT argument as suggested in error message, backup along with mirror backup would be created successfully

WITH Format option for mirrored backup devices

Once mirrored media set is created you may append later backups to same mirrored media set without using WITH FORMAT. In case of adding new mirror to existing media WITH FORMAT would be required again.

  • Dec 19 / 2011
  • 0
Backup and Restore, dbDigger

Option ‘format’ conflicts with option(s) ‘noinit’

While using the options Format and NOINIT in WITH clause of backup statement, following error statement is generated

Msg 3031, Level 16, State 1, Line 1
Option ‘format’ conflicts with option(s) ‘noinit’. Remove the conflicting option and reissue the statement.

Simple solution is to use
Init with Format, Init with Noformat or NoInit with Noformat.
NoInit with format can not be used.

But using this simple solution is not enough unless you have proper understanding of these options used in WITH clause.
First of all it should be noted that these (NoInit, NoFormat) options are not mandatory. If you do not provide these then default are NOFORMAT and NOINIT.
Now let us have a look that what does these options mean and what is effect of using these instead of default values.

INIT / NOINIT

INIT / NOINIT is relates to relation among existing backups and current backup on a backup device. The term backup device here refers to backup path + backup file name. Using NOINIT with an already present backup device would append the current backup to existing backups there. In that case there would be multiple backups in a single device and size of backup device would be cumulative size of all backups. During restore process or while using RESTORE HEADERONLY would show separate backups existing in a single backup device. On other hand using INIT option would overwrite all existing backups and only last backup would be stored.

FORMAT / NOFORMAT

FORMAT / NOFORMAT resembles the INIT / NOINIT in functionality except that it also deletes the device header. While using disk for backup media FORMAT does not affect more than INIT but on tape drives it may affect the file system on tape.

REASON FOR ERROR

Now we may understand that what was the reason of our error statement. NOINIT could not be used along with FORMAT because FORMAT would try to format the backup device along with device header while NOINIT would try to append the backup in the device.

Pages:1234
Consult us to explore the Databases. Contact us