SQL2008R2 database Backup-dual-machine backup

Two Resolve SQL2008 Agent Job Error: Workaround for c001f011 maintenance plan creation failure

The SQL2008 database always fails to create an instance from a COM component that has a CLSID of {17bca6e8-a95d-497e-b2f9-af6aa475916f} from IClassFactory.

The following error occurred because: c001f011. (Microsoft.SqlServer.ManagedDTS)------------------------------

Failed to create instance from IClassFactory for COM component with CLSID {17bca6e8-a95d-497e-b2f9-af6aa475916f} because the following error occurred: c001f011. (Microsoft.SqlServer.ManagedDTS)

Workaround: Drag files

Execute the following statement on the command line to re-register the Dts.dll file, corresponding to the different platforms:

(x86)
C:\windows\system32\regsvr32 "C:\Program Files\Microsoft SQL Server\100\dts\binn\dts.dll"
(x64)
C:\windows\syswow64\regsvr32 "C:\Program Files (x86) \microsoft SQL Server\100\dts\binn\dts.dll"

Third, implement a dual-machine backup sql2008 database by creating a shared folder on the server

Instructions to back up a database by creating a job or plan content must have the MSSQL agent service turned on

SQL server2008 Computer: 192.168.123.104

Offsite Backup computer: 192.168.123.107

Windows2008 system User:administrator password:admin_12345

Two computers in the same network segment

• Create a shared folder backup with permissions of Administrator Full Control
• New Job

SQL statements in the command:

EXEC sp_configure ' xp_cmdshell ', 1

GO

RECONFIGURE

GO

DECLARE @sql varchar (4000)

DECLARE @backupfile varchar (2000)

DECLARE @retaindays int

DECLARE @now datetime

DECLARE @deletefiles varchar (2000)

DECLARE @cmd varchar (2000)

DECLARE @i int

DECLARE @User varchar (2000)

DECLARE @Pwd varchar (2000)

DECLARE @Store varchar (2000)

DECLARE @IPPart varchar (2000)

DECLARE @IP varchar (2000)

Set @Store = ' hbsp '--database name

Set @User = ' Administrator '-User name (offsite server)-----to Administrator privileges

Set @Pwd = ' admin_12345 '--Password (offsite server)-------password do not have symbols

Set @IPPart = ' d:\hbspbackup '--Path (the shared directory of the remote server, this directory has access to read and write permissions of the above user)

Set @IP = ' 192.168.123.107 '--remote server IP or host name

Set @retaindays = 30--Number of days to keep backups

--Create mappings

EXEC master. xp_cmdshell ' net use \\192.168.123.107\hbspbackup "admin_12345"/user:192.168.123.107\administrator '

--Start Backup

Set @backupfile = ' \\192.168.123.107\hbspbackup\ ' + @Store + ' _db_ ' +

Replace (replace (CONVERT (Varchar,getdate (), 20), '-', '), ', '), ': ', ') + '. BAK '

Set @sql = ' backup database ' + @Store + ' to disk= ' [e-mail protected]+ ' with retaindays= ' +convert (varchar), @retainday S

EXEC (@sql)

Set @backupfile = ' \\192.168.123.107\hbspbackup\ ' + @Store + ' _tlog_ ' +

Replace (replace (CONVERT (Varchar,getdate (), 20), '-', '), ', '), ': ', ') + '. TRN '

Set @sql = ' backup LOG ' + @Store + ' to disk= ' [e-mail protected]+ ' with retaindays= ' +convert (varchar), @retaindays)

EXEC (@sql)

--close allow execution of xp_cmdshell

EXEC sp_configure ' xp_cmdshell ', 0

GO

RECONFIGURE with OVERRIDE

GO

This statement only backs up the database in the server shared folder, and the database is not backed up locally, and if you want to claim the backup file locally, add a locally backed SQL statement

Bakcup database hbsp to disk ' C:\backup\back.bak '

Back log hbsp to disk ' c:\backup\backlog.trn '