首頁 資料庫 mysql教程 SQLServer 批量备份与还原

SQLServer 批量备份与还原

Jun 07, 2016 pm 03:25 PM
sqlserver 備份 大量 資料庫 還原

备份与还原是数据库避不开的主题,而作为DBA,经常会面临将一台机器上的所有数据库重新构建到一台新机器上的要求; 在现在都讲究自动化管理的时代,传统的界面操作备份还原的做法不仅浪费时间和精力,而且还很容易出错,并且这次完成后, 下次再有这样的要求

    备份与还原是数据库避不开的主题,而作为DBA,经常会面临将一台机器上的所有数据库重新构建到一台新机器上的要求;

在现在都讲究自动化管理的时代,传统的界面操作备份还原的做法不仅浪费时间和精力,而且还很容易出错,并且这次完成后,

下次再有这样的要求,必须又重头开始(估计做5次就能做得人狂吐);于是,我们需要一套应对这种频繁操作、耗时、耗精力

的通用处理方法,所以以下批处理脚本就诞生了。

     脚本主要的功能:

     1. 备份一个服务器上的所有数据库(当然你也可以选择),备份文件按数据库名+日期生成,以.bak 结尾;

     2. 将所有的备份文件还原到一台新机器上;

     3. 验证磁盘和路径的正确性;

    说明:

     脚本合适 SQLServer 2005 & 2008 版本;

 

 批量备份数据库:

