Backup manual

Backup manual

Comanda generala pentru back-up manual este:

Backup toata baza de date:

BACKUP DATABASE

TO < backup_device > n ]

[ WITH

[ BLOCKSIZE = ]

, ] DESCRIPTION = ]

, ] DIFFERENTIAL ]

, ] EXPIREDATE =

| RETAINDAYS = ]

[ [ , ] PASSWORD = ]

[ [ , ] FORMAT | NOFORMAT ]

, ] ]

, ] MEDIADESCRIPTION = ]

[ [ , ] MEDIANAME = ]

, ] MEDIAPASSWORD = ]

, ] NAME = ]

, ] ]

, ] ]

, ] ]

, ] RESTART ]

, ] STATS [ = percentage ] ]

]

Backup files sau filegroups specifice:

BACKUP DATABASE

< file_or_filegroup > n ]

TO < backup_device > n ]

[ WITH

[ BLOCKSIZE = ]

, ] DESCRIPTION = ]

, ] DIFFERENTIAL ]

, ] EXPIREDATE =

| RETAINDAYS = ]

[ [ , ] PASSWORD = ]

[ [ , ] FORMAT | NOFORMAT ]

, ] ]

, ] MEDIADESCRIPTION = ]

[ [ , ] MEDIANAME = ]

, ] MEDIAPASSWORD = ]

, ] NAME = ]

, ] ]

, ] ]

, ] ]

, ] RESTART ]

, ] STATS [ = percentage ] ]

]

Backup pentru un transaction log:

BACKUP LOG

{

TO < backup_device > n ]

[ WITH

[ BLOCKSIZE = ]

, ] DESCRIPTION = ]

,] EXPIREDATE =

| RETAINDAYS = ]

, ] PASSWORD = ]

, ] FORMAT | NOFORMAT ]

, ] ]

, ] MEDIADESCRIPTION = ]

[ [ , ] MEDIANAME = ]

, ] MEDIAPASSWORD = ]

, ] NAME = ]

, ] NO_TRUNCATE ]

, ] ]

, ] ]

, ] ]

, ] ]

, ] RESTART ]

, ] STATS [ = percentage ] ]

]

}

unde:

< backup_device > ::=

|

=

}

si:

< file_or_filegroup > ::=

|

FILEGROUP =

}

Trunchiere transaction log:

BACKUP LOG

]

}

Exemple

• Backup o intreaga baza de date

-- se creeaza mai intai un backup device logic pentru backup-ul full al bazei de date Petito

USE master

EXEC sp_addumpdevice 'disk', 'MyPetito',

DISK ='c Program FilesMicrosoft SQL ServerMSSQLBACKUPMyPetito.dat'

-- Backup baza de date Petito.

BACKUP DATABASE petito TO MyPetito

• Backup DB si LOG

-- se creaza mai intai backup device pt full backup pt DB petito.

USE master

EXEC sp_addumpdevice 'disk', 'MyPetito_2',

'c:Program FilesMicrosoft SQL ServerMSSQLBACKUPMyPetitio_2.dat'

-- se creaza log backup device.

USE master

EXEC sp_addumpdevice 'disk', 'MyPetitoLog1',

'c:Program FilesMicrosoft SQL ServerMSSQLBACKUPMyPetitoLogdat'

-- Backup full baza de date petito.

BACKUP DATABASE petito TO MyPetito_2

-- Backup log pt baza de date petito.

BACKUP LOG petito

TO MyPetitoLog1

• Backup diferential

BACKUP DATABASE Northwind

TO DISK = 'D:backupsnwindnwind_diff.bak'

WITH DIFFERENTIAL

• Backup de fisiere/filegroups

BACKUP DATABASE files

FILEGROUP = 'fg1 '

TO DISK = 'd:backupsfilesfgbak' WITH INIT