SQL Service Database BACKUP & RESTORE

1. Database backup Under the full recovery model

1  UseMaster;2 ALTER DATABASEAdventureWorks2012SETRECOVERY Full;3 GO4 --The AdventureWorks2012 database to new media set (backup Set 1).5 BACKUP DATABASEAdventureWorks20126    to DISK = 'Z:\SQLServerBackups\AdventureWorks2012FullRM.bak' 7    withFORMAT;8 GO9 --Create A routine log backup (backup set 2).Ten BACKUP LOGAdventureWorks2012 to DISK = 'Z:\SQLServerBackups\AdventureWorks2012FullRM.bak'; One GO

View Code

2. Full database restore (Full recovery model)

1  UseMaster;2 --Create tail-log Backup.3 BACKUP LOGAdventureWorks20124  to DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'  5     withNORECOVERY;6 GO7 --Restore The full database backup (from backup set 1).8 RESTORE DATABASEAdventureWorks20129    from DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak' Ten    with FILE=1,  One NORECOVERY; A  - --Restore The regular log backup (from backup set 2). - RESTORE LOGAdventureWorks2012 the    from DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'  -    with FILE=2,  - NORECOVERY; -  + --Restore The Tail-log backup (from backup set 3). - RESTORE LOGAdventureWorks2012 +    from DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak' A    with FILE=3,  at NORECOVERY; - GO - --Recover the database: - RESTORE DATABASEAdventureWorks2012 withRECOVERY; - GO

View Code

3. Generate the appropriate SQL script based on the database

SELECTName, N'ALTER DATABASE'+Name+'SET RECOVERY full; Gobackup DATABASE'+Name+'to DISK ="'C:\Databases_Backup\'+Name+'_fullrm.bak"'With FORMAT; Gobackup LOG'+Name+'to DISK ="'C:\Databases_Backup\'+Name+'_fullrm.bak"'; GO'  as [BACKUP SQL], N'RESTORE DATABASE'+Name+'From DISK ="'C:\Databases_Backup\'+Name+'_fullrm.bak"'with file=1, NORECOVERY; RESTORE LOG AdventureWorks2012 from DISK ="'C:\Databases_Backup\'+Name+'_fullrm.bak"'with file=2, NORECOVERY; RESTORE LOG AdventureWorks2012 from DISK ="'C:\Databases_Backup\'+Name+'_fullrm.bak"'with file=3, NORECOVERY; Gorestore DATABASE'+Name+'With RECOVERY; GO'  as [RESTORE SQL]  fromMaster.. sysdatabasesORDER  byName

View Code

SQL Service Database BACKUP & RESTORE