SQL Server database backup to servers, and delete

One: Backup

1. Create a new maintenance plan under Database management, then click the Red button to create a new scheduled job, and then you can see the newly created job in the job under SQL Server Agent.

Jobs under 2.SqlServer Agent open new Job Click Step Edit or add (usually with default) type select T-SQL,

3. Copy the code in, change your server connection (user:192.168.1.11), account (Administrator), password (admin123), disk directory (\ \ 192.168.1.111\bk\111_sqlbak).

A: The following scenario is an unexpected backup of all but the system database.

execMaster.. xp_cmdshell'net use \\192.168.1.111\bk\111_SQLBAK admin123/user:192.168.1.111\administrator', No_outputGoDeclare @s nvarchar( $),@del nvarchar( $)Select  @s="',@del="'DeclareDatebakcursor  forSelect      [Bak]='Backup Database'+QuoteName(Name)+'to disk ="'\\192.168.1.111\bk\111_SQLBAK\'+Name+'('+Convert(varchar(Ten),getdate(), -)+')'+'. bak"'With Init',     [del]='exec master. xp_cmdshell"'del \\192.168.1.111\bk\111_SQLBAK\'+Name+'('+Convert(varchar(Ten),getdate()-4, -)+')'+'. bak"', No_output'  fromMaster.. sysdatabaseswheredbid>4 --system Database not backed upOpenDatebakFetch Next  fromDatebak into @s,@del while @ @fetch_status=0    begin        exec(@del)        exec(@s)        Fetch Next  fromDatebak into @s,@del    EndCloseDatebakdeallocateDatebakGoexecMaster.. xp_cmdshell'net use \\192.168.1.111\bk\111_sqlbak/delete'Go

B: Back up only one database, specifying the database name.

Declare @DBName nvarchar(Max)Declare @BakName nvarchar(Max)--Modify the database name hereSelect @DBName='Sqlname'--Specify the save path \\192.168.1.111\BKSelect @BakName='\\192.168.1.111\bk\'+REPLACE(@DBName,'.','_')+'-('+Convert(Char(Ten),getdate(), -)+')'+'. bak'--Enable network mapping diskexecMaster.. xp_cmdshell'net use \\192.168.1.111\BK admin123/user:192.168.1.111\administrator'--Local Disk--Select @BakName = ' D:\dbbak\ ' +replace (@DBName, '. ', ' _ ') +convert (char), GETDATE (), +) + '. Bak 'Declare @BakSQL nvarchar(Max)Select @BakSQL='Backup Database'+@DBName+'To disk=" "+@BakName+" "With init,nounload'Print @BakSQLexec(@BakSQL)execMaster.. xp_cmdshell'net use \\192.168.1.111\bk/delete'

4: Once saved, it will run according to the time of your new maintenance plan. ( Note To open the SQL Server Agent service, set the automatic.) )

Two: Delete

1: First map the server backup directory to the local site character (\\192.168.1.111\bk),

2: Create a new maintenance plan and delete the server for more than 4 days of backup.

Right-click Maintenance plan click the Maintenance Plan Wizard and select the Purge maintenance task.

3: Select your mapped drive letter, extension default. bak

After the save, the basic is finished.

SQL Server database backup to servers, and delete