你所在位置:首页Sql资源 → SQL Server备份、还原、附加、分离数据库批量语句

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

发布时间:2019-07-11


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

 

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

 

备份数据库:

 

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.350job.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.350job.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.350job.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.350job.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.350job.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批量生成分离数据库语句:(数据馕募柯枷略诵校/p>

  www.350job.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


上一篇:SQL Server 2000+Windows Server 2003大内存配置
下一篇:SQL2005删除用户的时候,产生“数据库主体在该数据库中拥有架构,无法删除”错误的解决办法