SQLServer数据库备份（语句方式）

来源：互联网发布：淘宝查询买家退货率编辑：程序博客网时间：2024/06/04 00:56

1.将数据库名为“SIMDB”的数据库备份为d:\db_bak\SIMDB_20160105.bak
backup database SIMDB TO disk='d:\db_bak\SIMDB_20160105.bak' WITH FORMAT

------------------------------------------
2.

DECLARE

@FileName VARCHAR(200),

@CurrentTime VARCHAR(50),

@DBName VARCHAR(100),

@SQL VARCHAR(1000)

SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)

SET @DBName='SIMDB' --需要备份的数据库名

BEGIN

--Execute Backup

SET @FileName = 'd:\db_bak\' + @DBName + '_' + @CurrentTime

SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +

''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'

EXEC(@SQL)

END

------------------------------------

3. 备份数据库中存在的数据库（SELECT NAME FROM Master..SysDatabases 查看数据库中存在的数据库）

DECLARE

@FileName VARCHAR(200),

@CurrentTime VARCHAR(50),

@DBName VARCHAR(100),

@SQL VARCHAR(1000)

SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)

DECLARE CurDBName CURSOR FOR

SELECT NAME FROM Master..SysDatabases where dbid>6

OPEN CurDBName

FETCH NEXT FROM CurDBName INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

--Execute Backup

SET @FileName = 'd:\db_bak\' + @DBName + '_' + @CurrentTime

SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +

''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'

EXEC(@SQL)

--Get Next DataBase

FETCH NEXT FROM CurDBName INTO @DBName

END

CLOSE CurDBName

DEALLOCATE CurDBName

0 0