SQL Server full, differential, and transaction log backups and restores (scripting and GUI implementations)

Original: SQL Server full, differential, and transaction log backup and restore (script and GUI implementation) [original]

A Scripts for full backups, differential backups, and transaction log backups

--Full backup Database

BACKUP DATABASE  to DISK = ' E:\20150609_75\bak\Test_bak_full.bak '  with Init

--Differential BACKUP Database

BACKUP DATABASE  to DISK = ' E:\20150609_75\bak\Test_bak_diff.bak '  with INIT, differential   -- plus differential stands for differential backup

--Transaction log backup

BACKUP LOG  to DISK = ' E:\20150609_75\bak\Test_bak_log.bak '  with INIT   -backuplog means backing up the transaction log, backup database represents a full or differential backup

--Back up the transaction log with the current time in the file name, suitable for scheduled backups

DECLARE @strbackup NVARCHAR( -) --Change to date plus time SET @strbackup = 'E:\20150609_75\bak\Test_bak_log_'     + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), -),'-',"'),' ',                      "'),':',"')+ '. bak' BACKUP LOGTest_bak to DISK = @strbackup  withINIT;GO

Two. Restore a multi-file database (partitioned) that requires modification of the data file path

Error:

The reason for the above error is: The original backup of the data file under the G disk, restore the full backup of the existing server does not exist under the G disk this path will be error.

WORKAROUND: You can use the with move to save the data file to a new valid path, first establishing the valid path under disk.

The correct way to restore a multi-file backup is as follows:

Method 1 (script):

To restore a full backup:

RESTORE DATABASE [digitallibdb]  from  DISK =N'E:\20150609_75\DigitalLibDB_20150609_FULL.bak'   --path to full backup withMOVE'Digitallibdb_data'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb.mdf', MOVE'UI0'  to 'C:\Program Files\Microsoft SQL--Re-specify the path of the file, depending on the number of files Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_1.ndf ', MOVE'UI1'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_2.ndf', MOVE'UI10'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_3.ndf', MOVE'UI11'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_4.ndf', MOVE'UI12'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_5.ndf', MOVE'UI13'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_6.ndf', MOVE'UI14'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_7.ndf', MOVE'UI15'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_8.ndf', MOVE'UI16'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_9.ndf', MOVE'UI2'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_10.ndf', MOVE'UI3'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_11.ndf', MOVE'UI4'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_12.ndf', MOVE'UI5'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_13.ndf', MOVE'UI6'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_14.ndf', MOVE'UI7'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_15.ndf', MOVE'UI8'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_16.ndf', MOVE'UI9'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_17.ndf', MOVE'Ftrow_reginfo_unitname'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_18.ndf', MOVE'Digitallibdb_log'  to 'C:\Program Files\Microsoft SQL Server\mssql10_50.mssqlserver\mssql\data\digitallibdb_log.ldf', STATS= Ten,REPLACE, NORECOVERYGO

With move to: Re-specify the path to the file, with the number of move to depends on the number of database files

STATS = 10: Not completed 10% shows a record

REPLACE: Overwrite an existing database

NORECOVERY: Do not perform any operations on the database without rolling back uncommitted transactions

Full backup results:

Restore differential backup: (Only one differential backup, when restoring multiple differential backups, usually preceded by with NORECOVERY, to restore the last differential backup to use with RECOVERY)

Note: Moving a data file is a full backup when moving, differential backups and log backups do not save file paths, differential backups and log backups cannot be moved when restoring.

RESTORE DATABASE [digitallibdb] from disk= ' E:\20150609_75\ new Folder \digitallibdb_20150610_diff.bak ' with RECOVERY

Differential Backup Restore results:

Method 2: (visual interface operation)

To restore a full backup:

Select Restore Database

Select Full Backup path

Select Overwrite existing database, select Restore WITH NORECOVERY option

Restore full backup succeeded

At this point, the differential backup is not complete and the database is still in the restoring state

Restore differential backups, select Tasks-Restore-Files and filegroups

Therefore, restoring this differential backup at this time should be done using a script without moving the file.

RESTORE DATABASE [digitallibdb] from disk= ' E:\20150609_75\ new Folder \digitallibdb_20150610_diff.bak ' with RECOVERY-- To restore a differential backup

Three You do not need to modify the differential backup restore of the database file path

Attention:

For a differential backup restore that does not need to modify the database file path, it is no problem to use SQL Server visual interface restore.

Here, the differential backup was successful because the file was the directory under the C drive when the backup was restored, or the directory was restored to the C drive, and no path modification was required.

For database full, differential, and transaction log backups that do not need to modify the database file path, the restore process is as follows (using scripting):

--Restore sequence: Full backup--differential backup--log backup

--Restore a full backup of the single-file database, the database file path is consistent, without moving the path of the situation

RESTORE DATABASE [test_bak]  from  DISK = N'E:\20150609_75\local\Test_Bak_full.bak'with= Ten REPLACE , NORECOVERY GO

--Restore differential backups

RESTORE DATABASE [test_bak]  from  DISK = N'E:\20150609_75\local\Test_Bak_diff.bak'   with=  Ten, NORECOVERYGO

--Restore Log backups

RESTORE DATABASE [test_bak]  from  DISK = N'E:\20150609_75\local\Test_Bak_Log.bak'   with=  Ten, RECOVERY

I hope you have some help, if you feel good, please recommend on the right side Oh, welcome to shoot Bricks ~

SQL Server full, differential, and transaction log backups and restores (scripting and GUI implementations)