About SQL Geo-backup methods

Recently, the company more and more serious dependence on the server, the computer room two servers, a set of food and beverage system, invoicing system, and another set of OA system hung up (Mister's painstaking!). , taking into account the recent social disharmony, thieves flooded, and then particularly worried about the server data security, although made a local backup, but in The hateful thief in front ~ ~ ~ So have to hurry to do networkbacup more appropriate security!

Think of a program and several options on the web for reference:

Scenario One: SQL comes with a database backup plan

A: Basic ideas
1: To enable offsite backups, you must use a domain user account to start the SQL Server service and the SQL Server Agent service because the local System account cannot access the network.
2: Establish a domain user account with the same name as the SQL Server service startup in the remote machine, and the password remains the same. Create a shared folder in a remote machine and set the appropriate permissions.
Note: The new account is for Workgroup mode, and if it's based on domain mode, you don't need to build your account
3: Establish a maintenance plan for offsite backups in the SQL Server server, and in full backup and transaction log backups, use the UNC path of the input offsite shared folder.
4: Configure other settings in the maintenance plan. The above statement may be less intuitive, and the actual experiment below shows how to do offsite backups. The experimental network model is simple, such as: Two: Offsite backup demonstration
1: Start the SQL Server service and the SQL Server Agent service in the SQL Server server as a domain user account.
Using the Administrator account directly in this experiment, you can switch other domain user accounts based on the actual environment.                   2: In the file backup server to establish a shared folder, the share permissions to remove the Everyone user, join administrator users, permissions settings such as. 3: In SQL Server server, open Enterprise Manager, new Database maintenance plan: 4: In the Maintenance Plan Wizard, select the database that you plan to back up, set up the database optimization information and check the integrity of the steps.
5: Set the scheduling schedule in the "Specify database backup Schedule" in the wizard.
6: In the "Specify Backup Disk Directory" interface, manually enter the file Backup server shared folder UNC path. 7: In the transaction log backup schedule interface, decide how to back up according to the actual environment.
8: Follow the wizard to set up other steps to complete the offsite backup maintenance plan. Three: Test whether the offsite backup was successful.
1: Check if the backup file exists in the shared folder.

2: If no backup was successful, review the SQL Server logs and check the permissions settings and the exact consistency of the user name and password, or restart both computers!


Scenario Two: Using File synchronization software, FTP and other synchronization source server database backup files to the backup machine

Scenario three: Using SQL timed Jobs

Example:
--Establish a connection to the Yniang, where Garfield is the password for the login Yniang
Exec Master. xp_cmdshell ' net use \\yniang\backupgarfield/user:yy_domain\administrator '

--Backup Database Miao,yniang shared directory Backup to fully share the control
Backup Database [Miao] to Disk = ' \\YNiang\Backup\miao.bak '

--Disconnect the Yniang connection

Exec Master. xp_cmdshell ' net use \\yniang\backup/delete '

Programme III:

1: Build a shared folder on the target machine: Remotebak, and give a user (such as an administrator) Write permission, example administrator,123456.

2: Build the drive map in the source machine with the command mode:

xp_cmdshell ' net use z: \ \ target machine ip\remotebak "123456"/user: Target machine ip\administrator '

3: Automatic daily backup (put the following code in the job, set the daily scheduled execution)

DECLARE @shotname varchar (@newtime varchar), @sql varchar (8000set @newtime =convert (varchar), GETDATE (), 120)
Set @oldtime = CONVERT (varchar (), DATEADD (Dd,-10,getdate ()), 120)

Select @shotname = ' Sharewin ' +left (@newtime, 4) +substring (@newtime, 6,2) +substring (@newtime, 9,2)
Set @sql = ' backup database sharewin to disk = ' + ' ' z:\ ' [email protected] + '. Bak '

EXEC (@sql)
4: Delete the mappings after the backup is complete:

xp_cmdshell ' net use Z:/delete '

Reprint connection:http://hi.baidu.com/%CB%AE%B0%B6%D4%C2%B9%E2/blog/item/8c036651c646f7561038c299.html

http://alligator.blog.51cto.com/36993/112844

--------------------------------------------------

The above plan I tested, all feasible,

I used two kinds of offsite backup method, 1) Local LAN shared folder backup (local area network backup), 2) program two offsite FTP server backup (Internet offsite Backup)

