sql server 2005 的卸载和自动备份说明

不会起 發表於 2008-9-8 19:44:12

sql server 2005 的卸载和自动备份说明

卸载：执行命令 
c:\sqlserver2005\Setup.exe /qb REMOVE=ALL INSTANCENAME=<InstanceName> 
其中，setup.exe是SQLEXPR_ADV.EXE解压缩之后的<a target="_blank" href="#" class="UBBWordLink">安装</a>程序。 
 
之所以这么做，是因为在控制面板卸载时不干净，sql的实例都还在。用这个<a target="_blank" href="#" class="UBBWordLink">方法</a>最好。 
 
自动备份： 
原文：<a href="http://www.mssqltips.com/tip.asp?tip=1174">http://www.mssqltips.com/tip.asp?tip=1174</a> 
思路： 
  1：生成一个自动备份的sql脚本，一个名叫 sp_BackupDatabase 的存贮过程。 
 
1<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>USE [master]   
 2<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>GO   
 3<img src="https://img.jbzj.com/do/upimg/080908/1220W4252C020311063.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/><imgstyle="display: none"src="https://img.jbzj.com/do/upimg/080908/1220W4252XT032K94.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/><spanstyle="border-right: #808080 1px solid; border-top: #808080 1px solid; display: none; border-left: #808080 1px solid; border-bottom: #808080 1px solid; background-color: #ffffff">/**//****** Object:  StoredProcedure .    Script Date: 02/07/2007 11:40:47 ******/   
 4<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>SET ANSI_NULLS ON   
 5<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>GO   
 6<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>SET QUOTED_IDENTIFIER ON   
 7<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>GO   
 8<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/> 
 9<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/> 
10<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>-- =============================================   
11<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>-- Author: Edgewood Solutions   
12<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>-- Create date: 2007-02-07   
13<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>-- Description: Backup Database   
14<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>-- Parameter1: databaseName   
15<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>-- Parameter2: backupType F=full, D=differential, L=log 
16<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>-- =============================================   
17<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>CREATE PROCEDURE [dbo].[sp_BackupDatabase]    
18<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>       @databaseName sysname, @backupType CHAR(1)   
19<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>AS   
20<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>BEGIN   
21<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>       SET NOCOUNT ON;   
22<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/> 
23<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>       DECLARE @sqlCommand NVARCHAR(1000)   
24<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>       DECLARE @dateTime NVARCHAR(20)   
25<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/> 
26<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>       SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +   
27<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>       REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')    
28<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/> 
29<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>       IF @backupType = 'F'   
30<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +   
31<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>               ' TO DISK = ''C:\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'''   
32<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>          
33<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>       IF @backupType = 'D'   
34<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +   
35<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>               ' TO DISK = ''C:\Backup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'   
36<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>          
37<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>       IF @backupType = 'L'   
38<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>               SET @sqlCommand = 'BACKUP LOG ' + @databaseName +   
39<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>               ' TO DISK = ''C:\Backup\' + @databaseName + '_Log_' + @dateTime + '.TRN'''   
40<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>          
41<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>       EXECUTE sp_executesql @sqlCommand   
42<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>END

  注意，要编译一下。 
 
  2：调用这个存储过程的一个sql脚本2。这个脚本可以被外部的程序调用，使用sqlcmd.exe来解释执行。 
      
1<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>sp_BackupDatabase 'master', 'F' 
2<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>GO 
3<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>sp_BackupDatabase 'model', 'F' 
4<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>GO 
5<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>sp_BackupDatabase 'msdb', 'F' 
6<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>GO 
7<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>QUIT
 
  3：<a target="_blank" href="#" class="UBBWordLink">设置</a>定时任务，每天执行上述脚本2. 
 
<img src="https://img.jbzj.com/do/upimg/080908/1220W42523LP306339.gif" align="top" alt=""onload="return imgzoom(this,700);" onclick="javascript:window.open(this.src);" style="cursor: pointer;"/>sqlcmd -S serverName -E -i C:\Backup\脚本2.sql
      上述语句是计划任务的“Run”里面的参数。

頁: [1]

琼殿技术论坛's Archiver

sql server 2005 的卸载和自动备份 说明

sql server 2005 的卸载和自动备份说明