T-SQL Tutorial v1 2017

T-SQL Tutorial v1

2017



Backup and Restore

SQL Server allow to back up and restore your databases. Also, you can export and import

security certificates and keys.

TSQL Backup

?

?

?

Backup entire database to disk file

Backup partial backup to disk file

Backup database devices in a Striped Media Set

TSQL Restore

?

?

Restore entire database

Restore full and differential database backup

Backup entire database

The following example backs up the master database to a disk file.

BACKUP DATABASE Syntax:

BACKUP DATABASE { database_name }

TO backup_device [ ,...n ]

[ MIRROR TO clause ]

[ WITH { DIFFERENTIAL | [ ,...n ] } ];

BACKUP DATABASE Example:

BACKUP DATABASE master

TO DISK = 'E:\SQLServerBackup\master.bak'

WITH FORMAT;

GO

Messages:

Processed 464 pages for database 'master', file 'master' on file 1.

Processed 3 pages for database 'master', file 'mastlog' on file 1.

BACKUP DATABASE successfully processed 467 pages in 0.274 seconds (13.308 MB/sec).

Backup partial database

The following example backup partial the model database to a disk file.

BACKUP DATABASE Syntax:

BACKUP DATABASE { database_name }

READ_WRITE_FILEGROUPS [ , [ ,...n ] ]

TO backup_device [ ,...n ]

[ MIRROR TO clause ]

[ WITH { DIFFERENTIAL | [ ,...n ] } ];

Backup partial backup example:

BACKUP DATABASE model READ_WRITE_FILEGROUPS

TO DISK = 'E:\SQLServerBackup\model_partial.bak'

GO

Messages:

Processed 328 pages for database 'model', file 'modeldev' on file 1.

Processed 2 pages for database 'model', file 'modellog' on file 1.

BACKUP DATABASE...FILE=name successfully processed 330 pages in 0.325 seconds

(7.910 MB/sec).

Backup partial backup with differential example:

BACKUP DATABASE model READ_WRITE_FILEGROUPS

TO DISK = 'E:\SQLServerBackup\model_partial.dif'

WITH DIFFERENTIAL

GO

Messages:

Processed 40 pages for database 'model', file 'modeldev' on file 1.

Processed 2 pages for database 'model', file 'modellog' on file 1.

BACKUP DATABASE...FILE=name WITH DIFFERENTIAL successfully processed

42 pages in 0.147 seconds (2.182 MB/sec).

Backup database devices

Backup database devices in a Striped Media Set

The following example backup the model database devices in a striped media set.

Example:

BACKUP DATABASE model

TO DISK='E:\SQLServerBackup\1\model_1.bak',

DISK='E:\SQLServerBackup\2\model_2.bak'

WITH FORMAT,

MEDIANAME = 'modelStripedSet0',

MEDIADESCRIPTION = 'Striped media set for model database';

GO

Messages:

Processed 328 pages for database 'model', file 'modeldev' on file 1.

Processed 2 pages for database 'model', file 'modellog' on file 1.

BACKUP DATABASE successfully processed 330 pages in 0.350 seconds (7.345

MB/sec).

Restore entire database

Restore a full database backup from the logical backup device.

RESTORE DATABASE Syntax:

RESTORE DATABASE { database_name }

[ FROM [ ,...n ] ]

[ WITH

{

[ RECOVERY | NORECOVERY | STANDBY = {standby_file_name} ]

| , [ ,...n ]

|,

|,

|,

|,

|,

} [ ,...n ]

]

[;]

RESTORE DATABASE Example:

USE master;

GO

RESTORE DATABASE test_2

FROM test_2;

GO

Messages:

Processed 328 pages for database 'test_2', file 'test_2' on file 1.

Processed 2 pages for database 'test_2', file 'test_2_log' on file 1.

RESTORE DATABASE successfully processed 330 pages in 0.276 seconds (9.333

MB/sec).

Restore full and differential database

backup

Restore a full database backup followed by a differential backup from backup device.

RESTORE DATABASE Syntax:

RESTORE DATABASE [ database_name ]

FROM DISK = [ backup_path ]

WITH FILE = [ file_number]

RECOVERY;

RESTORE DATABASE Example:

RESTORE DATABASE AdventureWorks2012 FROM DISK =

'Z:\SQLServerBackups\AdventureWorks2012.bak' WITH FILE = 9 RECOVERY;

Messages:

Processed 328 pages for database 'test_2', file 'test_2' on file 1.

Processed 2 pages for database 'test_2', file 'test_2_log' on file 1.

RESTORE DATABASE successfully processed 330 pages in 0.276 seconds (9.333

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download