-- USE PUEBLO\BIBBY!

-- view the data files via location

RESTORE FILELISTONLY 
FROM DISK = 'C:\mssql\backup2\Native_backup.bkp'


-- master.dbo.sysdevices
-- add device

sp_addumpdevice 
 @devtype = 'disk', 
 @logicalname = 'test_device' , 
 @physicalname = 'C:\mssql\backup2\Native_backup.bkp'
 --@cntrltype = controller_type 
 --@devstatus = 'device_status' 
 
-- view the data files via device name

RESTORE FILELISTONLY 
FROM test_device

RESTORE HEADERONLY 
FROM test_device

--delete device

sp_dropdevice
 @logicalname = 'test_device'

-- the backup statement

BACKUP DATABASE pubs
TO DISK = 'C:\mssql\backup3\pubs_test.bak'
WITH 
  DESCRIPTION = 'full backup of pubs', 
  -- DIFFERENTIAL 
  -- EXPIREDATE 
  -- RETAINDAYS 
  -- PASSWORD
  -- FORMAT | NOFORMAT 
  INIT, --NOINIT
  -- MEDIADESCRIPTION
  -- MEDIANAME 
  -- MEDIAPASSWORD 
  -- NAME 
  -- NOSKIP/SKIP 
  -- NOREWIND/REWIND 
  -- NOUNLOAD/UNLOAD 
  -- RESTART
  STATS

------------------------------------------------------------------------
-- BACKING UP!
------------------------------------------------------------------------

-- full

BACKUP DATABASE pubs 
TO DISK = 'C:\mssql\full\pubs_test.bak'
WITH 
  DESCRIPTION = 'full backup of pubs',
  STATS,
  INIT

-- striping

BACKUP DATABASE pubs 
TO DISK = 'C:\mssql\striping\pubs_test1.bak',
DISK = 'C:\mssql\striping\pubs_test2.bak',
DISK = 'C:\mssql\striping\pubs_test3.bak'
WITH 
  DESCRIPTION = 'full backup of pubs',
  STATS,
  INIT

-----------------------------------------------------------------------

-- differential

-- do a full

BACKUP DATABASE pubs 
TO DISK = 'C:\mssql\diff\pubs_test.bak'
WITH 
  DESCRIPTION = 'full backup of pubs',
  STATS,
  INIT

-- do a diff

BACKUP DATABASE pubs
TO DISK = 'C:\mssql\diff\pubs_test.bak'
WITH 
  DESCRIPTION = 'differential backup of pubs',
  DIFFERENTIAL

-- look at the device and inspect the files

RESTORE HEADERONLY 
FROM DISK = 'C:\mssql\diff\pubs_test.bak'

-- Now Enterprise Manager

-----------------------------------------------------------------------------------

-- transaction log

use pubs
delete from authors where au_id = '000-00-1000'
delete from authors where au_id = '000-00-2000'
delete from authors where au_id = '000-00-3000'
use master

-- do a full

BACKUP DATABASE pubs 
TO DISK = 'C:\mssql\tlog\pubs_test.bak'
WITH 
  DESCRIPTION = 'full backup of pubs',
  STATS,
  INIT

-- do a tlog backup

BACKUP LOG pubs 
TO DISK = 'C:\mssql\tlog\pubs_test.bak'
WITH 
  DESCRIPTION = 'tlog backup of pubs'

-- make a few inserts



use pubs
insert into authors values (
	'000-00-1000', 'Smoe', 'Joe', '520 543-9876', '32 Jordan St', 'Wintrop',
	'NY', '23451', 0
	)

BEGIN TRANSACTION InsertAuthor 
   WITH MARK 'MARKTEST'
GO

GO
insert into authors values (
	'000-00-2000', 'Smith', 'Larry', '515 345-4321', '33 Plain St.', 'Anytown',
	'IN', '08184', 0
	)
GO
COMMIT TRANSACTION InsertAuthor
GO

insert into authors values (
	'000-00-3000', 'Johnson', 'Raymond', '678 478-2446',
	'111 Miller Ave', 'Buffalo', 'NY', '09876', 0
	)

