About automatic remote database backups for SQL Server 2005

Original: (original) automatic remote database backup for SQL Server 2005

Due to project needs, the database on the target server needs to be backed up and transferred daily, check some of the online help, combined with their own actual needs, wrote this article, hoping to have the same needs of friends to help. Target server: 192.168.1.197, backup server: 192.168.0.194

1, create a new folder on the backup server (my is C:\DbBackupTest), first of all to determine the backup server to have sufficient permissions (the permissions are set as shown), preferably the "Administrators" group account. Next to the backup server, open Group Policy, Computer Configuration, security settings, local policies--Network access: Local account sharing and security mode--classic-Local users authenticate as their own.

2.1. Open SQL Server 2005 on the target server, create a new job, and then create a new step (as shown), after specifying the target database, write the following script (note the space and backslash), then click "OK" to complete the first step.

--Open xp_cmdshell
EXEC sp_configure ' show advanced options ', 1; RECONFIGURE;

EXEC sp_configure ' xp_cmdshell ', 1; RECONFIGURE;

--Establish a connection

Exec xp_cmdshell ' net use \\192.168.0.194\DbBackupTest "fill in the backup server's login password"/user:192.168.0.194\administrator '

--Backing up the database

Declare @Path Nvarchar (200)

Set @Path = ' \\192.168.0.194\DbBackupTest\DigitalGolf_New_ ' + Convert (nvarchar,getdate (), +) + '. Bak '

Backup Database digitalgolf_new

to disk = @Path

With Format

--Disconnect

Exec xp_cmdshell ' net use \\192.168.0.194\dbbackuptest/delete '

2.2. Create a new "plan" as shown in:

3, after the above two steps, it has been completed, you can directly on the job right click to perform the test has been backed up successfully ^-^.

4. Related Help Links:

Http://tech.huweishen.com/gongju/1423.html
Http://www.cnblogs.com/059212315/archive/2012/08/10/2631627.html
http://hi.baidu.com/ziyoubird/item/03440b226f5a824646996290
Http://database.51cto.com/art/201011/233117.htm

4, more backup form reference Help as follows: (Reprinted from: http://hi.baidu.com/ziyoubird/item/03440b226f5a824646996290)

--Full backup
Backup Database NorthwindCS
To disk= ' G:\Backup\NorthwindCS_Full_20070908.bak '

--Differential backup
Backup Database NorthwindCS
To disk= ' G:\Backup\NorthwindCS_Diff_20070908.bak '
With differential

--log backup, default truncation log
Backup Log NorthwindCS
To disk= ' G:\Backup\NorthwindCS_Log_20070908.bak '

--log backup, do not truncate log
Backup Log NorthwindCS
To disk= ' G:\Backup\NorthwindCS_Log_20070908.bak '
With No_truncate

--Truncate LOG not retained
Backup Log NorthwindCS
With No_log

-OR
Backup Log NorthwindCS
With TRUNCATE_ONLY
--Log files do not become smaller after truncation
--it is necessary to be able to shrink

--File backup
Exec sp_helpdb NorthwindCS--View data files
Backup Database NorthwindCS
File= ' NorthwindCS '--The logical name of the data file
To disk= ' G:\Backup\NorthwindCS_File_20070908.bak '

--File Group backup
Exec sp_helpdb NorthwindCS--View data files
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 one target is allowed to be lost when recovering
Backup Database NorthwindCS
To disk= ' G:\Backup\NorthwindCS_Full_1.bak '
, disk= ' G:\Backup\NorthwindCS_Full_2.bak '

--Mirrored backup
--Each goal is the same
Backup Database NorthwindCS
To disk= ' G:\Backup\NorthwindCS_Mirror_1.bak '
Mirror
To disk= ' G:\Backup\NorthwindCS_Mirror_2.bak '
With format-the first time you do a mirrored backup format the target

--mirrored backup to local and remote
Backup Database NorthwindCS
To disk= ' G:\Backup\NorthwindCS_Mirror_1.bak '
Mirror
To disk= ' \\192.168.1.200\Backup\NorthwindCS_Mirror_2.bak '
With Format

--Generate a backup file every day
Declare @Path Nvarchar (2000)
Set @Path = ' G:\Backup\NorthwindCS_Full_ '
+convert (Nvarchar,getdate (), ()) + '. Bak '
Backup Database NorthwindCS
to http://www.hack58.net/Article/html/3/7/2008/mailt [email protected]

--From Norecovery or
--standby mode recovery database is available
Restore Database Northwindcs_bak
With Recovery

--View the backup set in the target backup
Restore headeronly
From Disk = ' G:\Backup\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 Set password protected backup
Backup Database NorthwindCS
To disk= ' G:\Backup\NorthwindCS_Full_20070908.bak '
with Password = ' 123 ', init
Restore Database NorthwindCS
From disk= ' G:\Backup\NorthwindCS_Full_20070908.bak '
with Password = ' 123 '

About automatic remote database backups for SQL Server 2005