Backup and Restore

Question 1

What are the four different media types for backup and restore procedures?

Answer 1

File (local or remote)
URL
Backup Device
Tape

Question 2

What is the syntax to restore a database from backup (file)?

Answer 2

USE MASTER
RESTORE DATABASE [DATABASENAME] FROM DISK = N'C:\LOCATION' 
WITH FILE = 1,
MOVE N'DATAFILE' TO N'C:\LOCATION',
MOVE N'LOGFILE' TO N'C:\LOCATION'

Question 3

What are the different recovery models available for SQL Servers?

Answer 3

SIMPLE
FULL
BULK LOGGED

Question 4

What is the SIMPLE Recovery model for SQL?

Answer 4

No details are kept in the transaction log so the log file is small.

Question 5

What is the FULL Recovery model for SQL?

Answer 5

All transactions are recorded in the log file and they are able to be restored

Question 6

What is the BULK LOGGED Recovery model for SQL?

Answer 6

All transactions are recorded in the log file and they are able to be restored

Question 7

What is the syntax for changing the recovery model of a database?

Answer 7

USE MASTER
GO
ALTER DATABASE [DATABASENAME] SET RECOVERY WITH NO_WAIT
GO

Question 8

Where is the location of the recovery model in the GUI?

Answer 8

DATABASE->Properties->Options->Recovery Model

Question 9

What are the three different backup types?

Answer 9

FULL
DIFFERENTIAL
TRANSACTION (Simple cannot do transaction backups)

Question 10

What’s the difference between full and bulk logged recovery models?

Answer 10

With Bulk Logged, you can determine if you want to keep bulk inserted records into the transaction log.

Question 11

What is a FULL backup?

Answer 11

It backs up everything in the database. Creates a new baseline for differential backups.

Question 12

What is a DIFFERENTIAL backup?

Answer 12

It backs up everything since the last backup (FULL or DIFFERENTIAL)

Question 13

What is a TRANSACTION LOG BACKUP?

Answer 13

It backs up every transaction since the last backup, whether it was FULL, DIFFERENTIAL OR TRANSACTION LOG.

Question 14

How do you restore back to a specific point in time?

Answer 14

Restore in this order:

Full
Differential
All Transaction backups until the time needed

Question 15

What are the different recovery states that you can put for a database restore?

Answer 15

RECOVERY
NORECOVERY
STANDBY

Question 16

What does RECOVERY state mean?

Answer 16

It brings the database up in full access mode.

Question 17

What does STANDBY state mean?

Answer 17

It brings up the database in READ-ONLY mode.

Question 18

What does NORECOVERY state mean?

Answer 18

It makes it so the database is not accessible, but you can still make changes to the database settings. Useful if you are planning on doing more data restoration to it.

Question 19

What is a tail log backup?

Answer 19

When you do a restore to a specific time, the tail log tries to get a new TRANSACTION log that it can then use to get the restore.

Question 20

What is the syntax for finding out how much space a full database will require?

Answer 20

EXEC SP_SPACEUSED –USE TABLE IN QUOTES TO SEE SPECIFICALLY HOW MUCH SPACE A TABLE USES

Question 21

What is a COPY-ONLY backup?

Answer 21

It does not disrupt the main backups that you are doing. It is strictly a copy. Used with Transaction Logs. Sometimes with full.

Question 22

What are the reliability settings when performing a backup?

Answer 22

Verify backup when finished
Perform Checksum before writing to media
Continue on Error

Question 23

What is the syntax for moving forward with a restore even if there are problems?

Answer 23

Use CONTINUE_AFTER_ERROR

RESTORE DATABASE [databasename] FROM DISK = N’C:\PATH’ WITH CONTINUE_AFTER_ERROR

Question 24

How do you check a database integrity?

Answer 24

DBCC CHECKDB ([DATABASE])
If you want to repair:
DBCC CHECKDB ([DATABASE], REPAIR_ALLOW_DATA_LOSS)

For that though, you need to put Database into Single User Mode

Question 25

What is the syntax to put a database into Single User Mode?

Answer 25

ALTER DATABASE [DATABASENAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Question 26

What are the different user modes that you can set for a database?

Answer 26

MULTI_USER
SINGLE_USER
RESTRICTED_USER