-- do another tlog backup

BACKUP LOG pubs 
TO DISK = 'C:\mssql\tlog\pubs_test.bak'
WITH 
  DESCRIPTION = 'tlog backup of pubs'

RESTORE HEADERONLY 
FROM DISK = 'C:\mssql\tlog\pubs_test.bak'


--------------------------------------------------------------------------------

-- filegroups

BACKUP LOG pubs 
TO DISK = 'C:\mssql\filegroup\fg_backup_log1.bak'
WITH 
  DESCRIPTION = 'tlog backup of pubs'

BACKUP DATABASE pubs
FILEGROUP = 'FG_test1',
FILEGROUP = 'FG_test2'
TO DISK = 'C:\mssql\filegroup\fg_backup.bak'

BACKUP LOG pubs 
TO DISK = 'C:\mssql\filegroup\fg_backup_log2.bak'
WITH 
  DESCRIPTION = 'tlog backup of pubs'

RESTORE HEADERONLY 
FROM DISK = 'C:\mssql\filegroup\fg_backup.bak'

RESTORE FILELISTONLY 
FROM DISK = 'C:\mssql\filegroup\fg_backup.bak'

---------------------------------------------------------------------------------

-- verifying your data

RESTORE VERIFYONLY 
FROM DISK = 'C:\mssql\backup2\Native_backup.bkp'



/****************************************************************
*****************************************************************
*****************************************************************
****************************************************************/

----------------------------------------------------------------
-- RESTORING YOUR DATA!
----------------------------------------------------------------

-- full restore

sp_who2
kill 53



RESTORE DATABASE pubs 
  FROM DISK = 'C:\mssql\full\pubs_test.bak'

RESTORE DATABASE Northwind_test
  FROM DISK = 'C:\mssql\backup2\Native_backup.bkp'
  WITH RECOVERY,
  MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$BIBBY\Data\nwind_test1.mdf', 
  MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$BIBBY\Data\nwind_test1.ldf'

DROP DATABASE Northwind_test
  
-- full plus differential

RESTORE HEADERONLY 
FROM DISK = 'C:\mssql\diff\pubs_test.bak'

RESTORE DATABASE pubs 
  FROM DISK = 'C:\mssql\diff\pubs_test.bak'
  WITH NORECOVERY,
	FILE = 1

RESTORE DATABASE pubs 
  FROM DISK = 'C:\mssql\diff\pubs_test.bak'
  WITH RECOVERY,
	FILE = 2

-- full restore plus transaction log to a point in time

RESTORE HEADERONLY 
FROM DISK = 'C:\mssql\tlog\pubs_test.bak'

RESTORE FILELISTONLY 
FROM DISK = 'C:\mssql\tlog\pubs_test.bak'

use master
RESTORE DATABASE pubs 
  FROM DISK = 'C:\mssql\tlog\pubs_test.bak'
  WITH 	NORECOVERY,
	FILE = 1

RESTORE LOG pubs
  FROM DISK = 'C:\mssql\tlog\pubs_test.bak'
  WITH 	NORECOVERY,
	FILE = 2

RESTORE LOG pubs
  FROM DISK = 'C:\mssql\tlog\pubs_test.bak'
  WITH 	RECOVERY,
	FILE = 3
	--,STOPAT = '2002-06-07 00:30:00.000'
	--,STOPATMARK = 'InsertAuthor'
	--,STOPBEFOREMARK = 'InsertAuthor'


use pubs
select * from authors
use master

-- restore filegroups


RESTORE DATABASE pubs
   FILE = 'DATA1',
   FILEGROUP = 'FG_test1',
   FILE = 'DATA2',
   FILEGROUP = 'FG_test2'
   FROM DISK = 'C:\mssql\filegroup\fg_backup.bak'
   WITH REPLACE,
	NORECOVERY

RESTORE LOG pubs
  FROM DISK = 'C:\mssql\filegroup\fg_backup_log1.bak'
  WITH 	NORECOVERY

RESTORE LOG pubs
  FROM DISK = 'C:\mssql\filegroup\fg_backup_log2.bak'
  WITH RECOVERY