<span>--</span><span>---------------------------批量备份数据-------------------------------------------</span><span><br></span><span>Use</span><span> master<br></span><span>GO</span><span><br></span><span>/*</span><span>=================Usp_BackUp_DataBase========================<br>  =====BackUp Sigle DataBase                            ======<br>  =====Ken.Guo                                          ======<br>  =====2010.9.10                                         ======<br>  =====Version: 2005 & 2008 SQL Server                  ======<br>  =====EXEC Usp_BackUp_DataBase 'MyDB','D:\BackUp'      ======<br>  ============================================================<br></span><span>*/</span><span><br></span><span>CREATE</span><span>PROC</span><span>[</span><span>dbo</span><span>]</span><span>.</span><span>[</span><span>Usp_BackUp_DataBase</span><span>]</span><span>@DatabaseName</span><span>nvarchar</span><span>(</span><span>200</span><span>),</span><span>@Path</span><span>nvarchar</span><span>(</span><span>200</span><span>)   <br></span><span>AS</span><span>  <br>  </span><span>BEGIN</span><span> <br>  </span><span>DECLARE</span><span>@fn</span><span>varchar</span><span>(</span><span>200</span><span>)<br>           ,</span><span>@sql</span><span>varchar</span><span>(</span><span>1000</span><span>)   <br>  </span><span>SET</span><span>@fn</span><span>=</span><span>@Path</span><span>+</span><span>(</span><span>case</span><span>when</span><span>right</span><span>(</span><span>@Path</span><span>,</span><span>1</span><span>)   </span><span></span><span>'</span><span>\</span><span>'</span><span>then</span><span>'</span><span>\</span><span>'</span><span>else</span><span>''</span><span>end</span><span>)   <br>  </span><span>+</span><span>@DatabaseName</span><span>+</span><span>'</span><span>_</span><span>'</span><span>   <br>  </span><span>+</span><span>convert</span><span>(</span><span>char</span><span>(</span><span>8</span><span>),</span><span>getdate</span><span>(),</span><span>112</span><span>)</span><span>+</span><span>'</span><span>_</span><span>'</span><span>   <br> </span><span>+</span><span>replace</span><span>(</span><span>convert</span><span>(</span><span>char</span><span>(</span><span>8</span><span>),</span><span>getdate</span><span>(),</span><span>108</span><span>),</span><span>'</span><span>:</span><span>'</span><span>,</span><span>''</span><span>)   <br>  </span><span>+</span><span>'</span><span>.bak</span><span>'</span><span>   <br>  </span><span>set</span><span>@sql</span><span>=</span><span>'</span><span>backup   database   </span><span>'</span><span>+</span><span>@DatabaseName</span><span>+</span><span>'</span><span>   to   disk   =   N</span><span>'''</span><span>+</span><span>@fn</span><span>+</span><span>''''</span><span>   <br>  </span><span>--</span><span>SELECT @sql </span><span><br></span><span>EXEC</span><span>(</span><span>@sql</span><span>)  <br>  </span><span>END</span><span><br><br></span><span>GO</span><span><br><br></span><span>Use</span><span> master<br></span><span>GO</span><span><br></span><span>/*</span><span>=============BackUp Mutile DataBase=========================</span><span>*/</span><span><br></span><span>DECLARE</span><span>@dbname</span><span>nvarchar</span><span>(</span><span>200</span><span>)<br>       ,</span><span>@backup_path</span><span>nvarchar</span><span>(</span><span>200</span><span>)<br></span><span>SET</span><span>@backup_path</span><span>=</span><span>'</span><span>D:\BackUp\</span><span>'</span><span><br></span><span>DECLARE</span><span> db_info </span><span>CURSOR</span><span> <br>    LOCAL <br>    STATIC <br>    READ_ONLY <br>    FORWARD_ONLY <br></span><span>FOR</span><span>--</span><span>根据查询,添加其他筛选条件</span><span><br></span><span>SELECT</span><span> <br>      name <br>  </span><span>FROM</span><span> master.sys.databases </span><span>WITH</span><span>(NOLOCK) <br>  </span><span>WHERE</span><span> <br>      database_id</span><span>></span><span>4</span><span><br><br></span><span>OPEN</span><span> db_info<br></span><span>FETCH</span><span>NEXT</span><span>FROM</span><span> db_info </span><span>INTO</span><span>@dbname</span><span><br><br></span><span>WHILE</span><span>@@FETCH_STATUS</span><span>=</span><span>0</span><span><br> </span><span>begin</span><span><br>  </span><span>EXEC</span><span> master.dbo.<span>Usp_BackUp_DataBase </span></span><span>@dbname</span><span>,</span><span>@backup_path</span><span><br>  </span><span>FETCH</span><span>NEXT</span><span>FROM</span><span> db_info </span><span>INTO</span><span>@dbname</span><span><br> </span><span>END</span><span><br></span><span>close</span><span> db_info<br></span><span>deallocate</span><span> db_info<br><br></span><span>--</span><span>-------------------------------BackUp DataBase End------------------------------------</span>
登入後複製

检查还原磁盘:

<span>Use</span><span> master<br></span><span>GO</span><span><br></span><span>/*</span><span>=================Check Restore Path Drives Exists==========================<br>  =====Ken.Guo                                                         ======<br>  =====2010.9.10                                                        ======<br>  =====EXEC Usp_Check_DriveExists @RestoreDataPath,@ResultCount OUTPUT ======<br>  ===========================================================================<br></span><span>*/</span><span><br></span><span>CREATE</span><span>PROC</span><span> Usp_Check_DriveExists(<br>      </span><span>@RestoreDataPath</span><span>nvarchar</span><span>(</span><span>200</span><span>)<br>     ,</span><span>@ResultCount</span><span>int</span><span> OUTPUT) <br></span><span>AS</span><span><br></span><span>BEGIN</span><span><br></span><span>--</span><span>Check Restore Path and Size >1000M</span><span><br></span><span>if</span><span>CHARINDEX</span><span>(</span><span>'</span><span>:</span><span>'</span><span>,</span><span>@RestoreDataPath</span><span>)</span><span>></span><span>0</span><span><br>  </span><span>begin</span><span><br>    </span><span>DECLARE</span><span>@Drive</span><span>nvarchar</span><span>(</span><span>10</span><span>)<br>           ,</span><span>@errorinfo</span><span>nvarchar</span><span>(</span><span>500</span><span>)<br><br>    </span><span>DECLARE</span><span>@DriveList</span><span>TABLE</span><span> <br>    (    <br>         Drive </span><span>nvarchar</span><span>(</span><span>10</span><span>) <br>        ,DSize </span><span>bigint</span><span> <br>    )<br>    </span><span>INSERT</span><span>INTO</span><span>@DriveList</span><span><br>     </span><span>EXEC</span><span> master.dbo.xp_fixeddrives<br><br>    </span><span>SET</span><span>@Drive</span><span>=Left</span><span>(</span><span>@RestoreDataPath</span><span>,</span><span>CHARINDEX</span><span>(</span><span>'</span><span>:</span><span>'</span><span>,</span><span>@RestoreDataPath</span><span>)</span><span>-</span><span>1</span><span>)<br>    </span><span>if</span><span>not</span><span>exists</span><span>(</span><span>SELECT</span><span> <br>                      </span><span>*</span><span> <br>                  </span><span>FROM</span><span>@DriveList</span><span> <br>                  </span><span>WHERE</span><span> <br>                      Drive</span><span>=</span><span>@Drive</span><span> <br>                      </span><span>AND</span><span> DSize</span><span>></span><span>1024</span><span><br>               <br>               )<br>      </span><span>begin</span><span><br>       </span><span>set</span><span>@errorinfo</span><span>=</span><span>N</span><span>'</span><span>找不到还原磁盘:</span><span>'</span><span>+</span><span>@Drive</span><span>+</span><span>N</span><span>'</span><span> ,或者磁盘剩余空间小于1G</span><span>'</span><span><br>       </span><span>RAISERROR</span><span>50001</span><span>@errorinfo</span><span> <br>       </span><span>set</span><span>@ResultCount</span><span>=</span><span>0</span><span><br>       </span><span>return</span><span><br>      </span><span>end</span><span><br>  </span><span>end</span><span><br></span><span>else</span><span>if</span><span>(</span><span>LEN</span><span>(</span><span>@RestoreDataPath</span><span>)</span><span>></span><span>1</span><span>) </span><span>AND</span><span>CHARINDEX</span><span>(</span><span>'</span><span>:</span><span>'</span><span>,</span><span>@RestoreDataPath</span><span>)</span><span>=</span><span>0</span><span><br>  </span><span>begin</span><span><br>    </span><span>set</span><span>@errorinfo</span><span>=</span><span>N</span><span>'</span><span>还原路径错误:</span><span>'</span><span>+</span><span>@RestoreDataPath</span><span>+</span><span>N</span><span>'</span><span>,必须包含":" 号</span><span>'</span><span><br>    </span><span>Raiserror</span><span>50001</span><span>@errorinfo</span><span>   <br>    </span><span>set</span><span>@ResultCount</span><span>=</span><span>0</span><span><br>    </span><span>return</span><span> <br>  </span><span>end</span><span><br> </span><span>set</span><span>@ResultCount</span><span>=</span><span>1</span><span><br></span><span>end</span><span><br></span><span>GO</span>
登入後複製

还原单个数据库:

<span>Use</span><span> master<br></span><span>GO</span><span><br></span><span>/*</span><span>=================Usp_RestoreDataBaseFormPath=======================================<br>  =====Restore Single DataBase From a Back File                                ======<br>  =====Ken.Guo                                                                 ======<br>  =====2010.9.10                                                                ======<br>  =====Version: 2005 & 2008 SQL Server                                         ======<br>  =====Usp_RestoreDataBaseFormPath 'D:\databack\dbcenter.bak','D:\Data',0      ======<br>  =====Key Point Info:                                                         ======<br>  --Restore HeaderOnly  from disk='D:\data\xx.bak'<br>  --Restore FileListOnly from disk='D:\data\xx.bak'<br>  ===================================================================================<br></span><span>*/</span><span><br></span><span>CREATE</span><span>PROC</span><span> Usp_RestoreDataBaseFormPath<br>(</span><span>@DatabBaseBakPath</span><span>nvarchar</span><span>(</span><span>400</span><span>),<br> </span><span>@RestoreDataPath</span><span>nvarchar</span><span>(</span><span>400</span><span>)</span><span>=</span><span>''</span><span>,  </span><span>--</span><span>RESTORE DATABASE PATH </span><span><br></span><span>@IsRun</span><span>smallint</span><span>=</span><span>0</span><span>--</span><span> 0 PRINT  1 run </span><span><br></span><span>) <br></span><span>AS</span><span><br></span><span>BEGIN</span><span><br></span><span>set</span><span> nocount </span><span>on</span><span><br><br></span><span>declare</span><span>@dbname</span><span>nvarchar</span><span>(</span><span>200</span><span>),</span><span>@SQL</span><span>nvarchar</span><span>(</span><span>4000</span><span>),</span><span>@DirSQL</span><span>nvarchar</span><span>(</span><span>1000</span><span>),</span><span>@errorinfo</span><span>nvarchar</span><span>(</span><span>300</span><span>)<br></span><span>--</span><span>add path \</span><span><br></span><span>if</span><span> (</span><span>@RestoreDataPath</span><span>is</span><span>not</span><span>null</span><span>) </span><span>and</span><span>len</span><span>(</span><span>@RestoreDataPath</span><span>)</span><span>></span><span>1</span><span> <br>   </span><span>and</span><span> (</span><span>right</span><span>(</span><span>@RestoreDataPath</span><span>,</span><span>1</span><span>)</span><span></span><span>'</span><span>\</span><span>'</span><span>)<br>   </span><span>set</span><span>@RestoreDataPath</span><span>=</span><span>@RestoreDataPath</span><span>+</span><span>'</span><span>\</span><span>'</span><span><br><br></span><span>declare</span><span>@checkdrive</span><span>int</span><span><br></span><span>set</span><span>@checkdrive</span><span>=</span><span>1</span><span><br> </span><span>exec</span><span> master.dbo.Usp_Check_DriveExists </span><span>@RestoreDataPath</span><span>,</span><span>@checkdrive</span><span> output<br><br> </span><span>if</span><span>(</span><span>@checkdrive</span><span></span><span>1</span><span>)<br>    </span><span>Goto</span><span> ExitFLag <br><br></span><span>DECLARE</span><span>@BakFileList</span><span>TABLE</span><span> <br>    (    LogicalName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,PhysicalName </span><span>nvarchar</span><span>(</span><span>260</span><span>)<br>    )<br><br></span><span>DECLARE</span><span>@BakHeaderInfo</span><span>TABLE</span><span><br>    (<br>        DatabaseName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>    )<br><br></span><span>if</span><span>Charindex</span><span>(</span><span>'</span><span>Microsoft SQL Server 2008</span><span>'</span><span>,</span><span>@@VERSION</span><span>)</span><span>></span><span>0</span><span><br>  </span><span>begin</span><span><br>    </span><span>--</span><span>SQL Server 2008    </span><span><br></span><span>DECLARE</span><span>@BakFileList2008</span><span>TABLE</span><span> <br>    (    LogicalName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,PhysicalName </span><span>nvarchar</span><span>(</span><span>260</span><span>)<br>        ,Type </span><span>char</span><span>(</span><span>1</span><span>)<br>        ,FileGroupName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,SIZE numeric(</span><span>20</span><span>,</span><span>0</span><span>)<br>        ,MaxSize numeric(</span><span>20</span><span>,</span><span>0</span><span>)<br>        ,FileID </span><span>bigint</span><span><br>        ,CreateLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>)<br>        ,DropLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>) </span><span>NULL</span><span><br>        ,UniqueID </span><span>uniqueidentifier</span><span><br>        ,ReadOnlyLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>) </span><span>NULL</span><span><br>        ,ReadWriteLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>) </span><span>NULL</span><span><br>        ,BackupSizeInBytes </span><span>bigint</span><span><br>        ,SourceBlockSize </span><span>int</span><span><br>        ,FileGroupID </span><span>int</span><span><br>        ,LogGroupGUID </span><span>uniqueidentifier</span><span>NULL</span><span><br>        ,DifferentialBaseLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>) </span><span>NULL</span><span><br>        ,DifferentialBaseGUID </span><span>uniqueidentifier</span><span><br>        ,IsReadOnly </span><span>bit</span><span><br>        ,IsPresent </span><span>bit</span><span><br>        ,TDEThumbprint </span><span>varbinary</span><span>(</span><span>32</span><span>)<br>      )    <br>    <br>     </span><span>INSERT</span><span>INTO</span><span>@BakFileList2008</span><span>        <br>       </span><span>EXEC</span><span> sp_executesql N</span><span>'</span><span>Restore FileListOnly From Disk=@DatabBaseBakPath</span><span>'</span><span>,N</span><span>'</span><span>@DatabBaseBakPath nvarchar(260)</span><span>'</span><span>,</span><span>@DatabBaseBakPath</span><span> <br><br>    </span><span>DECLARE</span><span>@BakHeaderInfo2008</span><span>TABLE</span><span><br>    (<br>         BackupName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,BackupDescription </span><span>nvarchar</span><span>(</span><span>255</span><span>)<br>        ,BackupType </span><span>smallint</span><span><br>        ,ExpirationDate </span><span>datetime</span><span><br>        ,Compressed </span><span>tinyint</span><span><br>        ,POSITION </span><span>smallint</span><span><br>        ,DeviceType </span><span>tinyint</span><span><br>        ,UserName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,ServerName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,DatabaseName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,DatabaseVersion </span><span>int</span><span><br>        ,DatabaseCreationDate </span><span>datetime</span><span><br>        ,BackupSize numeric(</span><span>20</span><span>,</span><span>0</span><span>)<br>        ,FirstLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>)<br>        ,LastLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>)<br>        ,CheckpointLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>)<br>        ,DatabaseBackupLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>)<br>        ,BackupStartDate </span><span>datetime</span><span><br>        ,BackupFinishDate </span><span>datetime</span><span><br>        ,SortOrder </span><span>smallint</span><span><br>        ,CodePage </span><span>smallint</span><span><br>        ,UnicodeLocaleId </span><span>int</span><span><br>        ,UnicodeComparisonStyle </span><span>int</span><span><br>        ,CompatibilityLevel </span><span>tinyint</span><span><br>        ,SoftwareVendorId </span><span>int</span><span><br>        ,SoftwareVersionMajor </span><span>int</span><span><br>        ,SoftwareVersionMinor </span><span>int</span><span><br>        ,SoftwareVersionBuild </span><span>int</span><span><br>        ,MachineName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,Flags </span><span>int</span><span><br>        ,BindingID </span><span>uniqueidentifier</span><span><br>        ,RecoveryForkID </span><span>uniqueidentifier</span><span><br>        ,COLLATION </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,FamilyGUID </span><span>uniqueidentifier</span><span><br>        ,HasBulkLoggedData </span><span>bit</span><span><br>        ,IsSnapshot </span><span>bit</span><span><br>        ,IsReadOnly </span><span>bit</span><span><br>        ,IsSingleUser </span><span>bit</span><span><br>        ,HasBackupChecksums </span><span>bit</span><span><br>        ,IsDamaged </span><span>bit</span><span><br>        ,BeginsLogChain </span><span>bit</span><span><br>        ,HasIncompleteMetaData </span><span>bit</span><span><br>        ,IsForceOffline </span><span>bit</span><span><br>        ,IsCopyOnly </span><span>bit</span><span><br>        ,FirstRecoveryForkID </span><span>uniqueidentifier</span><span><br>        ,ForkPointLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>) </span><span>NULL</span><span><br>        ,RecoveryModel </span><span>nvarchar</span><span>(</span><span>60</span><span>)<br>        ,DifferentialBaseLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>) </span><span>NULL</span><span><br>        ,DifferentialBaseGUID </span><span>uniqueidentifier</span><span><br>        ,BackupTypeDescription </span><span>nvarchar</span><span>(</span><span>60</span><span>)<br>        ,BackupSetGUID </span><span>uniqueidentifier</span><span>NULL</span><span><br>        ,CompressedBackupSize numeric(</span><span>20</span><span>,</span><span>0</span><span>)<br>    )           <br><br>    </span><span>INSERT</span><span>INTO</span><span>@BakHeaderInfo2008</span><span>        <br>       </span><span>EXEC</span><span> sp_executesql N</span><span>'</span><span>Restore HeaderOnly From Disk=@DatabBaseBakPath</span><span>'</span><span>,N</span><span>'</span><span>@DatabBaseBakPath nvarchar(260)</span><span>'</span><span>,</span><span>@DatabBaseBakPath</span><span> <br>    <br>    </span><span>insert</span><span>into</span><span>@BakHeaderInfo</span><span>(DatabaseName)<br>    </span><span>select</span><span> DatabaseName </span><span>from</span><span>@BakHeaderInfo2008</span><span><br><br>    </span><span>insert</span><span>into</span><span>@BakFileList</span><span>(LogicalName ,PhysicalName)<br>    </span><span>select</span><span>  LogicalName ,PhysicalName </span><span>from</span><span>@BakFileList2008</span><span><br>  </span><span>end</span><span><br></span><span>else</span><span><br>  </span><span>begin</span><span><br>    </span><span>--</span><span>SQL Server 2005    </span><span><br></span><span>DECLARE</span><span>@BakFileList2005</span><span>TABLE</span><span> <br>    (<br>         LogicalName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,PhysicalName </span><span>nvarchar</span><span>(</span><span>260</span><span>)<br>        ,Type </span><span>char</span><span>(</span><span>1</span><span>)<br>        ,FileGroupName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,SIZE numeric(</span><span>20</span><span>,</span><span>0</span><span>)<br>        ,MaxSize numeric(</span><span>20</span><span>,</span><span>0</span><span>)<br>        ,FileID </span><span>bigint</span><span><br>        ,CreateLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>)<br>        ,DropLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>) </span><span>NULL</span><span><br>        ,UniqueID </span><span>uniqueidentifier</span><span><br>        ,ReadOnlyLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>) </span><span>NULL</span><span><br>        ,ReadWriteLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>) </span><span>NULL</span><span><br>        ,BackupSizeInBytes </span><span>bigint</span><span><br>        ,SourceBlockSize </span><span>int</span><span><br>        ,FileGroupID </span><span>int</span><span><br>        ,LogGroupGUID </span><span>uniqueidentifier</span><span>NULL</span><span><br>        ,DifferentialBaseLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>) </span><span>NULL</span><span><br>        ,DifferentialBaseGUID </span><span>uniqueidentifier</span><span><br>        ,IsReadOnly </span><span>bit</span><span><br>        ,IsPresent </span><span>bit</span><span><br>    )    <br><br>    </span><span>INSERT</span><span>INTO</span><span>@BakFileList2005</span><span>      <br>        </span><span>EXEC</span><span> sp_executesql N</span><span>'</span><span>Restore FileListOnly From Disk=@DatabBaseBakPath</span><span>'</span><span>,N</span><span>'</span><span>@DatabBaseBakPath nvarchar(260)</span><span>'</span><span>,</span><span>@DatabBaseBakPath</span><span> <br>  <br>    </span><span>DECLARE</span><span>@BakHeaderInfo2005</span><span>TABLE</span><span> <br>    (<br>         BackupName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,BackupDescription </span><span>nvarchar</span><span>(</span><span>255</span><span>)<br>        ,BackupType </span><span>smallint</span><span><br>        ,ExpirationDate </span><span>datetime</span><span><br>        ,Compressed </span><span>tinyint</span><span><br>        ,POSITION </span><span>smallint</span><span><br>        ,DeviceType </span><span>tinyint</span><span><br>        ,UserName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,ServerName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,DatabaseName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,DatabaseVersion </span><span>int</span><span><br>        ,DatabaseCreationDate </span><span>datetime</span><span><br>        ,BackupSize numeric(</span><span>20</span><span>,</span><span>0</span><span>)<br>        ,FirstLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>)<br>        ,LastLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>)<br>        ,CheckpointLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>)<br>        ,DatabaseBackupLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>)<br>        ,BackupStartDate </span><span>datetime</span><span><br>        ,BackupFinishDate </span><span>datetime</span><span><br>        ,SortOrder </span><span>smallint</span><span><br>        ,CodePage </span><span>smallint</span><span><br>        ,UnicodeLocaleId </span><span>int</span><span><br>        ,UnicodeComparisonStyle </span><span>int</span><span><br>        ,CompatibilityLevel </span><span>tinyint</span><span><br>        ,SoftwareVendorId </span><span>int</span><span><br>        ,SoftwareVersionMajor </span><span>int</span><span><br>        ,SoftwareVersionMinor </span><span>int</span><span><br>        ,SoftwareVersionBuild </span><span>int</span><span><br>        ,MachineName </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,Flags </span><span>int</span><span><br>        ,BindingID </span><span>uniqueidentifier</span><span><br>        ,RecoveryForkID </span><span>uniqueidentifier</span><span><br>        ,COLLATION </span><span>nvarchar</span><span>(</span><span>128</span><span>)<br>        ,FamilyGUID </span><span>uniqueidentifier</span><span><br>        ,HasBulkLoggedData </span><span>bit</span><span><br>        ,IsSnapshot </span><span>bit</span><span><br>        ,IsReadOnly </span><span>bit</span><span><br>        ,IsSingleUser </span><span>bit</span><span><br>        ,HasBackupChecksums </span><span>bit</span><span><br>        ,IsDamaged </span><span>bit</span><span><br>        ,BeginsLogChain </span><span>bit</span><span><br>        ,HasIncompleteMetaData </span><span>bit</span><span><br>        ,IsForceOffline </span><span>bit</span><span><br>        ,IsCopyOnly </span><span>bit</span><span><br>        ,FirstRecoveryForkID </span><span>uniqueidentifier</span><span><br>        ,ForkPointLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>) </span><span>NULL</span><span><br>        ,RecoveryModel </span><span>nvarchar</span><span>(</span><span>60</span><span>)<br>        ,DifferentialBaseLSN numeric(</span><span>25</span><span>,</span><span>0</span><span>) </span><span>NULL</span><span><br>        ,DifferentialBaseGUID </span><span>uniqueidentifier</span><span><br>        ,BackupTypeDescription </span><span>nvarchar</span><span>(</span><span>60</span><span>)<br>        ,BackupSetGUID </span><span>uniqueidentifier</span><span>NULL</span><span><br>    )    <br><br>    </span><span>INSERT</span><span>INTO</span><span>@BakHeaderInfo2005</span><span>        <br>        </span><span>EXEC</span><span> sp_executesql N</span><span>'</span><span>Restore HeaderOnly From Disk=@DatabBaseBakPath</span><span>'</span><span>,N</span><span>'</span><span>@DatabBaseBakPath nvarchar(260)</span><span>'</span><span>,</span><span>@DatabBaseBakPath</span><span> <br><br>    </span><span>insert</span><span>into</span><span>@BakHeaderInfo</span><span>(DatabaseName)<br>    </span><span>select</span><span> DatabaseName </span><span>from</span><span>@BakHeaderInfo2005</span><span><br><br>    </span><span>insert</span><span>into</span><span>@BakFileList</span><span>(LogicalName ,PhysicalName)<br>    </span><span>select</span><span>  LogicalName ,PhysicalName </span><span>from</span><span>@BakFileList2005</span><span><br><br>  </span><span>end</span><span><br><br></span><span>--</span><span>Check back file info</span><span><br></span><span>if</span><span>not</span><span>exists</span><span> (</span><span>select</span><span>1</span><span>from</span><span>@BakFileList</span><span>) </span><span>OR</span><span>not</span><span>exists</span><span> (</span><span>select</span><span>1</span><span>from</span><span>@BakHeaderInfo</span><span>)<br> </span><span>begin</span><span><br>   </span><span>set</span><span>@errorinfo</span><span>=</span><span>N</span><span>'</span><span>取不到备份文件:</span><span>'</span><span>+</span><span>@DatabBaseBakPath</span><span>+</span><span>N</span><span>'</span><span> 的信息,请检查备份文件是否正确或者版本是否兼容</span><span>'</span><span><br>   </span><span>Raiserror</span><span>50001</span><span>@errorinfo</span><span>    <br>   </span><span>Goto</span><span> ExitFLag<br> </span><span>end</span><span><br><br></span><span>--</span><span>Get DataBase Name</span><span><br></span><span>SELECT</span><span>TOP</span><span>1</span><span>@dbname</span><span>=</span><span>databasename </span><span>FROM</span><span>@BakHeaderInfo</span><span><br><br></span><span>if</span><span>exists</span><span> (</span><span>select</span><span>1</span><span>from</span><span> master.sys.databases </span><span>with</span><span>(nolock) </span><span>where</span><span> name</span><span>=</span><span>@dbname</span><span>)<br>     </span><span>begin</span><span><br>       <br>       </span><span>set</span><span>@errorinfo</span><span>=</span><span>N</span><span>'</span><span>数据库:</span><span>'</span><span>+</span><span>@dbname</span><span>+</span><span>N</span><span>'</span><span>已经存在,不能还原</span><span>'</span><span> <br>       </span><span>Raiserror</span><span>50001</span><span>@errorinfo</span><span>  <br>       </span><span>Goto</span><span> ExitFLag<br>     </span><span>end</span><span><br><br></span><span>DECLARE</span><span>@LogicalName</span><span>nvarchar</span><span>(</span><span>200</span><span>),</span><span>@PhysicalName</span><span>nvarchar</span><span>(</span><span>400</span><span>)<br>       ,</span><span>@pos</span><span>int</span><span> ,</span><span>@endpos</span><span>int</span><span>,</span><span>@LastPhysicalName</span><span>nvarchar</span><span>(</span><span>400</span><span>)<br><br></span><span>DECLARE</span><span> db_file </span><span>CURSOR</span><span> <br>    LOCAL <br>    READ_ONLY <br>    FORWARD_ONLY <br>    STATIC <br></span><span>FOR</span><span><br> </span><span>SELECT</span><span> <br>     LogicalName<br>    ,PhysicalName  <br> </span><span>FROM</span><span>@BakFileList</span><span><br><br></span><span>OPEN</span><span> db_file<br><br></span><span>set</span><span>@DirSQL</span><span>=</span><span>''</span><span><br></span><span>set</span><span>@SQL</span><span>=+</span><span>N</span><span>'</span><span>RESTORE DATABASE </span><span>'</span><span>+</span><span>QUOTENAME</span><span>(</span><span>@dbname</span><span>)</span><span>+</span><span>'</span><span> from disk=N</span><span>'''</span><span>+</span><span>@DatabBaseBakPath</span><span>+</span><span>''''</span><span><br></span><span>set</span><span>@SQL</span><span>=</span><span>@SQL</span><span>+</span><span>char</span><span>(</span><span>13</span><span>)</span><span>+</span><span>Char</span><span>(</span><span>10</span><span>)</span><span>+</span><span>N</span><span>'</span><span> WITH FILE=1 </span><span>'</span><span><br><br></span><span>FETCH</span><span>NEXT</span><span>FROM</span><span> db_file </span><span>INTO</span><span>@LogicalName</span><span>,</span><span>@PhysicalName</span><span><br><br></span><span>WHILE</span><span>@@FETCH_STATUS</span><span>=</span><span>0</span><span><br> </span><span>begin</span><span><br>   </span><span>--</span><span>-Get DB PhysicalName</span><span><br></span><span>set</span><span>@endpos</span><span>=</span><span>0</span><span><br>   </span><span>while</span><span>CHARINDEX</span><span>(</span><span>'</span><span>\</span><span>'</span><span>,</span><span>@PhysicalName</span><span>)</span><span>></span><span>0</span><span><br>    </span><span>begin</span><span><br>      </span><span>set</span><span>@pos</span><span>=</span><span>CHARINDEX</span><span>(</span><span>'</span><span>\</span><span>'</span><span>,</span><span>@PhysicalName</span><span>,</span><span>@endpos</span><span>)<br>      </span><span>if</span><span>(</span><span>@pos</span><span>=</span><span>0</span><span>)<br>          </span><span>break</span><span>;<br>      </span><span>set</span><span>@endpos</span><span>=</span><span>@pos</span><span>+</span><span>1</span><span>;<br>    </span><span>end</span><span><br>   <br>   </span><span>--</span><span>create new db path</span><span><br></span><span>if</span><span>(</span><span>len</span><span>(</span><span>@RestoreDataPath</span><span>)</span><span>></span><span>1</span><span>)<br>      </span><span>begin</span><span><br>          </span><span>set</span><span>@PhysicalName</span><span>=</span><span>@RestoreDataPath</span><span>+</span><span>@dbname</span><span>+</span><span>'</span><span>\</span><span>'</span><span>+</span><span>SUBSTRING</span><span>(</span><span>@PhysicalName</span><span>,</span><span>@endpos</span><span>,</span><span>LEN</span><span>(</span><span>@PhysicalName</span><span>)</span><span>-</span><span>@endpos</span><span>+</span><span>1</span><span>)<br>          </span><span>set</span><span>@DirSQL</span><span>=</span><span>N</span><span>'</span><span>EXEC master.sys.xp_create_subdir N</span><span>'''</span><span>+</span><span>@RestoreDataPath</span><span>+</span><span>@dbname</span><span>+</span><span>''''</span><span><br>       </span><span>END</span><span><br>    </span><span>else</span><span><br>      </span><span>begin</span><span><br>        </span><span>if</span><span>len</span><span>(</span><span>@DirSQL</span><span>)</span><span><span>1</span><span>OR</span><span> (</span><span>SUBSTRING</span><span>(</span><span>@PhysicalName</span><span>,</span><span>1</span><span>,</span><span>@endpos</span><span>-</span><span>1</span><span>)</span><span></span><span>@LastPhysicalName</span><span>)<br>          </span><span>if</span><span>(</span><span>len</span><span>(</span><span>@DirSQL</span><span>)</span><span><span>1</span><span>)<br>             </span><span>set</span><span>@DirSQL</span><span>=</span><span>N</span><span>'</span><span>EXEC master.sys.xp_create_subdir N</span><span>'''</span><span>+</span><span>SUBSTRING</span><span>(</span><span>@PhysicalName</span><span>,</span><span>1</span><span>,</span><span>@endpos</span><span>-</span><span>1</span><span>)</span><span>+</span><span>''''</span><span><br>          </span><span>else</span><span><br>           </span><span>set</span><span>@DirSQL</span><span>=</span><span>@DirSQL</span><span>+</span><span>char</span><span>(</span><span>13</span><span>)</span><span>+</span><span>N</span><span>'</span><span>EXEC master.sys.xp_create_subdir N</span><span>'''</span><span>+</span><span>SUBSTRING</span><span>(</span><span>@PhysicalName</span><span>,</span><span>1</span><span>,</span><span>@endpos</span><span>-</span><span>1</span><span>)</span><span>+</span><span>''''</span><span><br>       <br>         </span><span>--</span><span>-Check Drives</span><span><br></span><span>set</span><span>@checkdrive</span><span>=</span><span>1</span><span><br>         </span><span>exec</span><span> master.dbo.Usp_Check_DriveExists </span><span>@PhysicalName</span><span>,</span><span>@checkdrive</span><span> output<br><br>         </span><span>if</span><span>(</span><span>@checkdrive</span><span></span><span>1</span><span>)<br>            </span><span>Goto</span><span> ExitFLag <br><br>        </span><span>set</span><span>@LastPhysicalName</span><span>=</span><span>SUBSTRING</span><span>(</span><span>@PhysicalName</span><span>,</span><span>1</span><span>,</span><span>@endpos</span><span>-</span><span>1</span><span>);<br>      </span><span>END</span><span><br>    <br>    </span><span>set</span><span>@SQL</span><span>=</span><span>@SQL</span><span>+</span><span>char</span><span>(</span><span>13</span><span>)</span><span>+</span><span>Char</span><span>(</span><span>10</span><span>)</span><span>+</span><span>N</span><span>'</span><span> ,Move N</span><span>'''</span><span>+</span><span>@LogicalName</span><span>+</span><span>''''</span><span>+</span><span>'</span><span> TO N</span><span>'''</span><span>+</span><span>@PhysicalName</span><span>+</span><span>''''</span><span><br>    <br>   </span><span>FETCH</span><span>NEXT</span><span>FROM</span><span> db_file </span><span>INTO</span><span>@LogicalName</span><span>,</span><span>@PhysicalName</span><span><br> </span><span>end</span><span><br> </span><span>set</span><span>@SQL</span><span>=</span><span>@SQL</span><span>+</span><span>char</span><span>(</span><span>13</span><span>)</span><span>+</span><span>Char</span><span>(</span><span>10</span><span>)</span><span>+</span><span>N</span><span>'</span><span> ,NOUNLOAD,Recovery,STATS = 10</span><span>'</span><span><br><br></span><span>if</span><span>(</span><span>@IsRun</span><span>=</span><span>0</span><span>)<br>    </span><span>print</span><span>( </span><span>@DirSQL</span><span>+</span><span>char</span><span>(</span><span>13</span><span>)</span><span>+</span><span>char</span><span>(</span><span>10</span><span>)</span><span>+</span><span>'</span><span>GO</span><span>'</span><span>+</span><span>char</span><span>(</span><span>13</span><span>)</span><span>+</span><span>Char</span><span>(</span><span>10</span><span>)</span><span>+</span><span>@SQL</span><span>+</span><span>char</span><span>(</span><span>13</span><span>))<br></span><span>else</span><span><br> </span><span>begin</span><span><br>  </span><span>print</span><span>(</span><span>'</span><span>-----------Begin Restore Database:</span><span>'</span><span>+</span><span>@dbname</span><span>+</span><span>'</span><span>------------------</span><span>'</span><span>)<br>  </span><span>exec</span><span>(</span><span>@DirSQL</span><span>)<br>  </span><span>exec</span><span>(</span><span>@SQL</span><span>)<br>  </span><span>print</span><span>(</span><span>'</span><span>-----------End Restore Database:</span><span>'</span><span>+</span><span>@dbname</span><span>+</span><span>'</span><span>---------------------</span><span>'</span><span>+</span><span>char</span><span>(</span><span>13</span><span>))<br> </span><span>end</span><span><br><br> </span><span>close</span><span> db_file<br> </span><span>deallocate</span><span> db_file<br><br>ExitFLag:<br></span><span>set</span><span> nocount </span><span>off</span><span><br></span><span>end</span></span></span>
登入後複製

批量还原数据库:

<span>Use</span><span> master<br></span><span>GO</span><span><br></span><span>/*</span><span>=================Usp_RestoreMuiteDataBaseFromPath========================<br>  =====Restore Mutite DataBase File From a Path                      ======<br>  =====Ken.Guo                                                       ======<br>  =====2010.9.10                                                      ======<br>  =====Version: 2005 & 2008 SQL Server                               ======<br>  =====EXEC Usp_RestoreMuiteDataBaseFromPath 'D:\databack','',0      ======<br>  =========================================================================<br></span><span>*/</span><span><br></span><span>CREATE</span><span>PROC</span><span> Usp_RestoreMuiteDataBaseFromPath<br>( </span><span>@DatabBaseBakPath</span><span>nvarchar</span><span>(</span><span>400</span><span>)<br> ,</span><span>@RestoreDataPath</span><span>nvarchar</span><span>(</span><span>400</span><span>)</span><span>=</span><span>''</span><span>--</span><span>RESTORE DATABASE PATH </span><span><br></span><span> ,</span><span>@IsRun</span><span>smallint</span><span>=</span><span>0</span><span>--</span><span> 0 PRINT 1 run </span><span><br></span><span>) <br></span><span>AS</span><span><br></span><span>BEGIN</span><span><br></span><span>set</span><span> nocount </span><span>on</span><span><br></span><span>DECLARE</span><span>@BackUpFileName</span><span>nvarchar</span><span>(</span><span>200</span><span>) <br>       ,</span><span>@DbName</span><span>nvarchar</span><span>(</span><span>200</span><span>) <br>       ,</span><span>@errorinfo</span><span>nvarchar</span><span>(</span><span>400</span><span>)<br><br></span><span>IF</span><span>not</span><span>exists</span><span>(</span><span>SELECT</span><span>1</span><span> <br>              </span><span>FROM</span><span> master.sys.procedures </span><span>WITH</span><span>(NOLOCK) <br>              </span><span>WHERE</span><span> <br>                  name</span><span>=</span><span>N</span><span>'</span><span>Usp_RestoreDataBaseFormPath</span><span>'</span><span>  <br>           <br>           )<br>  </span><span>begin</span><span><br>   </span><span>Raiserror</span><span>50001</span><span> N</span><span>'</span><span>找不到存储过程SP_RestoreDataBaseFormPath </span><span>'</span><span>    <br>   </span><span>Goto</span><span> ExitFLag<br>  </span><span>end</span><span><br><br></span><span>--</span><span>add path \</span><span><br></span><span>if</span><span> (</span><span>@DatabBaseBakPath</span><span>is</span><span>not</span><span>null</span><span>) </span><span>and</span><span>len</span><span>(</span><span>@DatabBaseBakPath</span><span>)</span><span>></span><span>1</span><span> <br>   </span><span>and</span><span> (</span><span>right</span><span>(</span><span>@DatabBaseBakPath</span><span>,</span><span>1</span><span>)</span><span></span><span>'</span><span>\</span><span>'</span><span>)<br> </span><span>set</span><span>@DatabBaseBakPath</span><span>=</span><span>@DatabBaseBakPath</span><span>+</span><span>'</span><span>\</span><span>'</span><span><br><br></span><span>--</span><span>Check Restore Path and Size >1000M</span><span><br></span><span>DECLARE</span><span>@checkdrive</span><span>int</span><span><br></span><span>SET</span><span>@checkdrive</span><span>=</span><span>1</span><span><br> </span><span>EXEC</span><span> master.dbo.Usp_Check_DriveExists </span><span>@RestoreDataPath</span><span>,</span><span>@checkdrive</span><span> OUTPUT<br><br> </span><span>IF</span><span>(</span><span>@checkdrive</span><span></span><span>1</span><span>)<br>    </span><span>Goto</span><span> ExitFLag <br>    <br></span><span>DECLARE</span><span>@Dir</span><span>TABLE</span><span> <br>( <br>     BackDBFileName </span><span>nvarchar</span><span>(</span><span>100</span><span>) <br>    ,DEPTH </span><span>int</span><span> <br>    ,</span><span>[</span><span>File</span><span>]</span><span>int</span><span> <br>)<br><br></span><span>INSERT</span><span>INTO</span><span>@Dir</span><span>EXEC</span><span> xp_dirtree </span><span>@DatabBaseBakPath</span><span><br>                     ,</span><span>1</span><span><br>                     ,</span><span>1</span><span><br><br></span><span>DELETE</span><span>FROM</span><span>@Dir</span><span> <br></span><span>WHERE</span><span>charindex</span><span>(</span><span>'</span><span>.bak</span><span>'</span><span>,BackDBFileName)</span><span>=</span><span>0</span><span><br><br></span><span>if</span><span>not</span><span>exists</span><span> (</span><span>select</span><span>top</span><span>1</span><span>1</span><span>from</span><span>@Dir</span><span>)<br>  </span><span>begin</span><span><br>   </span><span>Raiserror</span><span>50001</span><span> N</span><span>'</span><span>在提供的路径下没有找到合符要求的备份文件</span><span>'</span><span>    <br>   </span><span>Goto</span><span> ExitFLag<br>  </span><span>end</span><span><br><br></span><span>declare</span><span> db_file </span><span>Cursor</span><span> Local Static Read_Only Forward_Only<br></span><span>for</span><span><br></span><span>select</span><span> BackDBFileName </span><span>from</span><span>@Dir</span><span><br><br></span><span>Open</span><span> db_file<br></span><span>Fetch</span><span>Next</span><span>from</span><span> db_file </span><span>into</span><span>@BackUpFileName</span><span><br></span><span>while</span><span>@@FETCH_STATUS</span><span>=</span><span>0</span><span><br> </span><span>begin</span><span><br>  </span><span>--</span><span>Restore DataBase</span><span><br></span><span>set</span><span>@BackUpFileName</span><span>=</span><span>@DatabBaseBakPath</span><span>+</span><span>@BackUpFileName</span><span><br>  </span><span>exec</span><span> master.dbo.Usp_RestoreDataBaseFormPath </span><span>@BackUpFileName</span><span>,</span><span>@RestoreDataPath</span><span>,</span><span>@IsRun</span><span><br>  </span><span>Fetch</span><span>Next</span><span>from</span><span> db_file </span><span>into</span><span>@BackUpFileName</span><span><br> </span><span>end</span><span><br></span><span>Close</span><span> db_file<br></span><span>deallocate</span><span> db_file<br><br>ExitFLag:<br></span><span>set</span><span> nocount </span><span>off</span><span><br></span><span>end</span>
登入後複製
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

熱門話題

Java教學
1658
14
CakePHP 教程
1415
52
Laravel 教程
1309
25
PHP教程
1257
29
C# 教程
1231
24
如何在 PHP 中使用 MySQL 備份和還原? 如何在 PHP 中使用 MySQL 備份和還原? Jun 03, 2024 pm 12:19 PM

在PHP中備份和還原MySQL資料庫可透過以下步驟實現:備份資料庫:使用mysqldump指令轉儲資料庫為SQL檔。還原資料庫:使用mysql指令從SQL檔案還原資料庫。

iOS 18 新增「已復原」相簿功能 可找回遺失或損壞的照片 iOS 18 新增「已復原」相簿功能 可找回遺失或損壞的照片 Jul 18, 2024 am 05:48 AM

蘋果公司最新發布的iOS18、iPadOS18以及macOSSequoia系統為Photos應用程式增添了一項重要功能,旨在幫助用戶輕鬆恢復因各種原因遺失或損壞的照片和影片。這項新功能在Photos應用的"工具"部分引入了一個名為"已恢復"的相冊,當用戶設備中存在未納入其照片庫的圖片或影片時,該相冊將自動顯示。 "已恢復"相簿的出現為因資料庫損壞、相機應用未正確保存至照片庫或第三方應用管理照片庫時照片和視頻丟失提供了解決方案。使用者只需簡單幾步

在PHP中使用MySQLi建立資料庫連線的詳盡教學 在PHP中使用MySQLi建立資料庫連線的詳盡教學 Jun 04, 2024 pm 01:42 PM

如何在PHP中使用MySQLi建立資料庫連線:包含MySQLi擴充(require_once)建立連線函數(functionconnect_to_db)呼叫連線函數($conn=connect_to_db())執行查詢($result=$conn->query())關閉連線( $conn->close())

navicat資料庫檔案在哪 navicat資料庫檔案在哪 Apr 23, 2024 am 10:57 AM

Navicat 資料庫設定檔的儲存位置因作業系統而異:Windows:使用者特定路徑為%APPDATA%\PremiumSoft\Navicat\macOS:使用者特定路徑為~/Library/Application Support/Navicat\Linux:使用者特定路徑為~/ .config/navicat\設定檔名稱包含連線類型,如navicat_mysql.ini。這些設定檔儲存資料庫連線資訊、查詢歷史記錄和 SSH 設定。

如何在PHP中處理資料庫連線錯誤 如何在PHP中處理資料庫連線錯誤 Jun 05, 2024 pm 02:16 PM

PHP處理資料庫連線報錯,可以使用下列步驟:使用mysqli_connect_errno()取得錯誤代碼。使用mysqli_connect_error()取得錯誤訊息。透過擷取並記錄這些錯誤訊息,可以輕鬆識別並解決資料庫連接問題,確保應用程式的順暢運作。

navicat資料庫連接url怎麼寫 navicat資料庫連接url怎麼寫 Apr 24, 2024 am 02:33 AM

Navicat連線URL格式為:協定://使用者名稱:密碼@主機:連接埠/資料庫名稱?參數,包含了連線所需的信息,包括協定、使用者名稱、密碼、主機名稱、連接埠、資料庫名稱和可選參數。

如何在 Golang 中使用資料庫回呼函數? 如何在 Golang 中使用資料庫回呼函數? Jun 03, 2024 pm 02:20 PM

在Golang中使用資料庫回呼函數可以實現:在指定資料庫操作完成後執行自訂程式碼。透過單獨的函數新增自訂行為,無需編寫額外程式碼。回調函數可用於插入、更新、刪除和查詢操作。必須使用sql.Exec、sql.QueryRow或sql.Query函數才能使用回呼函數。

如何用 Golang 連接遠端資料庫? 如何用 Golang 連接遠端資料庫? Jun 01, 2024 pm 08:31 PM

透過Go標準庫database/sql包,可以連接到MySQL、PostgreSQL或SQLite等遠端資料庫:建立包含資料庫連接資訊的連接字串。使用sql.Open()函數開啟資料庫連線。執行SQL查詢和插入操作等資料庫操作。使用defer關閉資料庫連線以釋放資源。

See all articles