SQL Server 备份、还原、附加、分离数据库 批量语句

以下例子是我在实际生产环境中使用过的，在查询分析器中运行。

备份数据库：

use master  declare @ls_time varchar(100)  declare @ls_dbname varchar(100)  set @ls_time = convert(varchar, getdate(), 112) + '_' + replace(convert(varchar, getdate(), 108), ':', '')    set @ls_dbname = 'D:\data\backup\' + @ls_time + '_PRE_Article.bak'  BACKUP DATABASE PRE_Article TO disk = @ls_dbname                                   set @ls_dbname = 'D:\data\backup\' + @ls_time + '_PRE_Supply.bak'  BACKUP DATABASE PRE_Supply TO disk = @ls_dbname    set @ls_dbname = 'D:\data\backup\' + @ls_time + '_PRE_Ask.bak'  BACKUP DATABASE PRE_Ask TO disk = @ls_dbname    -- ...... 按上面格式添加

收缩数据库：

DUMP TRANSACTION PRE_Article WITH NO_LOG  BACKUP LOG PRE_Article WITH NO_LOG  DBCC SHRINKDATABASE(PRE_Article)    DUMP TRANSACTION PRE_Supply WITH NO_LOG  BACKUP LOG PRE_Supply WITH NO_LOG  DBCC SHRINKDATABASE(PRE_Supply)    DUMP TRANSACTION PRE_Ask WITH NO_LOG  BACKUP LOG PRE_Ask WITH NO_LOG  DBCC SHRINKDATABASE(PRE_Ask)    -- ...... 按上面格式添加

附加数据库：

EXEC sp_attach_db @dbname = 'PRE_Article'  ,@filename1 = 'E:\Data\PRE_Article_Data.MDF'   ,@filename2 = 'E:\Data\PRE_Article_Log.LDF'       EXEC sp_attach_db @dbname = 'PRE_Supply'  ,@filename1 = 'E:\Data\PRE_Supply_Data.MDF'   ,@filename2 = 'E:\Data\PRE_Supply_Log.LDF'    EXEC sp_attach_db @dbname = 'PRE_Ask'  ,@filename1 = 'E:\Data\PRE_Ask_Data.MDF'   ,@filename2 = 'E:\Data\PRE_Ask_Log.LDF'    -- ...... 按上面格式添加

BAT批量生成附加数据库语句：（数据库文件同目录下运行）

 ECHO USE [master] >> 附加.sql  ECHO GO >> 附加.sql  FOR %%I IN (*.mdf) DO ECHO CREATE DATABASE [%%~nI] ON >> 附加.sql  FOR %%I IN (*.mdf) DO ECHO (FILENAME = N'%%~dpnxI'), >> 附加.sql  FOR %%I IN (*.ldf) DO ECHO (FILENAME = N'%%~dpnxI') >> 附加.sql  ECHO FOR ATTACH >> 附加.sql  ECHO GO >> 附加.sql  ::sqlcmd -i 附加.sql  ::del 附加.sql  ECHO.  ::PAUSE

BAT批量生成分离数据库语句：（数据库文件同目录下运行）

 FOR %%I IN (*.mdf) DO echo EXEC master.dbo.sp_detach_db @dbname = N'%%~nI', @keepfulltextindexfile=N'true' >> 分离.sql  ECHO.  ::PAUSE