以下例子是我在实际生产环境中使用过的，在查询分析器中运行。
备份数据库：
01
use master
02
declare @ls_time varchar(100)
03
declare @ls_dbname varchar(100)
04
set @ls_time = convert(varchar, getdate(), 112) + '_' + replace(convert(varchar, getdate(), 108), ':', '')
05
www.2cto.com
06
set @ls_dbname = 'D:\data\backup\' + @ls_time + '_PRE_Article.bak'
07
BACKUP DATABASE PRE_Article TO disk = @ls_dbname
08
09
set @ls_dbname = 'D:\data\backup\' + @ls_time + '_PRE_Supply.bak'
10
BACKUP DATABASE PRE_Supply TO disk = @ls_dbname
11
12
set @ls_dbname = 'D:\data\backup\' + @ls_time + '_PRE_Ask.bak'
13
BACKUP DATABASE PRE_Ask TO disk = @ls_dbname
14
15
-- ...... 按上面格式添加
收缩数据库：
www.2cto.com
01
DUMP TRANSACTION PRE_Article WITH NO_LOG
02
BACKUP LOG PRE_Article WITH NO_LOG
03
DBCC SHRINKDATABASE(PRE_Article)
04
05
DUMP TRANSACTION PRE_Supply WITH NO_LOG
06
BACKUP LOG PRE_Supply WITH NO_LOG
07
DBCC SHRINKDATABASE(PRE_Supply)
08
09
DUMP TRANSACTION PRE_Ask WITH NO_LOG
10
BACKUP LOG PRE_Ask WITH NO_LOG
11
DBCC SHRINKDATABASE(PRE_Ask)
12
www.2cto.com
13
-- ...... 按上面格式添加
附加数据库：
01
EXEC sp_attach_db @dbname = 'PRE_Article'
02
,@filename1 = 'E:\Data\PRE_Article_Data.MDF'
03
,@filename2 = 'E:\Data\PRE_Article_Log.LDF'
04
05
EXEC sp_attach_db @dbname = 'PRE_Supply'
06
,@filename1 = 'E:\Data\PRE_Supply_Data.MDF'
07
,@filename2 = 'E:\Data\PRE_Supply_Log.LDF'
08
09
EXEC sp_attach_db @dbname = 'PRE_Ask'
10 www.2cto.com
,@filename1 = 'E:\Data\PRE_Ask_Data.MDF'
11
,@filename2 = 'E:\Data\PRE_Ask_Log.LDF'
12
13
-- ...... 按上面格式添加
BAT批量生成附加数据库语句：（数据库文件同目录下运行）
01
ECHO USE [master] >> 附加.sql
02
ECHO GO >> 附加.sql
03 www.2cto.com
FOR %%I IN (*.mdf) DO ECHO CREATE DATABASE [%%~nI] ON >> 附加.sql
04
FOR %%I IN (*.mdf) DO ECHO (FILENAME = N'%%~dpnxI'), >> 附加.sql
05
FOR %%I IN (*.ldf) DO ECHO (FILENAME = N'%%~dpnxI') >> 附加.sql
06
ECHO FOR ATTACH >> 附加.sql
07
ECHO GO >> 附加.sql
08
::sqlcmd -i 附加.sql
09
::del 附加.sql
10
ECHO.
11
::PAUSE
BAT批量生成分离数据库语句：（数据库文件同目录下运行）
www.2cto.com
1
FOR %%I IN (*.mdf) DO echo EXEC master.dbo.sp_detach_db @dbname = N'%%~nI', @keepfulltextindexfile=N'true' >> 分离.sql
2
ECHO.
3
::PAUSE