----------------------------------------------

Posted @ 2012-06-27 11:18 QA Dragon Read (9) Comments (0) edit

failed to create a database backup maintenance plan.

When you create a database backup maintenance plan, the failure prompts the following error

----------------------------------------------------------------------------------------------------------

Failed to create maintenance plan.

------------------------------
Additional Information:

Failed to load type from assembly "Microsoft.SqlServer.Smo, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91" Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection ". (Microsoft.SqlServer.MaintenancePlanTasks)

----------------------------------------------------------------------------------------------------------

Workaround install sql2005 's SP2 patch

Official:http://www.microsoft.com/zh-cn/download/details.aspx?id=9969

Reference:http://hi.baidu.com/sai5d/item/0afa42bf3a371b43bb0e127d

Posted @ 2012-06-27 11:14 QA Dragon Read (8) Comments (0) edit

SQL BACKUP statement (full)

--Full backups backup database NorthwindCS to disk= ' G:\Backup\NorthwindCS_Full_20070908.bak '--differential backup Database Northwind CS to disk= ' G:\Backup\NorthwindCS_Diff_20070908.bak ' with differential--log backup, default truncated logs backup log NorthwindCS to disk= ' G:\Backup\NorthwindCS_Log_20070908.bak '--log backup, do not truncate log NorthwindCS to disk= ' G:\Backup\NorthwindCS_Log_ 20070908.bak ' with no_truncate--truncate log does not retain backup log NorthwindCS with no_log-or backup log NorthwindCS with Trun Cate_only--The log file does not become smaller after truncation--it is necessary to shrink--file backup Exec sp_helpdb NorthwindCS--View data file backup Database NorthwindCS file= ' N 
Orthwindcs '--The logical name of the data file to disk= ' G:\Backup\NorthwindCS_File_20070908.bak '--filegroup backup Exec sp_helpdb NorthwindCS--View data file 
Backup Database NorthwindCS filegroup= ' Primary '--The logical name of the data file to disk= ' G:\Backup\NorthwindCS_FileGroup_20070908.bak ' With init-split backup to multiple targets-no loss of target backup Database NorthwindCS to disk= ' G:\Backup\NorthwindCS_Full_1.bak ', dis when recovery is not allowed K= ' G:\Backup\NorthwindCS_Full_2.bak'--mirrored backup--each target is the same backup Database NorthwindCS to disk= ' G:\Backup\NorthwindCS_Mirror_1.bak ' Mirror to disk= ' G:\Ba Ckup\northwindcs_mirror_2.bak ' with format--the first time a mirrored backup is formatted target--mirrored backup to local and remote backup Database NorthwindCS to disk= ' G:\Ba Ckup\northwindcs_mirror_1.bak ' Mirror to disk= ' \\192.168.1.200\Backup\NorthwindCS_Mirror_2.bak ' with Format--generates a daily Backup file Declare @Path Nvarchar Set @Path = ' G:\Backup\NorthwindCS_Full_ ' +convert (Nvarchar,getdate (), ()) + '. Bak ' Ba Ckup Database NorthwindCS to http://www.hack58.net/article/html/3/7/2008/[email protected]--from NORECOVERY or--St Andby mode recovery database is available for restore DB Northwindcs_bak with Recovery--view backup set in target backup RESTORE HEADERONLY from Disk = ' G:\Backu P\northwindcs_full_20070908.bak '--View information for the first backup set of a target backup Restore filelistonly from Disk = ' G:\Backup\NorthwindCS_Full_ 20070908_2.bak ' with file=1--View the volume label of the target backup Restore labelonly from Disk = ' G:\Backup\NorthwindCS_Full_20070908_2.bak '-- Backup settings password protect backup Database NortHwindcs to disk= ' G:\Backup\NorthwindCS_Full_20070908.bak ' with Password = ' 123 ', init Restore Database NorthwindCS Fro M disk= ' G:\Backup\NorthwindCS_Full_20070908.bak ' with Password = ' 123 ' Source: HTTP://HI.BAIDU.COM/ZIYOUBIRD/BLOG/ITEM/E0 458ac48cbae0c239db49f1.html

Transferred from: http://www.cnblogs.com/liubaolongcool/archive/2012/06/27.html

About SQL Geo-backup methods (GO)