Maintenance Plans For Backup Database dan Restore Database Applies To: Microsoft SQL Server 2008 R2 Tasks: Backup Database and Restore using Maintenance Plans. Example: • Backup Database “DB_Test” from Server “IT107\SQLSVR_1” to Local Drive
Maintenance Plan Name : Backup •

Restore file backup, to Database “DB_Test_Simulasi” in Server “IT107\SQLSVR_2”
Maintenance Plan Name : Restore

1

Maintenance Plan Name : Backup 1. a. Open “Management > Maintenance Plans”

b. Right-click, choose “New Maintenance Plan”
c. Type for “Name”, Example : Backup. Click “OK”.

2

2.

3. Save the file, Example: Backup – sa [Design].

4. Choose “Manage Connection…”


3

5. Choose “Local Server Connection”
6. Type for “Password”. Click “OK”.

7. To save, press “CTR + S”
Note: If the Design is saved, will like this.

4

8. Choose “Maintenance Plan Tasks > Execute T-SQL-Statement Task”

And drag tasks to Designer Surface.

9.

5

10. Check location path of database.

6

11. Create Task-1 a. Right-click at “Execute T-SQL Statement Task”

b. Choose “Edit…”


c. Type this code to “T-SQL Statement”
/* BEGIN */ USE [DB_Test] GO -- Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE [DB_Test] SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (DB_Test); GO DBCC SHRINKFILE (DB_Test_Log, 1); GO -- Reset the database recovery model. ALTER DATABASE [DB_Test] SET RECOVERY FULL; GO USE master GO /* END */

7

d.

e. Click “OK”. f. Note: To Copy the code, choose “View T-SQL”


8

12.

9

13. Create Task-2 a. Right-click at “Execute T-SQL Statement Task”

b. Choose “Edit…”


c. Type this code to “T-SQL Statement”


/* BEGIN */ Declare @Backup as varchar(100), @BackupFile as varchar(100) select @BackupFile='DB_Test' +'_'+ convert(varchar(20),getdate(),12) +'_'+ replace (CONVERT(VARCHAR(5),GETDATE(),108), ':', '')+'.dat' select @Backup='C:\BackupTest\'+@BackupFile backup database [DB_Test] to DISK=@Backup Declare @CopyCommand as varchar(200) select @CopyCommand='Copy '+@Backup+' \\xxx.xxx.x.44\Share\BackupDB\'+@BackupFile EXEC master..xp_cmdshell @CopyCommand,NO_OUTPUT select @CopyCommand='Copy '+@Backup+' \\xxx.xxx.x.44\Share\RestoreDB\DB_Test.DAT' EXEC master..xp_cmdshell @CopyCommand,NO_OUTPUT /* END */

10

d.

e. Click “OK”. f. Note: To Copy the code, choose “View T-SQL”


11

14. Create pointer to connect from “Task – 1” to “Task – 2” a. Active Pointer at “Task – 1”

b. Drag pointer ( color:green ) from “Task – 1” to “Task – 2”

15. Check for Maintenance Plan Name : Backup, that already create.

12

16. Set Schedule Time.

a. Choose icon “

”

b. If you want, run this task at once, choose “Schedule Type” : One Time.

c. Click “OK”.

13

17. Check for Local Drive for Backup. There is no file.

18. Go to “Management > Maintenance Plans > Backup” Choose “Execute”.

14

19. Check for Local Drive for Backup.

20. Backup Database successfully.

15

Maintenance Plan Name : Restore 21. a. Open “Management > Maintenance Plans”

b. Right-click, choose “New Maintenance Plan”
c. Type for “Name”, Example : Restore. Click “OK”.

16

22.

23. Save the file, Example: Restore – sa [Design].

24. Choose “Manage Connection…”


17

25. Choose “Local Server Connection”
26. Type for “Password”. Click “OK”.

27. To save, press “CTR + S”
Note: If the Design is saved, will like this.

18

28. Choose “Maintenance Plan Tasks > Execute T-SQL-Statement Task”

And drag tasks to Designer Surface.

29.

19

30. Check file backup for restore.

31. Check availability database.

32. Check location path of database.

20

33. Create Task-1 a. Right-click at “Execute T-SQL Statement Task”

b. Choose “Edit…”


c. Type this code to “T-SQL Statement”


/* Task for : Detach DB */ /* BEGIN */ USE [master] GO ALTER DATABASE [DB_Test_Simulasi] SET GO

SINGLE_USER WITH ROLLBACK IMMEDIATE

USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N'DB_Test_Simulasi' GO /* END */

21

d. Click “OK”. e. Note: To Copy the code, choose “View T-SQL”


22

34. Create Task-2 a. Right-click at “Execute T-SQL Statement Task”

b. Choose “Edit…”


c. Type this code to “T-SQL Statement”
/* Task for : Attach DB */ /* BEGIN */ USE [master] GO CREATE DATABASE [DB_Test_Simulasi] ON ( FILENAME = N'C:\DB_Test\DB_Test_Simulasi.mdf' ), ( FILENAME = N'C:\DB_Test\DB_Test_Simulasi_log.ldf' ) FOR ATTACH GO /* END */

23

d. Click “OK”. e. Note: To Copy the code, choose “View T-SQL”


24

35. Create Task-3 a. Right-click at “Execute T-SQL Statement Task”

b. Choose “Edit…”


c. Type this code to “T-SQL Statement”
/* Task for : Restore DB */ /* BEGIN */

RESTORE DATABASE [DB_Test_Simulasi] FROM DISK = N'D:\Share\RestoreDB\DB_Test.DAT' WITH FILE = 1, MOVE N'DB_Test' TO N'C:\DB_Test\DB_Test_Simulasi.mdf', MOVE N'DB_Test_Log' TO N'C:\DB_Test\DB_Test_Simulasi_Log.ldf', NOUNLOAD, REPLACE, STATS = 10 GO /* END */

25

d. Click “OK”. e. Note: To Copy the code, choose “View T-SQL”


26

36. Create pointer to connect from “Task – 1” to “Task – 3” a. Active Pointer at “Task – 1”

b. Drag pointer ( color:green ) from “Task – 1” to “Task – 3”

37. Check for Maintenance Plan Name : Restore, that already create.

27

38. Set Schedule Time.

a. Choose icon “

”

b. If you want, run this task at once, choose “Schedule Type” : One Time.

c. Click “OK”.

28

39. Check for database files date – before restore.

40. Go to “Management > Maintenance Plans > Restore” Choose “Execute”.

29

41. Check for database files date – after restore.

42. Restore Database successfully.

----- End of Documents -----

30