Breaking News: Grepper is joining You.com. Read the official announcement!

sql server restore

Innocent Iguana answered on March 4, 2021 Popularity 9/10 Helpfulness 3/10

answer sql server restore

related sql server restore database

sql server restore

Comment

# Physical Restore-----------------------------
# stop the server 
net stop MSSQLSERVER
# Delete the current database
del /Q C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabase.mdf
del /Q C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyDatabase_log.ldf
# Copy the backup files to the MSSQL data directory
copy C:\backups\sqlserver\MyDatabase.bak C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup
# Restore backup
sqlcmd -S localhost -U sa -P password -Q "RESTORE DATABASE MyDatabase FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\MyDatabase.bak' WITH REPLACE, RECOVERY"
# Start the server
net start MSSQLSERVER

# Logical Restore----------------------------------
# Create a new database to restore the backup:
sqlcmd -S localhost -U sa -P yourpassword -Q "CREATE DATABASE mydatabase_new"
# Restore the backup file to the new database:
sqlcmd -S localhost -U sa -P yourpassword -Q "RESTORE DATABASE mydatabase_new FROM DISK='C:\mydatabase_backup.bak'"
# Verify that the data has been restored
sqlcmd -S localhost -U sa -P yourpassword -d mydatabase_new -Q "SELECT * FROM mytable"

# Point in time Restore-----------------------------
# Create a new database to restore the backup
sqlcmd -S localhost -U sa -P password -Q "CREATE DATABASE mydatabase_new"
# Restore the backup file to the new database
sqlcmd -S localhost -U sa -P password -Q "RESTORE DATABASE mydatabase_new FROM DISK='C:\mydatabase_backup.bak' WITH NORECOVERY, MOVE 'mydatabase' TO 'C:\mydatabase_new.mdf', MOVE 'mydatabase_log' TO 'C:\mydatabase_new.ldf'"
# Bring the database online by applying the transaction log backup to the restored database:
sqlcmd -S localhost -U sa -P password -Q "RESTORE LOG mydatabase_new FROM DISK='C:\mydatabase_log_backup.trn' WITH NORECOVERY"

# Differential Restore--------------------------------
# restore the full backup
RESTORE DATABASE dbname FROM DISK='C:\backup\full_backup.bak' WITH NORECOVERY
# restore the differential backup
RESTORE DATABASE dbname FROM DISK='C:\backup\diff_backup.bak' WITH RECOVERY

# Incremental Restore-----------------------------------
# restore the full backup
RESTORE DATABASE dbname FROM DISK='C:\full_backup.bak' WITH NORECOVERY
# restore the incremental backup
RESTORE LOG dbname FROM DISK='C:\incremental_backup.trn' WITH NORECOVERY

Popularity 9/10 Helpfulness 3/10 Language sql

Source: Grepper

Tags: restore sql sql-server

Link to this answer
Share Copy Link

Contributed on Mar 07 2023

Innocent Iguana

0 Answers Avg Quality 2/10

Closely Related Answers

sql server restore database

Comment

--Step 1: Check the logical file names with the help of the following command:

  RESTORE FILELISTONLY 
  FROM DISK = 'E:\DBBackups\mydb.bak'

--Step 2: Use the logical names you get from the above query in the below query:

  RESTORE DATABASE [mydb_new]
  FILE = N'<MDFLogicalName>' 
  FROM DISK = N'E:\DBBackups\mydb.bak'
  WITH FILE = 1
	  ,NOUNLOAD
	  ,STATS = 10
	  ,MOVE N'<MDFLogicalname>' TO N'E:\DBBackups\mydb_new.mdf'
	  ,MOVE N'<LDFLogicalName>' TO N'E:\DBBackups\mydb_new_0.ldf'

Popularity 10/10 Helpfulness 5/10 Language sql

Source: Grepper

Tags: database databas

Link to this answer
Share Copy Link

Contributed on Mar 04 2021

Upset Unicorn

0 Answers Avg Quality 2/10

sql server restore

Contents

More Related Answers

sql server restore

Closely Related Answers

sql server restore database

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.