Restore database SQL server query

For a database using the full or bulk-logged recovery model, SQL Server requires in most cases that you back up the tail of the log before restoring the database. For more information, see .

B. Restoring full and differential database backups

The following example restores a full database backup followed by a differential backup from the Z:SQLServerBackupsAdventureWorks2012.bak backup device, which contains both backups. The full database backup to be restored is the sixth backup set on the device (FILE = 6), and the differential database backup is the ninth backup set on the device (FILE = 9). As soon as the differential backup is recovered, the database is recovered.

RESTORE DATABASE AdventureWorks2012 FROM DISK = 'Z:SQLServerBackupsAdventureWorks2012.bak' WITH FILE = 6 NORECOVERY; RESTORE DATABASE AdventureWorks2012 FROM DISK = 'Z:SQLServerBackupsAdventureWorks2012.bak' WITH FILE = 9 RECOVERY;

C. Restoring a database using RESTART syntax

The following example uses the RESTART option to restart a RESTORE operation interrupted by a server power failure.

- This database RESTORE halted prematurely due to power failure. RESTORE DATABASE AdventureWorks2012 FROM AdventureWorksBackups; - Here is the RESTORE RESTART operation. RESTORE DATABASE AdventureWorks2012 FROM AdventureWorksBackups WITH RESTART;

D. Restoring a database and move files

The following example restores a full database and transaction log and moves the restored database into the C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLData directory.

RESTORE DATABASE AdventureWorks2012 FROM AdventureWorksBackups WITH NORECOVERY, MOVE 'AdventureWorks2012_Data' TO 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDataNewAdvWorks.mdf', MOVE 'AdventureWorks2012_Log' TO 'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDataNewAdvWorks.ldf'; RESTORE LOG AdventureWorks2012 FROM AdventureWorksBackups WITH RECOVERY;

E. Copying a database using BACKUP and RESTORE

The following example uses both the BACKUP and RESTORE statements to make a copy of the AdventureWorks2012 database. The MOVE statement causes the data and log file to be restored to the specified locations. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored. The new copy of the database is named TestDB. For more information, see .

BACKUP DATABASE AdventureWorks2012 TO AdventureWorksBackups ; RESTORE FILELISTONLY FROM AdventureWorksBackups ; RESTORE DATABASE TestDB FROM AdventureWorksBackups WITH MOVE 'AdventureWorks2012_Data' TO 'C:MySQLServertestdb.mdf', MOVE 'AdventureWorks2012_Log' TO 'C:MySQLServertestdb.ldf'; GO

F. Restoring to a point-in-time using STOPAT

The following example restores a database to its state as of 12:00 AM on and shows a restore operation that involves multiple log backups. On the backup device, AdventureWorksBackups, the full database backup to be restored is the third backup set on the device (FILE = 3), the first log backup is the fourth backup set (FILE = 4), and the second log backup is the fifth backup set (FILE = 5).

RESTORE DATABASE AdventureWorks2012 FROM AdventureWorksBackups WITH FILE=3, NORECOVERY; RESTORE LOG AdventureWorks2012 FROM AdventureWorksBackups WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; RESTORE LOG AdventureWorks2012 FROM AdventureWorksBackups WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;

G. Restoring the transaction log to a mark

The following example restores the transaction log to the mark in the marked transaction named ListPriceUpdate.

• Database SQL server
• Create database SQL query
• Restore database in SQL server using query