09 July 2009

Auto Backup Database in SqlServer with Date and Time Information in the File Name

  1. Start SQL Server Management Studio.
  2. In the Connect to Server dialog box, click the appropriate values in the Server type list, in the Server name list, and in the Authentication list.
  3. Click Connect.
  4. In Object Explorer, expand Databases.
  5. Right-click the database that you want to back up, click Tasks, and then click Back Up.
  6. In the Back Up Database - DatabaseName dialog box, type the name of the backup set in the Name box, and then click Add under Destination.
  7. In the Select Backup Destination dialog box, type a path and a file name in the Destinations on disk box, and then click OK.
  8. In the Script list, click Script Action to Job.
  9. In the New Job dialog box, click Steps under Select a page, and then click Edit if you want to change the job parameters.
  10. for Backup Database with Date and Time Information in the File Name, change script:
    • declare @currentDate datetime
    • set @currentDate = GetDate()
    • --
    • declare @fileName varchar(255)
    • set @fileName = 'C:\BKP\MyDatabase'
    • + 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 [MyDatabase] to disk = @fileName with NOFORMAT, NOINIT,
    • name = N'MyDatabase-Full Database Backup',
    • SKIP, NOREWIND, NOUNLOAD, STATS = 10

  11. In the Job Step Properties - 1 dialog box, you can see the backup command.
  12. Under Select a page, click Schedules, and then click New.
  13. In the New Job Schedule dialog box, type the job name in the Name box, specify the job schedule, and then click OK.

    Note If you want to configure alerts or notifications, you can click Alerts or Notifications under Select a page.
  14. Click OK two times.
Oke....selamat mencoba...

No comments:

Post a Comment

silahkan isi Comment Anda :