/************************************
Chapter 11 Scripts
For MCITP SQL Server 2005 Database Administration All-in-One Exam Guide
By Darril Gibson

The scripts available here are the same ones used in each of the chapters. Ideally, you would type in the script and learn from any errors you might make. Occasionally, however, you may want a copy of the script to paste into the query window or into a batch file. If so, feel free to use these script files. 

I strongly encourage you not to rely on these pre-typed scripts, but instead to get used to typing the T-SQL yourself. The more you do it (even if you’re just typing what you see in the book), the more comfortable you’ll get with T-SQL.

Some scripts in the book are intended to be run from the command line. They've been included where appropriate so that you can copy and paste them into Notepad and save them as a batch file (.bat). You can then run the batch file from the command prompt.

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

CREATE Database Chapter11;
GO

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

ALTER DATABASE Chapter11 
SET RECOVERY BULK_LOGGED

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

ALTER DATABASE Chapter11 
SET RECOVERY FULL

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

BACKUP DATABASE DatabaseName 
    TO DISK = 'Path' 

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

DECLARE @i int, @VendorID int, @CreditRating int
SET @VendorID = 1
SET @CreditRating = 3
SET @i = 1
WHILE @i < 1000 
BEGIN
  SET @i = @i + 1
  USE AdventureWorks
  UPDATE Purchasing.Vendor
  SET CreditRating = @CreditRating
    WHERE VendorID=@VendorID
END

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

BACKUP DATABASE AdventureWorks 
    TO DISK = 'C:\AW_Full.bak' 
GO
DBCC SQLPERF (LOGSPACE)

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

BACKUP LOG AdventureWorks 
    TO DISK = 'C:\AW_Log.bak' 
GO
DBCC SQLPERF (LOGSPACE)

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

BACKUP DATABASE DatabaseName 
    TO DISK = 'Path' 
WITH DIFFERENTIAL

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

BACKUP DATABASE DatabaseName 
    TO DISK = 'Path' 
WITH COPY-ONLY

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

BACKUP LOG DatabaseName 
    TO DISK = 'Path' 

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

EXEC sp_addumpdevice 'disk', 'AW_TSQL_BackupDevice', 
'C:\SQLBackups\AW_TSQL.Bak'

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

BACKUP DATABASE AdventureWorks 
    TO DISK = 'C:\AW_Full.bak' 
GO
RESTORE HEADERONLY
FROM DISK = 'c:\AW_Full.bak'

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

USE AdventureWorks
INSERT INTO Person.Contact
(FirstName, MiddleName, LastName,PasswordHash, PasswordSalt)
VALUES ('Popeye', 'The','Sailorman', 'P@ssw0rd','P@$$S@lt')
GO
USE AdventureWorks
SELECT * FROM Person.Contact
WHERE FirstName = 'Popeye'

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

BACKUP DATABASE AdventureWorks 
   TO AW_TSQL_BackupDevice

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

RESTORE HEADERONLY
FROM AW_TSQL_BackupDevice

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

USE AdventureWorks
UPDATE Person.Contact
Set LastName = 'Spinach Man'
WHERE FirstName = 'Popeye'
GO
SELECT * FROM Person.Contact
WHERE FirstName = 'Popeye'

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

BACKUP DATABASE AdventureWorks 
   TO AW_TSQL_BackupDevice
        WITH DIFFERENTIAL
GO
RESTORE HEADERONLY
FROM AW_TSQL_BackupDevice

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

USE AdventureWorks
UPDATE Person.Contact
Set LastName = 'Sailor Man'
WHERE FirstName = 'Popeye'
GO
SELECT * FROM Person.Contact
WHERE FirstName = 'Popeye'

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

BACKUP LOG AdventureWorks 
   TO AW_TSQL_BackupDevice
GO
RESTORE HEADERONLY
FROM AW_TSQL_BackupDevice

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

USE AdventureWorks;
GO
SELECT * FROM Person.Contact
WHERE FirstName = 'Popeye'

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

DELETE FROM Person.Contact
WHERE FirstName = 'Popeye'

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

USE Master;
GO
BACKUP LOG AdventureWorks 
   TO AW_TSQL_BackupDevice
	WITH NORECOVERY

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

USE Master;
GO
RESTORE DATABASE AdventureWorks 
   FROM AW_TSQL_BackupDevice
        WITH NORECOVERY

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

RESTORE HEADERONLY
FROM AW_TSQL_BackupDevice

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

RESTORE DATABASE AdventureWorks
   FROM AW_TSQL_BackupDevice
   WITH FILE = 2, NORECOVERY

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

RESTORE LOG AdventureWorks
   FROM AW_TSQL_BackupDevice
   WITH FILE = 3, NORECOVERY

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

RESTORE LOG AdventureWorks
   FROM AW_TSQL_BackupDevice
   WITH FILE = 4, RECOVERY

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

Rem this can be pasted into a batch file

sqlservr – m 

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

Rem this can be pasted into a batch file

Start /wait d:\servers\setup.exe /qn instancename = instanceName reinstall = SQL_Engine REBUILDDATABASE=1 sapwd = sapassword