Home > SQL Server > Backup database sử dụng SQL Script (T-SQL)

Backup database sử dụng SQL Script (T-SQL)

Giả sử bạn có database CMS.

1. Backup đơn giản :

BACKUP DATABASE [CMS] TO  DISK = N'D:\CMS.bak' WITH  INIT ,  NOUNLOAD ,  
NAME = N'CMS backup',  NOSKIP ,  STATS = 10,  NOFORMAT

2. Thêm thông tin ngày vào tên file backup :

declare @f sysname
  set @f=N'D:\CMS'+convert(nvarchar,getdate(),112)+N'.bak'

  BACKUP DATABASE [CMS] TO  DISK =@f
  WITH NOFORMAT, INIT,  NAME = N'CMS-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
  GO

3. Thêm thông tin ngày, giờ vào tên file backup:

declare @currentDate datetime
set @currentDate = GetDate()
declare @fileName varchar(255)
set @fileName = 'D:\CMS'
 + cast(Year(@currentDate) as varchar(4))
 + Replicate('0', 2 - Len(cast(Month(@currentDate) as varchar(2))))
 + cast(Month(@currentDate) as varchar(2))
 + Replicate('0', 2 - Len(cast(Day(@currentDate) as varchar(2))))
 + cast(Day(@currentDate) as varchar(2))
 + '_' +
 + Replicate('0', 2 - Len(cast(DatePart(hour, @currentDate) as varchar(2))))         
 + cast(DatePart(hour, @currentDate) as varchar(2)) 
 + Replicate('0', 2 - Len(cast(DatePart(minute, @currentDate) as varchar(2))))            
 + cast(DatePart(minute, @currentDate) as varchar(2)) + '.bak'
backup database [CMS] to disk = @fileName with NOFORMAT, NOINIT,
    name = N'CMS-Full Database Backup',
    SKIP, NOREWIND, NOUNLOAD,  STATS = 10
Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: