਍㰀栀攀愀搀㸀ഀഀ Antapex.org: Overview of some often used SQL Server TSQL code ਍㰀⼀栀攀愀搀㸀ഀഀ ਍ഀഀ

Quick Backup & Restore SQL Server

਍ഀഀ Version : 1.0
਍㰀䈀㸀䐀愀琀攀㰀⼀䈀㸀ऀ㨀 ㈀　⼀　㜀⼀㈀　㄀㄀㰀戀爀㸀ഀഀ By : Albert van der Sel
਍ഀഀ
਍㰀戀爀㸀ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ Main Contents:
਍㰀戀爀㸀ഀഀ ਍㰀䄀 栀爀攀昀㴀∀⌀猀攀挀琀椀漀渀㄀∀㸀 ㄀⸀ 䈀䄀䌀䬀唀倀 ☀ 刀䔀匀吀伀刀䔀 伀䘀 刀䔀䜀唀䰀䄀刀 唀匀䔀刀 䐀䄀吀䄀䈀䄀匀䔀匀㰀⼀䄀㸀㰀戀爀㸀ഀഀ 2. BACKUP & RESTORE OF SYSTEM DATABASES
਍㰀䄀 栀爀攀昀㴀∀⌀猀攀挀琀椀漀渀㌀∀㸀 ㌀⸀ 圀䠀䄀吀 䤀䘀 吀䠀䔀 吀刀䄀一匀䄀䌀吀䤀伀一 䰀伀䜀 䤀匀 䘀唀䰀䰀㰀⼀䄀㸀㰀戀爀㸀ഀഀ ਍㰀⼀䈀㸀ഀഀ
਍㰀戀爀㸀ഀഀ
਍ഀഀ ਍㰀栀㌀ 椀搀㴀∀猀攀挀琀椀漀渀㄀∀㸀㄀⸀ 䈀䄀䌀䬀唀倀 ☀ 刀䔀匀吀伀刀䔀 伀䘀 刀䔀䜀唀䰀䄀刀 唀匀䔀刀 䐀䄀吀䄀䈀䄀匀䔀匀㨀㰀⼀栀㌀㸀㰀戀爀㸀ഀഀ ਍ഀഀ At least, the following backup policies are possible with regards to regular user databases:
਍㰀戀爀㸀ഀഀ
    ਍㰀氀椀㸀 䘀甀氀氀 戀愀挀欀甀瀀猀 漀渀氀礀Ⰰ 眀栀椀挀栀 愀爀攀 愀氀眀愀礀猀 挀漀渀猀椀猀琀攀渀琀⸀ 匀甀挀栀 愀 戀愀挀欀甀瀀 挀愀渀 戀攀 爀攀猀琀漀爀攀搀 琀漀 最攀琀 琀栀攀 搀愀琀愀戀愀猀攀 戀愀挀欀㰀戀爀㸀ഀഀ to the situation at the time the backup was created.
    ਍吀栀椀猀 挀愀渀 愀氀眀愀礀猀 戀攀 搀漀渀攀 眀椀琀栀 愀渀礀 搀愀琀愀戀愀猀攀 ∀爀攀挀漀瘀攀爀礀 洀漀搀攀∀ ⠀䘀甀氀氀Ⰰ 匀椀洀瀀氀攀Ⰰ 䈀甀氀欀氀漀最最攀搀⤀㰀⼀氀椀㸀ഀഀ
    ਍㰀氀椀㸀 䘀甀氀氀 戀愀挀欀甀瀀 椀渀 挀漀洀戀椀渀愀琀椀漀渀 眀椀琀栀 氀愀琀攀爀 吀爀愀渀猀愀挀琀椀漀渀氀漀最 戀愀挀欀甀瀀猀⸀ 吀栀攀 吀爀愀渀猀愀挀琀椀漀渀 氀漀最 戀愀挀欀甀瀀猀Ⰰ 挀漀渀琀愀椀渀㰀戀爀㸀ഀഀ the changes with respect to the former backup (whether that's a full- or transactionlog backup).
    ਍䤀渀 挀愀猀攀 漀昀 愀 爀攀猀琀漀爀攀 愀挀琀椀漀渀Ⰰ 爀攀猀琀漀爀攀 琀栀攀 䘀甀氀氀 昀椀爀猀琀Ⰰ 愀渀搀 琀栀攀渀 爀攀猀琀漀爀攀 愀氀氀 猀甀戀猀攀焀甀攀渀琀 琀爀愀渀猀愀挀琀椀漀渀 氀漀最 戀愀挀欀甀瀀猀Ⰰ㰀戀爀㸀ഀഀ from the first transaction log backup, all the way up to the latest backup.
    ਍一漀琀攀㨀 吀栀攀 搀愀琀愀戀愀猀攀 渀攀攀搀猀 琀漀 甀猀攀 琀栀攀 ∀䘀甀氀氀 爀攀挀漀瘀攀爀礀 洀漀搀攀氀∀ ⠀眀栀椀挀栀 椀猀 猀漀洀攀眀栀愀琀 挀漀洀瀀愀爀愀戀氀攀 琀漀 ∀愀爀挀栀椀瘀攀 洀漀搀攀∀ 椀渀 伀爀愀挀氀攀⤀⸀㰀戀爀㸀㰀⼀氀椀㸀ഀഀ
    ਍㰀氀椀㸀䘀甀氀氀 戀愀挀欀甀瀀 椀渀 挀漀洀戀椀渀愀琀椀漀渀 眀椀琀栀 氀愀琀攀爀 䐀椀昀昀攀爀攀渀琀椀愀氀 戀愀挀欀甀瀀猀⸀ 吀栀攀 䐀椀昀昀攀爀攀渀琀椀愀氀 戀愀挀欀甀瀀猀Ⰰ 挀漀渀琀愀椀渀㰀戀爀㸀ഀഀ the changes with respect to the last FULL backup.
    ਍䰀愀琀攀爀 搀椀昀昀攀爀攀渀琀椀愀氀 戀愀挀欀甀瀀猀 眀椀氀氀 琀栀甀猀 最爀漀眀 椀渀 猀椀稀攀⸀ 䤀渀 挀愀猀攀 漀昀 愀 爀攀猀琀漀爀攀 愀挀琀椀漀渀Ⰰ 爀攀猀琀漀爀攀 琀栀攀 䘀甀氀氀 戀愀挀欀甀瀀 昀椀爀猀琀Ⰰ㰀戀爀㸀ഀഀ and then ONLY the most recent Differential backup.
    ਍㰀戀爀㸀ഀഀ This policy works best when the database was set in SIMPLE mode.
    ਍䠀漀眀攀瘀攀爀Ⰰ 椀琀 挀愀渀 戀攀 甀猀攀搀 椀渀 愀渀礀 搀愀琀愀戀愀猀攀 洀漀搀攀Ⰰ 栀漀眀攀瘀攀爀⸀ 圀栀攀渀 搀攀 洀漀搀攀 椀猀 猀攀琀 琀漀 䘀甀氀氀Ⰰ 礀漀甀 洀甀猀琀 洀愀欀攀 ⠀猀漀洀攀⤀㰀戀爀㸀ഀഀ transactionlog backups as well (otherwise the Tlog keeps growing and growing). ਍㰀⼀漀氀㸀ഀഀ ਍䴀愀欀攀 猀甀爀攀 琀栀愀琀 礀漀甀 甀渀搀攀爀猀琀愀渀搀 琀栀攀 搀愀琀愀戀愀猀攀 䘀唀䰀䰀 愀渀搀 匀䤀䴀倀䰀䔀 爀攀挀漀瘀攀爀礀 洀漀搀攀猀⸀㰀戀爀㸀ഀഀ
    ਍ഀഀ 1.1 Creating a FULL database backup and restore it:
    ਍㰀戀爀㸀ഀഀ - Example Full backup:
    ਍㰀戀爀㸀ഀഀ ਍䈀䄀䌀䬀唀倀 䐀䄀吀䄀䈀䄀匀䔀 吀䔀匀吀 吀伀 䐀䤀匀䬀㴀✀搀㨀尀戀愀挀欀甀瀀猀尀琀攀猀琀开昀甀氀氀⸀搀洀瀀✀ 圀䤀吀䠀 䤀一䤀吀㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ - Example restore of a full backup (and not restoring other backups):
    ਍㰀戀爀㸀ഀഀ ਍刀䔀匀吀伀刀䔀 䐀䄀吀䄀䈀䄀匀䔀 吀䔀匀吀 䘀刀伀䴀 䐀䤀匀䬀㴀✀搀㨀尀戀愀挀欀甀瀀猀尀琀攀猀琀开昀甀氀氀⸀搀洀瀀✀ 圀䤀吀䠀 刀䔀倀䰀䄀䌀䔀Ⰰ 刀䔀䌀伀嘀䔀刀夀㰀戀爀㸀ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
    ਍吀栀攀 ∀刀䔀䌀伀嘀䔀刀夀∀ 挀氀愀甀猀攀 洀攀愀渀猀 琀栀愀琀 琀栀椀猀 椀猀 礀漀甀爀 漀渀氀礀Ⰰ 漀爀 氀愀猀琀Ⰰ 戀愀挀欀甀瀀 琀漀 爀攀猀琀漀爀攀Ⰰ 愀渀搀 愀昀琀攀爀眀愀爀搀猀 琀栀攀 搀愀琀愀戀愀猀攀 渀攀攀搀猀㰀戀爀㸀ഀഀ to be recovered and opened.
    ਍㰀戀爀㸀ഀഀ
    ਍㰀䈀㸀㰀唀㸀㄀⸀㈀ 䌀爀攀愀琀椀渀最 愀 䘀唀䰀䰀 搀愀琀愀戀愀猀攀 戀愀挀欀甀瀀 愀渀搀 猀甀戀猀攀焀甀攀渀琀椀愀氀 䐀䤀䘀䘀䔀刀䔀一吀䤀䄀䰀 戀愀挀欀甀瀀猀㨀㰀⼀唀㸀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
    ਍ⴀ 䔀砀愀洀瀀氀攀 戀愀挀欀甀瀀猀㨀 昀椀爀猀琀 琀栀攀 昀甀氀氀 愀琀 攀⸀最⸀ 　㄀㨀　　栀 愀洀Ⰰ 琀栀攀渀 愀 渀甀洀戀攀爀 漀昀 搀椀昀昀猀 搀甀爀椀渀最 琀栀攀 搀愀礀⸀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ ਍䈀䄀䌀䬀唀倀 䐀䄀吀䄀䈀䄀匀䔀 吀䔀匀吀 吀伀 䐀䤀匀䬀㴀✀搀㨀尀戀愀挀欀甀瀀猀尀琀攀猀琀开昀甀氀氀⸀搀洀瀀✀ 圀䤀吀䠀 䤀一䤀吀                      ⴀⴀ 愀琀 　㄀㨀　　栀㰀戀爀㸀ഀഀ
    ਍䈀䄀䌀䬀唀倀 䐀䄀吀䄀䈀䄀匀䔀 吀䔀匀吀 吀伀 䐀䤀匀䬀㴀✀搀㨀尀戀愀挀欀甀瀀猀尀琀攀猀琀开搀椀昀昀开　㔀　　⸀搀洀瀀✀ 圀䤀吀䠀 䐀䤀䘀䘀䔀刀䔀一吀䤀䄀䰀Ⰰ 䤀一䤀吀   ⴀⴀ 愀琀 　㔀㨀　　栀㰀戀爀㸀ഀഀ
    ਍䈀䄀䌀䬀唀倀 䐀䄀吀䄀䈀䄀匀䔀 吀䔀匀吀 吀伀 䐀䤀匀䬀㴀✀搀㨀尀戀愀挀欀甀瀀猀尀琀攀猀琀开搀椀昀昀开　㤀　　⸀搀洀瀀✀ 圀䤀吀䠀 䐀䤀䘀䘀䔀刀䔀一吀䤀䄀䰀Ⰰ 䤀一䤀吀   ⴀⴀ 愀琀 　㤀㨀　　栀㰀戀爀㸀ഀഀ
    ਍䈀䄀䌀䬀唀倀 䐀䄀吀䄀䈀䄀匀䔀 吀䔀匀吀 吀伀 䐀䤀匀䬀㴀✀搀㨀尀戀愀挀欀甀瀀猀尀琀攀猀琀开搀椀昀昀开㄀㄀　　⸀搀洀瀀✀ 圀䤀吀䠀 䐀䤀䘀䘀䔀刀䔀一吀䤀䄀䰀Ⰰ 䤀一䤀吀   ⴀⴀ 愀琀 ㄀㄀㨀　　栀㰀戀爀㸀ഀഀ
    ਍䈀䄀䌀䬀唀倀 䐀䄀吀䄀䈀䄀匀䔀 吀䔀匀吀 吀伀 䐀䤀匀䬀㴀✀搀㨀尀戀愀挀欀甀瀀猀尀琀攀猀琀开搀椀昀昀开㄀㌀　　⸀搀洀瀀✀ 圀䤀吀䠀 䐀䤀䘀䘀䔀刀䔀一吀䤀䄀䰀Ⰰ 䤀一䤀吀   ⴀⴀ 愀琀 ㄀㌀㨀　　栀㰀戀爀㸀ഀഀ
    ਍䈀䄀䌀䬀唀倀 䐀䄀吀䄀䈀䄀匀䔀 吀䔀匀吀 吀伀 䐀䤀匀䬀㴀✀搀㨀尀戀愀挀欀甀瀀猀尀琀攀猀琀开搀椀昀昀开㄀㜀　　⸀搀洀瀀✀ 圀䤀吀䠀 䐀䤀䘀䘀䔀刀䔀一吀䤀䄀䰀Ⰰ 䤀一䤀吀   ⴀⴀ 愀琀 ㄀㜀㨀　　栀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍䤀洀瀀漀爀琀愀渀琀㨀 䄀 搀椀昀昀攀爀攀渀琀椀愀氀 戀愀挀欀甀瀀 挀漀渀琀愀椀渀猀 愀氀氀 琀栀攀 搀攀氀琀愀✀猀 眀椀琀栀 爀攀猀瀀攀挀琀 琀漀 琀栀攀 氀愀猀琀 昀甀氀氀 戀愀挀欀甀瀀⸀㰀戀爀㸀ഀഀ So, differential backups taken at a later time, are expected to be larger compared to earlier diff backups.
    ਍㰀戀爀㸀ഀഀ
    ਍㰀䈀㸀㰀唀㸀㄀⸀㌀ 刀攀猀琀漀爀椀渀最 愀 䘀唀䰀䰀 搀愀琀愀戀愀猀攀 戀愀挀欀甀瀀 昀漀氀氀漀眀攀搀 戀礀 愀 爀攀猀琀漀爀攀 漀昀 ⠀漀渀氀礀⤀ 琀栀攀 氀愀琀攀猀琀 䐀䤀䘀䘀䔀刀䔀一吀䤀䄀䰀 戀愀挀欀甀瀀㨀㰀⼀唀㸀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
    ਍䘀椀爀猀琀 礀漀甀 爀攀猀琀漀爀攀 琀栀攀 昀甀氀氀 眀椀琀栀 琀栀攀 ∀圀䤀吀䠀 一伀刀䔀䌀伀嘀䔀刀夀∀ 挀氀愀甀猀攀Ⰰ 琀栀攀渀 伀一䰀夀 爀攀猀琀漀爀攀 琀栀攀 䰀䄀吀䔀匀吀 搀椀昀昀攀爀攀渀琀椀愀氀 戀愀挀欀甀瀀㰀戀爀㸀ഀഀ using the "WITH RECOVERY" clause.
    ਍㰀戀爀㸀 ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ ਍刀䔀匀吀伀刀䔀 䐀䄀吀䄀䈀䄀匀䔀 吀䔀匀吀 䘀刀伀䴀 䐀䤀匀䬀㴀✀搀㨀尀戀愀挀欀甀瀀猀尀琀攀猀琀开昀甀氀氀⸀搀洀瀀✀ 圀䤀吀䠀 刀䔀倀䰀䄀䌀䔀Ⰰ 一伀刀䔀䌀伀嘀䔀刀夀 ⴀⴀ 昀椀爀猀琀 爀攀猀琀漀爀攀 琀栀攀 昀甀氀氀 戀愀挀欀甀瀀㰀戀爀㸀ഀഀ
    ਍刀䔀匀吀伀刀䔀 䐀䄀吀䄀䈀䄀匀䔀 吀䔀匀吀 䘀刀伀䴀 䐀䤀匀䬀㴀✀搀㨀尀戀愀挀欀甀瀀猀尀琀攀猀琀开搀椀昀昀开㄀㜀　　⸀搀洀瀀✀ 圀䤀吀䠀 刀䔀䌀伀嘀䔀刀夀 ⴀⴀ 琀栀攀渀 爀攀猀琀漀爀攀 漀渀氀礀 琀栀攀 氀愀琀攀猀琀 搀椀昀昀 戀愀挀欀甀瀀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍吀栀攀 愀戀漀瘀攀 攀砀愀洀瀀氀攀 愀猀猀甀洀攀猀 愀 挀爀愀猀栀 栀愀瀀瀀攀渀攀搀 愀昀琀攀爀 ㄀㜀㨀　　栀⸀㰀戀爀㸀 ഀഀ In that case, use only the full from 01:00h and the diff backup from 17.00h.
    ਍㰀戀爀㸀ഀഀ Suppose a crash happened at 11.45h.
    ਍䤀渀 琀栀愀琀 挀愀猀攀Ⰰ 甀猀攀 漀渀氀礀 琀栀攀 昀甀氀氀 昀爀漀洀 　㄀㨀　　栀 愀渀搀 琀栀攀 搀椀昀昀 戀愀挀欀甀瀀 昀爀漀洀 ㄀㄀⸀　　栀⸀㰀戀爀㸀ഀഀ
    ਍一漀琀攀㨀 琀栀椀猀 戀愀挀欀甀瀀⼀爀攀猀琀漀爀攀 瀀漀氀椀挀礀 椀猀 椀渀搀攀瀀攀渀搀攀渀琀 昀爀漀洀 礀漀甀爀 搀愀琀愀戀愀猀攀 ∀爀攀挀漀瘀攀爀礀 洀漀搀攀氀∀Ⰰ 氀椀欀攀 ∀昀甀氀氀∀ 漀爀 ∀猀椀洀瀀氀攀∀ 漀爀 ∀戀甀氀欀 氀漀最最攀搀∀⸀㰀戀爀㸀ഀഀ So, you can ALWAYS use this backup/restore policy.
    ਍㰀戀爀㸀ഀഀ
    ਍㰀䈀㸀㰀唀㸀㄀⸀㐀 䌀爀攀愀琀椀渀最 愀 䘀唀䰀䰀 搀愀琀愀戀愀猀攀 戀愀挀欀甀瀀 愀渀搀 猀甀戀猀攀焀甀攀渀琀椀愀氀 吀刀䄀一匀䄀䌀吀䤀伀一 䰀伀䜀 戀愀挀欀甀瀀猀㨀㰀⼀唀㸀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
    ਍䤀䴀倀伀刀吀䄀一吀㨀 琀栀攀 昀漀氀氀漀眀椀渀最 戀愀挀欀甀瀀 瀀漀氀椀挀礀 漀渀氀礀 眀漀爀欀猀 椀昀 礀漀甀爀 搀愀琀愀戀愀猀攀 甀猀攀猀 琀栀攀 䘀甀氀氀 ∀爀攀挀漀瘀攀爀礀 洀漀搀攀氀∀㰀戀爀㸀ഀഀ
    ਍ⴀ 䔀砀愀洀瀀氀攀 戀愀挀欀甀瀀猀㨀 昀椀爀猀琀 琀栀攀 昀甀氀氀 愀琀 攀⸀最⸀ 　㄀㨀　　栀 愀洀Ⰰ 琀栀攀渀 愀 渀甀洀戀攀爀 漀昀 吀刀䄀一匀䄀䌀吀䤀伀一 䰀伀䜀 戀愀挀欀甀瀀猀 搀甀爀椀渀最 琀栀攀 搀愀礀⸀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ BACKUP DATABASE TEST TO DISK='d:\backups\test_full.dmp' WITH INIT -- at 01:00h
    ਍㰀戀爀㸀 ഀഀ BACKUP LOG TEST TO DISK='d:\backups\test_log_0500.dmp' WITH INIT -- at 05:00h
    ਍㰀戀爀㸀 ഀഀ BACKUP LOG TEST TO DISK='d:\backups\test_log_0900.dmp' WITH INIT -- at 09:00h
    ਍㰀戀爀㸀 ഀഀ BACKUP LOG TEST TO DISK='d:\backups\test_log_1100.dmp' WITH INIT -- at 11:00h
    ਍㰀戀爀㸀 ഀഀ BACKUP LOG TEST TO DISK='d:\backups\test_log_1300.dmp' WITH INIT -- at 13:00h
    ਍㰀戀爀㸀 ഀഀ BACKUP LOG TEST TO DISK='d:\backups\test_log_1700.dmp' WITH INIT -- at 17:00h
    ਍㰀戀爀㸀 ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍ഀഀ (Note: database TEST needs to use the Full recovery model)
    ਍㰀戀爀㸀ഀഀ A Transaction log backup, contains the "delta's" compared to the last former backup, whether that was
    ਍愀 䘀甀氀氀 戀愀挀欀甀瀀Ⰰ 漀爀 愀 琀爀愀渀猀愀挀琀椀漀渀 氀漀最 戀愀挀欀甀瀀⸀㰀戀爀㸀ഀഀ So, at a restore action, you need to apply all your transaction log backups that are available.
    ਍㰀戀爀㸀ഀഀ
    ਍㰀䈀㸀㰀唀㸀㄀⸀㔀 刀攀猀琀漀爀椀渀最 愀 䘀唀䰀䰀 搀愀琀愀戀愀猀攀 戀愀挀欀甀瀀 昀漀氀氀漀眀攀搀 戀礀 愀 爀攀猀琀漀爀攀 漀昀 䄀䰀䰀 猀甀戀猀攀焀攀渀琀椀愀氀 吀爀愀渀猀愀挀琀椀漀渀 䰀漀最 戀愀挀欀甀瀀猀㨀㰀⼀唀㸀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
    ਍匀甀瀀瀀漀猀攀 琀栀愀琀Ⰰ 甀猀椀渀最 琀栀攀 洀漀搀攀氀 猀挀栀攀琀挀栀攀搀 椀渀 猀攀挀琀椀漀渀 ㄀⸀㐀Ⰰ 愀 挀爀愀猀栀 漀挀挀甀爀爀攀搀 愀琀 ㄀㄀⸀㐀㔀栀⸀㰀戀爀㸀ഀഀ What backups would you use to restore your database?
    ਍匀椀渀挀攀 愀 琀爀愀渀猀愀挀琀椀漀渀 氀漀最 戀愀挀欀甀瀀Ⰰ 挀漀渀琀愀椀渀猀 漀渀氀礀 琀栀攀 挀栀愀渀最攀猀 爀攀氀愀琀椀瘀攀 琀漀 琀栀攀 搀椀爀攀挀琀 昀漀爀洀攀爀 戀愀挀欀甀瀀Ⰰ 琀栀椀猀 琀椀洀攀 礀漀甀㰀戀爀㸀ഀഀ need to restore the full backup, followed by ALL applicable transaction log backups.
    ਍吀栀椀猀 椀猀 搀椀昀昀攀爀攀渀琀 昀爀漀洀 琀栀攀 搀椀昀昀攀爀攀渀琀椀愀氀 瀀漀氀椀挀礀Ⰰ 眀栀攀爀攀 礀漀甀 漀渀氀礀 渀攀攀搀攀搀 琀栀攀 昀甀氀氀ⴀ 愀渀搀 琀栀攀 氀愀猀琀 搀椀昀昀攀爀攀渀琀椀愀氀 戀愀挀欀甀瀀⸀㰀戀爀㸀ഀഀ So in this case we procede as follows:
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ RESTORE DATABASE TEST FROM DISK='d:\backups\test_full.dmp' WITH REPLACE, NORECOVERY
    ਍㰀戀爀㸀 ഀഀ RESTORE LOG TEST FROM DISK='d:\backups\test_log_0500.dmp' WITH NORECOVERY
    ਍㰀戀爀㸀 ഀഀ RESTORE LOG TEST FROM DISK='d:\backups\test_log_0900.dmp' WITH NORECOVERY
    ਍㰀戀爀㸀 ഀഀ RESTORE LOG TEST FROM DISK='d:\backups\test_log_1100.dmp' WITH RECOVERY
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ Note: only the last restore action needs the "WITH RECOVERY" clause.
    ਍㰀戀爀㸀ഀഀ Note: If the database was not fully destroyed, and it was possible to backup the transaction log
    ਍愀琀 ㄀㄀⸀㐀㔀栀 ⠀樀甀猀琀 愀昀琀攀爀 琀栀攀 挀爀愀猀栀⤀Ⰰ 椀琀 眀漀甀氀搀 椀渀 瀀爀椀渀挀椀瀀氀攀 戀攀 瀀漀猀猀椀戀氀攀 琀漀 戀愀挀欀甀瀀 琀栀攀 ∀琀愀椀氀∀ 漀昀 琀栀攀 琀爀愀渀猀愀挀琀椀漀渀 氀漀最Ⰰ㰀戀爀㸀ഀഀ thereby saving all delta's between 11.00h and 11.45h.
    ਍䈀甀琀 椀渀 洀漀猀琀 挀愀猀攀猀Ⰰ 椀琀✀猀 愀 戀椀琀 栀礀瀀漀琀栀攀琀椀挀愀氀⸀㰀戀爀㸀ഀഀ
    ਍㰀戀爀㸀ഀഀ 1.6 Restoring to a different location:
    ਍㰀戀爀㸀ഀഀ let's show you how to restore a database to different location. That is, the filesystems
    ਍愀渀搀 瀀愀琀栀 琀漀 眀栀攀爀攀 琀栀攀 搀愀琀愀戀愀猀攀 洀甀猀琀 戀攀 爀攀猀琀漀爀攀搀 琀漀Ⰰ 椀猀 搀椀昀昀攀爀攀渀琀 昀爀漀洀 琀栀攀 漀爀椀最椀渀愀氀 昀椀氀攀猀礀猀琀攀洀猀 愀渀搀⼀漀爀 瀀愀琀栀猀⸀㰀戀爀㸀ഀഀ
    ਍倀氀攀愀猀攀 猀攀攀 琀栀攀 昀漀氀氀漀眀椀渀最 攀砀愀洀瀀氀攀⸀ 䤀渀 琀栀椀猀 刀䔀匀吀伀刀䔀 猀琀愀琀攀洀攀渀琀Ⰰ 琀栀攀 䴀伀嘀䔀 漀瀀琀椀漀渀 琀攀氀氀猀 匀儀䰀 匀攀爀瘀攀爀㰀戀爀㸀ഀഀ the new location to where the files (which information is stored in the backup) need to be placed at the restore.
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ RESTORE DATABASE TEST FROM DISK='L:\temp\test_full.bak' WITH RECOVERY,
    ਍䴀伀嘀䔀 ✀琀攀猀琀开搀愀琀愀开㄀✀ 吀伀  ✀䐀㨀尀倀爀漀最爀愀洀 䘀椀氀攀猀尀䴀椀挀爀漀猀漀昀琀 匀儀䰀 匀攀爀瘀攀爀尀䴀匀匀儀䰀⸀㄀尀䴀匀匀儀䰀尀䐀愀琀愀尀琀攀猀琀开搀愀琀愀开㄀⸀洀搀昀✀Ⰰ 㰀戀爀㸀ഀഀ MOVE 'test_index_1' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_index_1.ndf',
    ਍䴀伀嘀䔀 ✀琀攀猀琀开搀愀琀愀开㈀✀ 吀伀 ✀䔀㨀尀倀爀漀最爀愀洀 䘀椀氀攀猀尀䴀椀挀爀漀猀漀昀琀 匀儀䰀 匀攀爀瘀攀爀尀䴀匀匀儀䰀⸀㄀尀䴀匀匀儀䰀尀䐀愀琀愀尀琀攀猀琀开搀愀琀愀开㈀⸀渀搀昀✀Ⰰ㰀戀爀㸀ഀഀ MOVE 'test_Log' TO 'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_Log.ldf', REPLACE
    ਍ഀഀ ਍㰀戀爀㸀ഀഀ One problem often seen here, is that you do not know the logical names, like for example the name 'test_data_1'.
    ਍䘀漀爀 愀渀 攀砀椀猀琀椀渀最 搀愀琀愀戀愀猀攀Ⰰ 琀栀攀 氀漀最椀挀愀氀 愀渀搀 瀀栀礀猀椀挀愀氀 昀椀氀攀渀愀洀攀猀 愀爀攀 攀愀猀礀 琀漀 昀椀渀搀 昀爀漀洀 琀栀攀 猀礀猀昀椀氀攀猀 瘀椀攀眀Ⰰ 氀椀欀攀㰀戀爀㸀ഀഀ using the query "select * from sysfiles".
    ਍㰀戀爀㸀ഀഀ Also, to retrieve logical and physical names from the backupfile itself, you can use the RESTORE FILELISTONLY command.
    ਍㰀戀爀㸀ഀഀ So, suppose you have a backupfile like d:\backups\test_full.bak, then you can retrieve the names using:
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ RESTORE FILELISTONLY from disk='d:\backups\test_full.bak'
    ਍ഀഀ ਍㰀戀爀㸀ഀഀ
    ਍ഀഀ 1.7 Backup information recorded in the MSDB Database:
    ਍㰀戀爀㸀ഀഀ The MSDB database also contain historical information about any backups that were made.
    ਍䔀猀瀀攀挀椀愀氀氀礀 琀栀攀 猀礀猀琀攀洀 琀愀戀氀攀猀 ∀戀愀挀欀甀瀀猀攀琀∀ 愀渀搀 ∀戀愀挀欀甀瀀洀攀搀椀愀昀愀洀椀氀礀∀ 挀愀爀爀礀 椀渀琀攀爀攀猀琀椀渀最 椀渀昀漀爀洀愀琀椀漀渀Ⰰ㰀戀爀㸀ഀഀ as the below queries will make clear:
    ਍㰀戀爀㸀ഀഀ ਍ⴀⴀ 愀搀樀甀猀琀 琀栀攀 搀愀琀攀 椀渀 琀栀攀 戀攀氀漀眀 焀甀攀爀椀攀猀 愀猀 礀漀甀 猀攀攀 昀椀琀⸀㰀戀爀㸀ഀഀ
    ਍匀䔀䰀䔀䌀吀 猀甀戀猀琀爀椀渀最⠀猀⸀搀愀琀愀戀愀猀攀开渀愀洀攀Ⰰ㄀Ⰰ㈀　⤀ 愀猀 ∀搀愀琀愀戀愀猀攀∀Ⰰ ⠀猀⸀戀愀挀欀甀瀀开猀椀稀攀⼀㄀　㈀㐀⼀㄀　㈀㐀⤀ 愀猀 ∀匀椀稀攀开椀渀开䴀䈀∀Ⰰ 猀⸀琀礀瀀攀Ⰰ㰀戀爀㸀ഀഀ s.backup_start_date, s.backup_finish_date, substring(f.physical_device_name,1,30)
    ਍䘀刀伀䴀   戀愀挀欀甀瀀猀攀琀 猀Ⰰ 戀愀挀欀甀瀀洀攀搀椀愀昀愀洀椀氀礀 昀㰀戀爀㸀ഀഀ WHERE s.media_set_id=f.media_set_id
    ਍䄀一䐀    猀⸀戀愀挀欀甀瀀开猀琀愀爀琀开搀愀琀攀 㸀 ✀㈀　㄀㄀ⴀ　㔀ⴀ　㔀✀㰀戀爀㸀ഀഀ ORDER BY s.backup_start_date
    ਍㰀戀爀㸀ഀഀ ਍匀䔀䰀䔀䌀吀 戀愀挀欀甀瀀开猀琀愀爀琀开搀愀琀攀Ⰰ 戀愀挀欀甀瀀开昀椀渀椀猀栀开搀愀琀攀Ⰰ 洀攀搀椀愀开猀攀琀开椀搀Ⰰ㰀戀爀㸀ഀഀ type, substring(database_name,1,20)
    ਍䘀刀伀䴀   戀愀挀欀甀瀀猀攀琀㰀戀爀㸀ഀഀ
    ਍匀䔀䰀䔀䌀吀 戀愀挀欀甀瀀开猀琀愀爀琀开搀愀琀攀Ⰰ 戀愀挀欀甀瀀开昀椀渀椀猀栀开搀愀琀攀Ⰰ 洀攀搀椀愀开猀攀琀开椀搀Ⰰ㰀戀爀㸀ഀഀ type, database_name
    ਍䘀刀伀䴀   戀愀挀欀甀瀀猀攀琀㰀戀爀㸀ഀഀ WHERE backup_start_date>'2011-05-05'
    ਍ഀഀ ਍㰀戀爀㸀ഀഀ
    ਍㰀戀爀㸀ഀഀ ਍㰀栀㌀ 椀搀㴀∀猀攀挀琀椀漀渀㈀∀㸀㈀⸀ 䈀䄀䌀䬀唀倀 ☀ 刀䔀匀吀伀刀䔀 伀䘀 匀夀匀吀䔀䴀 䐀䄀吀䄀䈀䄀匀䔀匀㨀㰀⼀栀㌀㸀㰀戀爀㸀ഀഀ ਍ഀഀ A SQL Server instance, has 4 socalled "system databases". These are:
    ਍㰀戀爀㸀ഀഀ - master database: contains metadata about the instance, databases, logins, and much more.
    ਍ⴀ 洀猀搀戀 搀愀琀愀戀愀猀攀㨀 挀漀渀琀愀椀渀猀 樀漀戀搀攀猀挀爀椀瀀琀椀漀渀猀Ⰰ 栀椀猀琀漀爀礀 椀渀昀漀爀洀愀琀椀漀渀 漀渀 樀漀戀猀Ⰰ 戀愀挀欀甀瀀猀Ⰰ 爀攀瀀氀椀挀愀琀椀漀渀 攀琀挀⸀⸀㰀戀爀㸀ഀഀ - model database: can function as a template for new databases, but not much dba's use it.
    ਍ⴀ 琀攀洀瀀搀戀 搀愀琀愀戀愀猀攀㨀 昀甀渀挀琀椀漀渀猀 愀猀 愀 琀攀洀瀀漀爀愀爀礀 眀漀爀欀猀瀀愀挀攀 昀漀爀 猀漀爀琀 漀瀀攀爀愀琀椀漀渀猀Ⰰ 琀攀洀瀀 琀愀戀氀攀猀Ⰰ 椀渀搀攀砀 爀攀戀甀椀氀搀猀 攀琀挀⸀⸀㰀戀爀㸀ഀഀ
    ਍㰀䈀㸀㰀唀㸀㈀⸀㄀ 䈀愀挀欀甀瀀 漀昀 琀栀攀 猀礀猀琀攀洀 搀愀琀愀戀愀猀攀猀㨀㰀⼀唀㸀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
    ਍夀漀甀 搀漀 渀漀琀 渀攀攀搀 琀漀 戀愀挀欀甀瀀 琀栀攀 琀攀洀瀀搀戀 搀愀琀愀戀愀猀攀⸀ 䘀漀爀 攀砀愀洀瀀氀攀Ⰰ 椀昀 琀栀攀 琀攀洀瀀搀戀 搀愀琀愀戀愀猀攀 昀椀氀攀猀 最攀琀猀 氀漀猀琀 昀漀爀 猀漀洀攀 爀攀愀猀漀渀Ⰰ㰀戀爀㸀ഀഀ SQL Server will simply create the tempdb database again at startup. But if that happens,
    ਍礀漀甀 猀琀椀氀氀 渀攀攀搀 琀漀 挀栀攀挀欀 琀栀攀 猀椀稀攀猀 愀渀搀 琀栀攀 渀甀洀戀攀爀 漀昀 昀椀氀攀猀Ⰰ 椀渀 漀爀搀攀爀 琀漀 挀栀攀挀欀 眀栀攀琀栀攀爀㰀戀爀㸀ഀഀ tempdb is still according to your specifications.
    ਍㰀戀爀㸀ഀഀ The other system databases (master, model, msdb) are critical for proper operation.
    ਍吀栀攀猀攀 搀愀琀愀戀愀猀攀猀 甀猀甀愀氀氀礀 眀椀氀氀 戀攀 ⠀愀渀搀 爀攀洀愀椀渀⤀ 焀甀椀琀攀 猀洀愀氀氀Ⰰ 椀渀 洀漀猀琀 挀愀猀攀猀 氀攀猀猀 琀栀愀渀 ㌀　 䴀䈀 漀爀 猀漀⸀ 匀漀Ⰰ 挀爀攀愀琀椀渀最 戀愀挀欀甀瀀猀㰀戀爀㸀ഀഀ should be a matter of seconds.
    ਍㰀戀爀㸀ഀഀ You should only make full backups of these databases. As said above, the databases are very small and the backups
    ਍眀椀氀氀 渀漀琀 漀挀挀甀瀀礀 洀甀挀栀 搀椀猀欀猀瀀愀挀攀 ⠀椀昀 礀漀甀 眀漀甀氀搀 戀愀挀欀甀瀀 琀漀 搀椀猀欀Ⰰ 眀栀椀挀栀 椀猀 爀攀挀漀洀洀攀渀搀攀搀⤀⸀㰀戀爀㸀ഀഀ
    ਍匀甀瀀瀀漀猀攀 琀栀愀琀 礀漀甀 眀漀甀氀搀 戀愀挀欀甀瀀 琀栀攀猀攀 搀愀琀愀戀愀猀攀猀 琀漀 琀栀攀 ∀䘀㨀尀猀焀氀戀愀挀欀甀瀀猀∀ 搀椀猀欀 氀漀挀愀琀椀漀渀⸀ 䄀渀 攀砀愀洀瀀氀攀 漀昀 戀愀挀欀甀瀀挀漀洀洀愀渀搀猀 琀栀攀渀㰀戀爀㸀ഀഀ could be as simple as this:
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ BACKUP DATABASE master TO DISK='F:\sqlbackups\master.dmp' WITH INIT
    ਍㰀戀爀㸀ഀഀ BACKUP DATABASE msdb TO DISK='F:\sqlbackups\msdb.dmp' WITH INIT
    ਍㰀戀爀㸀ഀഀ BACKUP DATABASE model TO DISK='F:\sqlbackups\model.dmp' WITH INIT
    ਍㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ 2.2 Restore of the system databases:
    ਍㰀戀爀㸀ഀഀ When you want to restore a regular "user" database (like for example the database "sales"), the SQL Server instance
    ਍挀愀渀 戀攀 爀甀渀渀椀渀最 椀渀 琀栀攀 甀猀甀愀氀 ∀洀甀氀琀椀ⴀ甀猀攀爀∀ 洀漀搀攀Ⰰ 猀漀 渀漀 猀瀀攀挀椀愀氀 瀀爀攀瀀攀爀愀琀椀漀渀 椀猀 渀攀挀挀攀猀猀愀爀礀 眀椀琀栀 爀攀猀瀀攀挀琀 琀漀 琀栀攀 猀琀愀琀攀 漀昀 琀栀攀 椀渀猀琀愀渀挀攀⸀㰀戀爀㸀ഀഀ
    ਍䤀琀✀猀 搀椀昀昀攀爀攀渀琀 眀栀攀渀 礀漀甀 渀攀攀搀 琀漀 爀攀猀琀漀爀攀 愀 猀礀猀琀攀洀 搀愀琀愀戀愀猀攀⸀ 䤀渀 琀栀椀猀 挀愀猀攀Ⰰ 椀昀 礀漀甀 栀愀瘀攀 氀漀猀琀 愀 猀礀猀琀攀洀 搀愀琀愀戀愀猀攀Ⰰ 礀漀甀爀 椀渀猀琀愀渀挀攀㰀戀爀㸀ഀഀ will not start anyway. But you can start it in "single user mode", which also allows you to restore a system database.
    ਍吀漀 猀琀愀爀琀 琀栀攀 椀渀猀琀愀渀挀攀 椀渀 猀椀渀最氀攀 甀猀攀爀 洀漀搀攀Ⰰ 礀漀甀 渀攀攀搀 琀漀 甀猀攀 琀栀攀 ∀⼀洀∀ 瀀愀爀愀洀攀琀攀爀 昀爀漀洀 琀栀攀 挀漀洀洀愀渀搀 氀椀渀攀⸀㰀戀爀㸀ഀഀ Here is an example of a restore of the master database:
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ NET START "MSSQLSERVER" /m
    ਍㰀戀爀㸀ഀഀ Next, connect with SQLCMD or the management studio, and use the following TSQL command:
    ਍㰀戀爀㸀ഀഀ RESTORE DATABASE MASTER FROM DISK='F:\sqlbackups\master.dmp' WITH REPLACE, RECOVERY
    ਍䜀伀㰀戀爀㸀ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
    ਍䘀漀爀 愀 渀愀洀攀搀 椀渀猀琀愀渀挀攀Ⰰ 琀栀攀 猀焀氀挀洀搀 挀漀渀渀攀挀琀 挀漀洀洀愀渀搀 洀甀猀琀 猀瀀攀挀椀昀礀 琀栀攀 ⴀ匀䌀漀洀瀀甀琀攀爀一愀洀攀尀䤀渀猀琀愀渀挀攀一愀洀攀 漀瀀琀椀漀渀⸀㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ Please also remember that no other service or program may have a connection to your instance.
    ਍匀漀Ⰰ 戀攀昀漀爀攀 礀漀甀 甀猀攀 琀栀攀 甀瀀瀀攀爀 挀漀洀洀愀渀搀猀Ⰰ 戀攀 猀甀爀攀 琀栀愀琀 愀氀氀 漀琀栀攀爀 猀攀爀瘀椀挀攀猀 愀渀搀 瀀爀漀最爀愀洀猀 琀栀愀琀 洀愀礀 挀漀渀渀攀挀琀 琀漀 匀儀䰀 猀攀爀瘀攀爀Ⰰ 愀爀攀 搀漀眀渀⸀㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ 2.3 A few notes on TEMPDB:
    ਍㰀戀爀㸀ഀഀ Usually, if the files of the TEMPDB database were lost, at a restart of the SQL Server service,
    ਍琀栀攀礀 猀栀漀甀氀搀 戀攀 爀攀挀爀攀愀琀攀搀⸀㰀戀爀㸀ഀഀ So, usually, there should be no problem.
    ਍㰀戀爀㸀ഀഀ You should also know that you do not need to backup the TEMPDB database, and you even can't:
    ਍ഀഀ ਍㰀戀爀㸀ഀഀ backup database tempdb to disk='c:\tempdb\tempdb.bak' with init
    ਍㰀戀爀㸀ഀഀ Msg 3147, Level 16, State 3, Line 1
    ਍䈀愀挀欀甀瀀 愀渀搀 爀攀猀琀漀爀攀 漀瀀攀爀愀琀椀漀渀猀 愀爀攀 渀漀琀 愀氀氀漀眀攀搀 漀渀 搀愀琀愀戀愀猀攀 琀攀洀瀀搀戀⸀㰀戀爀㸀ഀഀ Msg 3013, Level 16, State 1, Line 1
    ਍䈀䄀䌀䬀唀倀 䐀䄀吀䄀䈀䄀匀䔀 椀猀 琀攀爀洀椀渀愀琀椀渀最 愀戀渀漀爀洀愀氀氀礀⸀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
    ਍䠀漀眀攀瘀攀爀Ⰰ 猀漀洀攀琀椀洀攀猀 瀀爀漀戀氀攀洀猀 搀漀 漀挀挀甀爀Ⰰ 愀渀搀 礀漀甀 眀愀渀琀 琀栀攀 匀攀爀瘀椀挀攀 琀漀 挀爀攀愀琀攀 琀栀攀 吀䔀䴀倀䐀䈀 昀椀氀攀猀 愀琀 愀渀漀琀栀攀爀 氀漀挀愀琀椀漀渀⸀㰀戀爀㸀ഀഀ
    ਍䤀昀 礀漀甀 樀甀猀琀 眀愀渀琀 琀漀 洀漀瘀攀 琀栀攀 吀䔀䴀倀䐀䈀 昀椀氀攀猀 琀漀 愀渀漀琀栀攀爀 氀漀挀愀琀椀漀渀Ⰰ 昀漀爀 攀砀愀洀瀀氀攀Ⰰ 昀漀爀 瀀攀爀昀漀爀洀愀渀挀攀 爀攀愀猀漀渀猀Ⰰ㰀戀爀㸀ഀഀ while there are no problems, then use statements similar to:
    ਍㰀戀爀㸀ഀഀ alter database tempdb
    ਍䴀伀䐀䤀䘀夀 䘀䤀䰀䔀 ⠀一䄀䴀䔀 㴀 ✀琀攀洀瀀搀攀瘀✀Ⰰ 䘀䤀䰀䔀一䄀䴀䔀 㴀 ✀䠀㨀尀琀攀洀瀀搀戀尀琀攀洀瀀搀攀瘀⸀氀搀昀✀⤀㰀戀爀㸀ഀഀ GO
    ਍㰀戀爀㸀ഀഀ alter database tempdb
    ਍䴀伀䐀䤀䘀夀 䘀䤀䰀䔀 ⠀一䄀䴀䔀 㴀 ✀琀攀洀瀀氀漀最✀Ⰰ 䘀䤀䰀䔀一䄀䴀䔀 㴀 ✀䠀㨀尀琀攀洀瀀搀戀尀琀攀洀瀀氀漀最⸀氀搀昀✀⤀㰀戀爀㸀ഀഀ GO
    ਍㰀戀爀㸀ഀഀ (just repeat that for all the files which make up the tempdb database.)
    ਍㰀戀爀㸀ഀഀ Ofcourse, in the above statements, the H: drive is just an example.
    ਍㰀戀爀㸀ഀഀ Now, if there seems to be a problem, and the service won't start because somehow it cannot create the TEMPDB database,
    ਍琀栀攀渀 挀栀攀挀欀 琀栀椀猀 昀椀爀猀琀㨀㰀戀爀㸀ഀഀ
    ਍ⴀ 䄀爀攀 琀栀攀 瀀攀爀洀椀猀猀椀漀渀猀 漀渀 琀栀攀 昀椀氀攀猀礀猀琀攀洀⼀瀀愀琀栀 挀栀愀渀最攀搀㼀㰀戀爀㸀ഀഀ - Is the sqlservice account changed, and lacking permissions?
    ਍ⴀ 䤀猀 琀栀攀爀攀 猀甀昀昀椀挀椀攀渀琀 猀瀀愀挀攀 昀漀爀 吀䔀䴀倀䐀䈀 漀渀 琀栀攀 搀攀昀愀甀氀琀 氀漀挀愀琀椀漀渀㼀㰀戀爀㸀ഀഀ
    ਍䤀昀 愀氀氀 漀昀 琀栀攀 愀戀漀瘀攀 猀攀攀洀猀 伀䬀Ⰰ 琀栀攀渀 礀漀甀 洀椀最栀琀 琀爀礀 琀栀椀猀㨀㰀戀爀㸀ഀഀ
    ਍ⴀ 匀琀愀爀琀 匀儀䰀 匀攀爀瘀攀爀 椀猀 猀椀渀最氀攀 甀猀攀爀 洀漀搀攀 ⠀愀猀 猀栀漀眀渀 椀渀 猀攀挀琀椀漀渀 ㈀⸀㈀⤀㰀戀爀㸀ഀഀ - Make sure no other services can connect to SQL Server (like the Agent etc..)
    ਍ⴀ 匀琀愀爀琀 琀栀攀 挀漀洀洀愀渀搀 甀琀椀氀椀琀礀 ∀猀焀氀挀洀搀∀㰀戀爀㸀ഀഀ - Use the above "alter database tempdb" statements, and let the files point to a location of which you are sure
    ਍琀栀攀爀攀 挀愀渀渀漀琀 戀攀 愀 瀀爀漀戀氀攀洀⸀㰀戀爀㸀ഀഀ - Stop and start the service again, in normal mode.
    ਍ഀഀ ਍㰀戀爀㸀ഀഀ
    ਍㰀戀爀㸀ഀഀ ਍㰀栀㌀ 椀搀㴀∀猀攀挀琀椀漀渀㌀∀㸀㌀⸀ 圀䠀䄀吀 䤀䘀 吀䠀䔀 吀刀䄀一匀䄀䌀吀䤀伀一 䰀伀䜀 䤀匀 䘀唀䰀䰀㨀㰀⼀栀㌀㸀㰀戀爀㸀ഀഀ ਍ഀഀ ਍䤀昀 礀漀甀爀 搀愀琀愀戀愀猀攀 甀猀攀猀 琀栀攀 ∀匀椀洀瀀氀攀∀ 爀攀挀漀瘀攀爀礀 洀漀搀攀氀Ⰰ 礀漀甀 眀漀渀✀琀 爀甀渀 椀渀琀漀 琀栀椀猀 瀀爀漀戀氀攀洀 猀漀 昀愀猀琀⸀㰀戀爀㸀ഀഀ But if the "Full" recovery model is used, in some cases, when for example large batch loads are used,
    ਍礀漀甀 洀椀最栀琀 攀渀搀 甀瀀 椀渀 愀 猀椀琀甀愀琀椀漀渀 眀栀攀爀攀 琀栀攀 吀爀愀渀猀愀挀琀椀漀渀 氀漀最 椀猀 挀漀洀瀀氀攀琀攀氀礀 昀甀氀氀⸀㰀戀爀㸀ഀഀ
    ਍䤀渀 愀 瀀爀漀搀甀挀琀椀漀渀 猀椀琀甀愀琀椀漀渀Ⰰ 琀栀椀猀 爀攀愀氀氀礀 挀漀甀氀搀 戀攀 愀 洀椀猀攀爀愀戀氀攀 猀椀琀甀愀琀椀漀渀⸀㰀戀爀㸀ഀഀ Suppose you see no way to expand the log, and/or you do not have extra diskspace, and the load job is
    ਍愀氀爀攀愀搀礀 戀爀漀欀攀渀⸀㰀戀爀㸀ഀഀ
    ਍吀栀攀 昀漀氀氀漀眀椀渀最 栀椀渀琀猀 愀爀攀 愀挀琀甀愀氀氀礀 㰀䈀㸀戀愀搀 愀搀瘀椀挀攀㰀⼀䈀㸀Ⰰ 猀椀渀挀攀 礀漀甀 瀀爀漀戀愀戀氀礀 栀愀瘀攀 愀 戀愀挀欀甀瀀 瀀漀氀椀挀礀 椀渀 瀀氀愀挀攀Ⰰ 甀猀椀渀最㰀戀爀㸀ഀഀ a Full backup in combination with one ore more (usually more) Transaction Log backups.
    ਍䤀昀 礀漀甀 甀猀攀 琀栀攀 昀漀氀氀漀眀椀渀最 挀漀洀洀愀渀搀猀Ⰰ 礀漀甀 ∀戀爀攀愀欀∀ 琀栀愀琀 挀栀愀椀渀Ⰰ 愀渀搀 愀昀琀攀爀眀愀爀搀猀 礀漀甀 洀甀猀琀 挀爀攀愀琀攀 愀 渀攀眀 䘀甀氀氀 戀愀挀欀甀瀀 愀最愀椀渀Ⰰ㰀戀爀㸀ഀഀ and create Transaction log backups afterward, using your normal policy. In effect: you need to start a new backup cycle.
    ਍㰀戀爀㸀ഀഀ In any case, if there are no other alternatives, and you are stuck with a full log, then:
    ਍㰀戀爀㸀ഀഀ
    ਍㰀䈀㸀匀儀䰀 ㈀　　㠀㨀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀甀攀∀㸀ഀഀ ਍䈀䄀䌀䬀唀倀 䰀伀䜀 夀伀唀刀䐀䄀吀䄀䈀䄀匀䔀一䄀䴀䔀 吀伀 䐀䤀匀䬀㴀✀一唀䰀㨀✀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍䠀攀爀攀 礀漀甀 甀猀攀 愀 昀愀欀攀 戀愀挀欀甀瀀 搀椀猀欀 搀攀瘀椀挀攀Ⰰ 猀漀 琀漀 猀瀀攀愀欀Ⰰ 戀甀琀 礀漀甀爀 氀漀最 眀椀氀氀 戀攀 挀氀攀愀爀攀搀⸀㰀戀爀㸀ഀഀ But note that the actual log file(s) still have the same size: they will not be shrinked.
    ਍䈀甀琀Ⰰ 琀栀攀礀 愀爀攀 ⠀渀攀愀爀⤀ 攀洀瀀琀礀 愀最愀椀渀Ⰰ 猀漀 礀漀甀 挀愀渀 瀀爀漀挀攀攀搀 甀猀椀渀最 琀栀攀 搀愀琀愀戀愀猀攀 愀最愀椀渀⸀㰀戀爀㸀ഀഀ
    ਍㰀戀爀㸀ഀഀ Older Versions:
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ BACKUP LOG YOURDATABASENAME WITH TRUNCATE_ONLY
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ ਍㰀戀爀㸀ഀഀ
    ਍㰀戀爀㸀ഀഀ
    ਍㰀戀爀㸀ഀഀ
    ਍㰀⼀戀漀搀礀㸀ഀഀ