-
Notifications
You must be signed in to change notification settings - Fork 1
/
backuptest-RO.sql
64 lines (49 loc) · 2.1 KB
/
backuptest-RO.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
/*
drop database ts0to4
--restore DB in simple recovery mode
USE [master]
RESTORE DATABASE [ts0to4] FROM DISK = N'C:\data\data1\ts0to4.bak' WITH FILE = 1, NOUNLOAD, STATS = 5
GO
*/
--this script assumes a db (in this case ts0to4)
--created in simple recovery mode.
--set FG's to readonly
alter database ts0to4 modify filegroup FG01 readonly
alter database ts0to4 modify filegroup FG02 readonly
alter database ts0to4 modify filegroup FG03 readonly
alter database ts0to4 modify filegroup FG04 readonly
alter database ts0to4 modify filegroup FG05 readonly
alter database ts0to4 modify filegroup FG06 readonly
alter database ts0to4 modify filegroup FG07 readonly
alter database ts0to4 modify filegroup FG08 readonly
alter database ts0to4 modify filegroup FG09 readonly
alter database ts0to4 modify filegroup FG10 readonly
alter database ts0to4 modify filegroup FG11 readonly
alter database ts0to4 modify filegroup FG12 readonly
alter database ts0to4 modify filegroup FG13 readonly
alter database ts0to4 modify filegroup FG14 readonly
alter database ts0to4 modify filegroup FG15 readonly
alter database ts0to4 modify filegroup FG16 readonly
--change to full recovery mode
alter database ts0to4 set recovery full
--take full backup and log backup
backup database ts0to4
to disk = 'c:\data\data1\ts0to4Full_sw5.bak'
with checksum
-- backup log
backup log ts0to4 to disk = 'c:\data\data1\ts0to4Full_sw5_log.trn'
with checksum
go
-- BACKUP IS NOW COMPLETE!
-------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--BELOW IS HOW TO RESTORE A SINGLE FILE
--------------------------------------------------------------------------------------------------------------------------------
--set one file to offline
alter database ts0to4
modify file (name=ts0to4_FG02, OFFLINE)
---restore FG02 for example
restore database ts0to4 file = 'ts0to4_FG02'
from disk = 'c:\data\data1\ts0to4Full_sw5.bak'
with recovery
select * from ts0to4.dbo.vel_02