Skip to main content

Backup and restore database in SQL Server

  1. To backup the database, use below query.
    Backup database <databaseName> to disk ='<backupLocation>.bak' with init;
  2. To backup the logs, use below query.
    Backup log <databaseName> to disk ='<backupLocation>.trn' with init;
  3. To restore from the database backup, use below query.
    restore database <databaseName> from disk =<backupLocation>.bak' with norecovery, replace, move 'testmirror' to 'DATA.mdf', move 'testmirror_log' to 'DATA_log.ldf';
  4. To restore from the transaction log, use below query.
    restore log     <databaseName> from disk ='<backupLocation>.trn' with recovery, replace, move 'testmirror' to 'DATA.mdf', move 'testmirror_log' to 'DATA_log.ldf'
Labels:

Comments

Post a Comment

Popular posts from this blog

Identify the duplicates in MySQL

To identify the duplicate entries in MySQL, use below query.                      SELECT COUNT(*) as repetitions, group_concat(id, ' (', startTime, ', ', endTime, ') ' SEPARATOR ' | ') as row_data FROM GROUP BY startTime, endTime HAVING repetitions > 1 FYI, id, startTime and endTime columns should be changed based on your table schema. you can change the output format here like alias and the separator.
Post a Comment
Read more

T-SQL to set Max memory in MSSQL

 To set max memory for Microsoft SQL server, use the below Transact SQL. USE master EXEC sp_configure ''show advanced options'', 1 RECONFIGURE WITH OVERRIDE GO --To set a maximum memory limit, type the following, pressing Enter after each line: USE master EXEC sp_configure ''max server memory (MB)'', <MaxServerMemory> RECONFIGURE WITH OVERRIDE GO --MaxServerMemory is the value of the physical memory in megabytes (MB) that you want to allocate. --To hide the maximum memory setting, type the following, pressing Enter after each line: USE master EXEC sp_configure ''show advanced options'', 0 RECONFIGURE WITH OVERRIDE GO exit
Post a Comment
Read more