ÿþ<html> <head> <title>Antapex.org: Overview of some often used SQL Server TSQL code</title> </head> <body bgcolor="#FFFFFF" link="blue" alink="blue" vlink="blue"> <h1>Quick Backup & Restore SQL Server</h1> <B>Version</B> : 1.0<br> <B>Date</B> : 20/07/2011<br> <B>By</B> : Albert van der Sel<br> <hr/> <br> <font face="arial" size=2 color="blue"> <B>Main Contents:</B><br> <br> <B> <A href="#section1"> 1. BACKUP & RESTORE OF REGULAR USER DATABASES</A><br> <A href="#section2"> 2. BACKUP & RESTORE OF SYSTEM DATABASES</A><br> <A href="#section3"> 3. WHAT IF THE TRANSACTION LOG IS FULL</A><br> </B> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section1">1. BACKUP & RESTORE OF REGULAR USER DATABASES:</h3><br> <font face="courier" size=2 color="black"> At least, the following backup policies are possible with regards to regular user databases:<br> <br> <ol> <li> Full backups only, which are always consistent. Such a backup can be restored to get the database back<br> to the situation at the time the backup was created.<br></li> <br> <li> Full backup in combination with later Transactionlog backups. The Transaction log backups, contain<br> the changes with respect to the former backup (whether that's a full- or transactionlog backup).<br> In case of a restore action, restore the Full first, and then restore all subsequent transaction log backups,<br> from the first transaction log backup, all the way up to the latest backup.<br> Note: The database needs to use the "Full recovery model" (which is somewhat comparable to "archive mode" in Oracle).<br></li> <br> <li>Full backup in combination with later Differential backups. The Differential backups, contain<br> the changes with respect to the last FULL backup.<br> Later differential backups will thus grow in size. In case of a restore action, restore the Full backup first,<br> and then ONLY the most recent Differential backup.<br></li> </ol> <B><U>1.1 Creating a FULL database backup and restore it:</U></B><br> <br> - Example Full backup:<br> <br> <font face="courier" size=2 color="blue"> BACKUP DATABASE TEST TO DISK='d:\backups\test_full.dmp' WITH INIT<br> <font face="courier" size=2 color="black"> <br> - Example restore of a full backup (and not restoring other backups):<br> <br> <font face="courier" size=2 color="blue"> RESTORE DATABASE TEST FROM DISK='d:\backups\test_full.dmp' WITH REPLACE, RECOVERY<br> <font face="courier" size=2 color="black"> <br> The "RECOVERY" clause means that this is your only, or last, backup to restore, and afterwards the database needs<br> to be recovered and opened.<br> <br> <br> <B><U>1.2 Creating a FULL database backup and subsequential DIFFERENTIAL backups:</U></B><br> <br> - Example backups: first the full at e.g. 01:00h am, then a number of diffs during the day.<br> <br> <font face="courier" size=2 color="blue"> BACKUP DATABASE TEST TO DISK='d:\backups\test_full.dmp' WITH INIT -- at 01:00h<br> <br> BACKUP DATABASE TEST TO DISK='d:\backups\test_diff_0500.dmp' WITH DIFFERENTIAL, INIT -- at 05:00h<br> <br> BACKUP DATABASE TEST TO DISK='d:\backups\test_diff_0900.dmp' WITH DIFFERENTIAL, INIT -- at 09:00h<br> <br> BACKUP DATABASE TEST TO DISK='d:\backups\test_diff_1100.dmp' WITH DIFFERENTIAL, INIT -- at 11:00h<br> <br> BACKUP DATABASE TEST TO DISK='d:\backups\test_diff_1300.dmp' WITH DIFFERENTIAL, INIT -- at 13:00h<br> <br> BACKUP DATABASE TEST TO DISK='d:\backups\test_diff_1700.dmp' WITH DIFFERENTIAL, INIT -- at 17:00h<br> <br> <font face="courier" size=2 color="black"> Important: A differential backup contains all the delta's with respect to the last full backup.<br> So, differential backups taken at a later time, are expected to be larger compared to earlier diff backups.<br> <br> <br> <B><U>1.3 Restoring a FULL database backup followed by a restore of (only) the latest DIFFERENTIAL backup:</U></B><br> <br> First you restore the full with the "WITH NORECOVERY" clause, then ONLY restore the LATEST differential backup<br> using the "WITH RECOVERY" clause.<br> <br> <font face="courier" size=2 color="blue"> RESTORE DATABASE TEST FROM DISK='d:\backups\test_full.dmp' WITH REPLACE, NORECOVERY -- first restore the full backup<br> <br> RESTORE DATABASE TEST FROM DISK='d:\backups\test_diff_1700.dmp' WITH RECOVERY -- then restore only the latest diff backup<br> <br> <font face="courier" size=2 color="black"> The above example assumes a crash happened after 17:00h.<br> In that case, use only the full from 01:00h and the diff backup from 17.00h.<br> <br> Suppose a crash happened at 11.45h.<br> In that case, use only the full from 01:00h and the diff backup from 11.00h.<br> <br> Note: this backup/restore policy is independent from your database "recovery model", like "full" or "simple" or "bulk logged".<br> So, you can ALWAYS use this backup/restore policy.<br> <br> <br> <B><U>1.4 Creating a FULL database backup and subsequential TRANSACTION LOG backups:</U></B><br> <br> IMPORTANT: the following backup policy only works if your database uses the Full "recovery model"<br> <br> - Example backups: first the full at e.g. 01:00h am, then a number of TRANSACTION LOG backups during the day.<br> <br> <font face="courier" size=2 color="blue"> BACKUP DATABASE TEST TO DISK='d:\backups\test_full.dmp' WITH INIT -- at 01:00h<br> <br> BACKUP LOG TEST TO DISK='d:\backups\test_log_0500.dmp' WITH INIT -- at 05:00h<br> <br> BACKUP LOG TEST TO DISK='d:\backups\test_log_0900.dmp' WITH INIT -- at 09:00h<br> <br> BACKUP LOG TEST TO DISK='d:\backups\test_log_1100.dmp' WITH INIT -- at 11:00h<br> <br> BACKUP LOG TEST TO DISK='d:\backups\test_log_1300.dmp' WITH INIT -- at 13:00h<br> <br> BACKUP LOG TEST TO DISK='d:\backups\test_log_1700.dmp' WITH INIT -- at 17:00h<br> <br> <font face="courier" size=2 color="black"> (Note: database TEST needs to use the Full recovery model)<br> <br> A Transaction log backup, contains the "delta's" compared to the last former backup, whether that was<br> a Full backup, or a transaction log backup.<br> So, at a restore action, you need to apply all your transaction log backups that are available.<br> <br> <br> <B><U>1.5 Restoring a FULL database backup followed by a restore of ALL subseqential Transaction Log backups:</U></B><br> <br> Suppose that, using the model schetched in section 1.4, a crash occurred at 11.45h.<br> What backups would you use to restore your database?<br> Since a transaction log backup, contains only the changes relative to the direct former backup, this time you<br> need to restore the full backup, followed by ALL applicable transaction log backups.<br> This is different from the differential policy, where you only needed the full- and the last differential backup.<br> So in this case we procede as follows:<br> <br> <font face="courier" size=2 color="blue"> RESTORE DATABASE TEST FROM DISK='d:\backups\test_full.dmp' WITH REPLACE, NORECOVERY<br> <br> RESTORE LOG TEST FROM DISK='d:\backups\test_log_0500.dmp' WITH NORECOVERY<br> <br> RESTORE LOG TEST FROM DISK='d:\backups\test_log_0900.dmp' WITH NORECOVERY<br> <br> RESTORE LOG TEST FROM DISK='d:\backups\test_log_1100.dmp' WITH RECOVERY<br> <br> <font face="courier" size=2 color="black"> Note: only the last restore action needs the "WITH RECOVERY" clause.<br> <br> Note: If the database was not fully destroyed, and it was possible to backup the transaction log<br> at 11.45h (just after the crash), it would in principle be possible to backup the "tail" of the transaction log,<br> thereby saving all delta's between 11.00h and 11.45h.<br> But in most cases, it's a bit hypothetical.<br> <br> <br> <B><U>1.6 Restoring to a different location:</U></B><br> <br> let's show you how to restore a database to different location. That is, the filesystems<br> and path to where the database must be restored to, is different from the original filesystems and/or paths.<br> <br> Please see the following example. In this RESTORE statement, the MOVE option tells SQL Server<br> the <B>new location</B> to where the files (which information is stored in the backup) need to be placed at the restore.<br> <br> <font face="courier" size=2 color="blue"> RESTORE DATABASE TEST FROM DISK='L:\temp\test_full.bak' WITH RECOVERY,<br> MOVE 'test_data_1' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_data_1.mdf', <br> MOVE 'test_index_1' TO 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_index_1.ndf',<br> MOVE 'test_data_2' TO 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_data_2.ndf',<br> MOVE 'test_Log' TO 'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_Log.ldf', REPLACE<br> <font face="courier" size=2 color="black"> <br> One problem often seen here, is that you do not know the logical names, like for example the name 'test_data_1'.<br> For an existing database, the logical and physical filenames are easy to find from the sysfiles view, like<br> using the query "select * from sysfiles".<br> <br> Also, to retrieve logical and physical names from the backupfile itself, you can use the RESTORE FILELISTONLY command.<br> <br> So, suppose you have a backupfile like d:\backups\test_full.bak, then you can retrieve the names using: <br> <br> <font face="courier" size=2 color="blue"> RESTORE FILELISTONLY from disk='d:\backups\test_full.bak'<br> <font face="courier" size=2 color="black"> <br> <br> <B><U>1.7 Backup information recorded in the MSDB Database:</U></B><br> <br> The MSDB database also contain historical information about any backups that were made.<br> Especially the system tables "backupset" and "backupmediafamily" carry interesting information,<br> as the below queries will make clear:<br> <br> <font face="courier" size=2 color="blue"> -- adjust the date in the below queries as you see fit.<br> <br> SELECT substring(s.database_name,1,20) as "database", (s.backup_size/1024/1024) as "Size_in_MB", s.type,<br> s.backup_start_date, s.backup_finish_date, substring(f.physical_device_name,1,30)<br> FROM backupset s, backupmediafamily f<br> WHERE s.media_set_id=f.media_set_id<br> AND s.backup_start_date > '2011-05-05'<br> ORDER BY s.backup_start_date<br> <br> SELECT backup_start_date, backup_finish_date, media_set_id,<br> type, substring(database_name,1,20)<br> FROM backupset<br> <br> SELECT backup_start_date, backup_finish_date, media_set_id,<br> type, database_name<br> FROM backupset<br> WHERE backup_start_date>'2011-05-05'<br> <font face="courier" size=2 color="black"> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section2">2. BACKUP & RESTORE OF SYSTEM DATABASES:</h3><br> <font face="courier" size=2 color="black"> A SQL Server instance, has 4 socalled "system databases". These are:<br> <br> - master database: contains metadata about the instance, databases, logins, and much more.<br> - msdb database: contains jobdescriptions, history information on jobs, backups, replication etc..<br> - model database: can function as a template for new databases, but not much dba's use it.<br> - tempdb database: functions as a temporary workspace for sort operations, temp tables, index rebuilds etc..<br> <br> <B><U>2.1 Backup of the system databases:</U></B><br> <br> You do not need to backup the tempdb database. For example, if the tempdb database files gets lost for some reason,<br> SQL Server will simply create the tempdb database again at startup. But if that happens,<br> you still need to check the sizes and the number of files, in order to check whether<br> tempdb is still according to your specifications.<br> <br> The other system databases (master, model, msdb) are critical for proper operation.<br> These databases usually will be (and remain) quite small, in most cases less than 30 MB or so. So, creating backups<br> should be a matter of seconds.<br> <br> You should only make full backups of these databases. As said above, the databases are very small and the backups<br> will not occupy much diskspace (if you would backup to disk, which is recommended).<br> <br> Suppose that you would backup these databases to the "F:\sqlbackups" disk location. An example of backupcommands then<br> could be as simple as this:<br> <br> <font face="courier" size=2 color="blue"> BACKUP DATABASE master TO DISK='F:\sqlbackups\master.dmp' WITH INIT<br> <br> BACKUP DATABASE msdb TO DISK='F:\sqlbackups\msdb.dmp' WITH INIT<br> <br> BACKUP DATABASE model TO DISK='F:\sqlbackups\model.dmp' WITH INIT<br> <br> <font face="courier" size=2 color="black"> <br> <B><U>2.2 Restore of the system databases:</U></B><br> <br> When you want to restore a regular "user" database (like for example the database "sales"), the SQL Server instance<br> can be running in the usual "multi-user" mode, so no special preperation is neccessary with respect to the state of the instance.<br> <br> It's different when you need to restore a system database. In this case, if you have lost a system database, your instance<br> will not start anyway. But you can start it in "single user mode", which also allows you to restore a system database.<br> To start the instance in single user mode, you need to use the "/m" parameter from the command line.<br> Here is an example of a restore of the master database:<br> <br> <font face="courier" size=2 color="blue"> NET START "MSSQLSERVER" /m<br> <br> Next, connect with SQLCMD or the management studio, and use the following TSQL command:<br> <br> RESTORE DATABASE MASTER FROM DISK='F:\sqlbackups\master.dmp' WITH REPLACE, RECOVERY<br> GO<br> <font face="courier" size=2 color="black"> <br> For a named instance, the sqlcmd connect command must specify the -SComputerName\InstanceName option.<br> <br> Please also remember that no other service or program may have a connection to your instance.<br> So, before you use the upper commands, be sure that all other services and programs that may connect to SQL server, are down.<br> <br> <B><U>2.3 A few notes on TEMPDB:</U></B><br> <br> Usually, if the files of the TEMPDB database were lost, at a restart of the SQL Server service,<br> they should be recreated.<br> So, usually, there should be no problem.<br> <br> You should also know that you do not need to backup the TEMPDB database, and you even can't:<br> <font face="courier" size=2 color="brown"> <br> backup database tempdb to disk='c:\tempdb\tempdb.bak' with init<br> <br> Msg 3147, Level 16, State 3, Line 1<br> Backup and restore operations are not allowed on database tempdb.<br> Msg 3013, Level 16, State 1, Line 1<br> BACKUP DATABASE is terminating abnormally.<br> <br> <font face="courier" size=2 color="black"> <br> However, sometimes problems do occur, and you want the Service to create the TEMPDB files at another location.<br> <br> If you just want to move the TEMPDB files to another location, for example, for performance reasons,<br> while there are no problems, then use statements similar to:<br> <br> alter database tempdb<br> MODIFY FILE (NAME = 'tempdev', FILENAME = 'H:\tempdb\tempdev.ldf')<br> GO<br> <br> alter database tempdb<br> MODIFY FILE (NAME = 'templog', FILENAME = 'H:\tempdb\templog.ldf')<br> GO<br> <br> (just repeat that for all the files which make up the tempdb database.)<br> <br> Ofcourse, in the above statements, the H: drive is just an example.<br> <br> Now, if there seems to be a problem, and the service won't start because somehow it cannot create the TEMPDB database,<br> then check this first:<br> <br> - Are the permissions on the filesystem/path changed?<br> - Is the sqlservice account changed, and lacking permissions?<br> - Is there sufficient space for TEMPDB on the default location?<br> <br> If all of the above seems OK, then you might try this:<br> <br> - Start SQL Server is single user mode (as shown in section 2.2)<br> - Make sure no other services can connect to SQL Server (like the Agent etc..)<br> - Start the command utility "sqlcmd"<br> - Use the above "alter database tempdb" statements, and let the files point to a location of which you are sure<br> there cannot be a problem.<br> - Stop and start the service again, in normal mode.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section3">3. WHAT IF THE TRANSACTION LOG IS FULL:</h3><br> <font face="courier" size=2 color="black"> If your database uses the "Simple" recovery model, you won't run into this problem so fast.<br> But if the "Full" recovery model is used, in some cases, when for example large batch loads are used,<br> you might end up in a situation where the Transaction log is completely full.<br> <br> In a production situation, this really could be a miserable situation.<br> Suppose you see no way to expand the log, and/or you do not have extra diskspace, and the load job is<br> already broken.<br> <br> The following hints are actually <B>bad advice</B>, since you probably have a backup policy in place, using<br> a Full backup in combination with one ore more (usually more) Transaction Log backups.<br> If you use the following commands, you "break" that chain, and afterwards you must create a new Full backup again,<br> and create Transaction log backups afterward, using your normal policy. In effect: you need to start a new backup cycle.<br> <br> In any case, <B>if there are no other alternatives</B>, and you are stuck with a full log, then:<br> <br> <br> <B>SQL 2008:</B><br> <br> <font face="courier" size=2 color="blue"> BACKUP LOG YOURDATABASENAME TO DISK='NUL:'<br> <br> <font face="courier" size=2 color="black"> Here you use a fake backup disk device, so to speak, but your log will be cleared.<br> But note that the actual log file(s) still have the same size: they will not be shrinked.<br> But, they are (near) empty again, so you can proceed using the database again.<br> <br> <br> <B>Older Versions:</B><br> <br> <font face="courier" size=2 color="blue"> BACKUP LOG YOURDATABASENAME WITH TRUNCATE_ONLY<br> <br> <font face="courier" size=2 color="black"> <br> <br> <br> <br> <br> <br> </body> </html>