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
