I am sorry, this article is not available in your preferred language or you can try to use Google translation service to translate if you do not understand the article content.
SQL Server数据库压缩备份小记
This post is over a year old, some of this information may be out of date.
-- Copyright ? Microsoft Corporation. All Rights Reserved. -- This code released under the terms of the -- Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)USE[master]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============================================= -- Author: Microsoft -- Create date: 2010-02-06 -- Description: Backup Databases for SQLExpress -- Parameter1: databaseName -- Parameter2: backupType F=full, D=differential, L=log -- Parameter3: backup file location -- ============================================= CREATEPROCEDURE[dbo].[sp_BackupDatabases]@databaseName sysname =null,@backupTypeCHAR(1),@backupLocation nvarchar(200)ASSET NOCOUNT ON;DECLARE@DBsTABLE(
ID intIDENTITYPRIMARYKEY,
DBNAME nvarchar(500))-- Pick out only databases which are online in case ALL databases are chosen to be backed up -- If specific database is chosen to be backed up only pick that out from @DBs INSERTINTO@DBs(DBNAME)SELECT Name FROM master.sys.databaseswhere state=0AND name= ISNULL(@databaseName,name)ORDERBY Name
-- Filter out databases which do not need to backed up IF@backupType='F'BEGINDELETE@DBswhere DBNAME IN('tempdb','Northwind','pubs','AdventureWorks')ENDELSEIF@backupType='D'BEGINDELETE@DBswhere DBNAME IN('tempdb','Northwind','pubs','master','AdventureWorks')ENDELSEIF@backupType='L'BEGINDELETE@DBswhere DBNAME IN('tempdb','Northwind','pubs','master','AdventureWorks')ENDELSEBEGINRETURNEND-- Declare variables DECLARE@BackupName nvarchar(100)DECLARE@BackupFile nvarchar(300)DECLARE@DBNAME nvarchar(300)DECLARE@sqlCommand NVARCHAR(1000)DECLARE@dateTime NVARCHAR(20)DECLARE@Loopint-- Loop through the databases one by one SELECT@Loop=min(ID)FROM@DBsWHILE@LoopISNOTNULLBEGIN-- Database Names have to be in [dbname] format since some have - or _ in their name SET@DBNAME='['+(SELECT DBNAME FROM@DBsWHERE ID =@Loop)+']'-- Set the current date and time n yyyyhhmmss format SET@dateTime=REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','')+'_'+REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')-- Create backup filename in path\filename.extension format for full,diff and log backups IF@backupType='F'SET@BackupFile=@backupLocation+REPLACE(REPLACE(@DBNAME,'[',''),']','')+'_FULL_'+@dateTime+'.BAK'ELSEIF@backupType='D'SET@BackupFile=@backupLocation+REPLACE(REPLACE(@DBNAME,'[',''),']','')+'_DIFF_'+@dateTime+'.BAK'ELSEIF@backupType='L'SET@BackupFile=@backupLocation+REPLACE(REPLACE(@DBNAME,'[',''),']','')+'_LOG_'+@dateTime+'.TRN'-- Provide the backup a name for storing in the media IF@backupType='F'SET@BackupName=REPLACE(REPLACE(@DBNAME,'[',''),']','')+' full backup for '+@dateTimeIF@backupType='D'SET@BackupName=REPLACE(REPLACE(@DBNAME,'[',''),']','')+' differential backup for '+@dateTimeIF@backupType='L'SET@BackupName=REPLACE(REPLACE(@DBNAME,'[',''),']','')+' log backup for '+@dateTime-- Generate the dynamic SQL command to be executed IF@backupType='F'BEGINSET@sqlCommand='BACKUP DATABASE '+@DBNAME+' TO DISK = '''+@BackupFile+''' WITH INIT, NAME= '''+@BackupName+''', NOSKIP, NOFORMAT'ENDIF@backupType='D'BEGINSET@sqlCommand='BACKUP DATABASE '+@DBNAME+' TO DISK = '''+@BackupFile+''' WITH DIFFERENTIAL, INIT, NAME= '''+@BackupName+''', NOSKIP, NOFORMAT'ENDIF@backupType='L'BEGINSET@sqlCommand='BACKUP LOG '+@DBNAME+' TO DISK = '''+@BackupFile+''' WITH INIT, NAME= '''+@BackupName+''', NOSKIP, NOFORMAT'END-- Execute the generated SQL command EXEC(@sqlCommand)-- Goto the next database SELECT@Loop=min(ID)FROM@DBswhere ID>@LoopEND
To continue use comments feature please understand our privacy policy first and then you should allow the following type of cookies that for comment service:
Third-party cookies for authentication, security and fraud, and abuse prevention
Cookies that remember your settings
Strictly necessary cookies
By click the button below you will being asked by a pop-up box to confirm which cookies you're happy for me to use.
To continue use comments feature please understand our privacy policy first and then you should allow the following type of cookies that for comment service:
By click the button below you will being asked by a pop-up box to confirm which cookies you're happy for me to use.
Understood & Confirm Cookie Settings