Backup/Restore

Step by step backup/restore using T-SQL

January 10, 2011
Advertisements

This article mainly for SQL server learners, who is going to become a DBA, in this article I am going to explain the backup and restore using T-SQL.

=======================make sure you got the following information, see the example in the bottom

DB size – Sp_helpdb ‘Dbname’ – 80 GB
Source DB latest backup date and availability – Check the backup history and check backup is available in drive – Yes
Destination DB latest backup date and availability – Optional (latest backup available )
Can we overwrite destination with or without backup – Check with the requester – Yes, overwrite take a backup before

=======================

 

Why I choose the T-SQL instead of GUI?

Below are reasons,

I have seen a number of articles and blogs shows the GUI.

Anyone can use the GUI to restore the database, if they don’t well in SQL.

Again most of the DBAs hasn’t used the command and they don’t know the T-SQL syntax to restore the database. I recommend using the T-SQL to restore the database because it’s the greatest tool when you are migrating a huge number of databases (Not to right click each database to restore) and time saver.

Backup:

I enjoyed when I read BrentOzar’s backup best practice articles. I have recommended my readers too.

Let’s see the funny story

You have some problem with your mobile phone. You lost all your contact numbers even your girlfriend (wife) number too. Suddenly, she called to you then you asked who are you? …

So, keep doing the backup regularly otherwise ready to update your resume – 🙂

Before going to restore section keep verify your backups regularly (sometimes your backup might be damaged)

Important: Backup is the free D/R solution, when you lost your database.

Step by step restore: Backup has been taken in D drive.

USE MASTER
GO
BACKUP DATABASE DBA_TEST TO DISK='D:\BACKUP\DBA_TEST.BAK' -- with copy_only

Filelistonly returns the logical file names, physical files of data, log locations & file sizes.

USE MASTER
GO
RESTORE FILELISTONLY FROM DISK = 'Source server backup location' -- This could be a share path or backup copied in the destination path
--You can use either direct share or copy to destination depends on size and N/W speed
RESTORE FILELISTONLY FROM DISK ='\\WIN-PC\Backup\DBA_TEST.BAK' -- Taken a direct share from source
RESTORE FILELISTONLY FROM DISK ='B:\BACKUP\DBA_TEST.BAK' -- Copied from source D:\ to destination to B:\

 



Create the same copy\content of the database DBA_new from DBA_test. Use XP_fixeddrives to find the drive free space.

EXEC XP_FIXEDDRIVES
SELECT * FROM SYS.MASTER_FILES


“sys.master_files” will show the current database files location. All the companies have certain rules like, data file will go one separate physical LUN (RAID level) and the log file will go another physical LUN (RAID level). It will help to improve the performance.

So, before going to restore checks and ask the same.

 

Restore database <Destination database name> from disk =’Source server backup location’
With
Move ‘logical name of data file’ to ’Destination data file location’, 
Move ‘logical name of log file’ to ’Destination log file location’

 

-- Run SYS.MASTER_FILES on destination to get MDF & LDF location

RESTORE DATABASE DBA_NEW FROM DISK ='D:\BACKUP\DBA_TEST.BAK'
WITH
MOVE 'DBA' TO 'D:\DATA\DBA.MDF',
MOVE 'DBA_LOG' TO 'E:\LOG\DBA_LOG.LDF'
You can also overwrite the database by using REPLACE option. No need to mention Move option.
RESTORE DATABASE DBA_NEW FROM DISK ='D:\BACKUP\DBA_TEST.BAK'
WITH replace, recovery

The replace option will overwrite an existing data in the same DB, if you want existing data, take a backup before run.

For more : Look the restoration options  WITH options (like overwrite(REPLACE),move etc).
Replace — Will overwrite the existing data
Norecovery — Will leave the database in restoring state and wait for further backup restore, will use this when we have sequence of backup full+differential and logs.
https://sqlserverblogforum.com/backuprestore/sql-server-database-backup-restore-steps/
Recovery — Will bring the database online, this used for the finial restore.
The database restoration has done in different machine then,  transfers the logins and the passwords  by using this Microsoft script.

 

Example:

—-Backup & restore / refresh – note the source & target

DB size – Sp_helpdb ‘Dbname’ – 80 GB 
Source DB latest backup date and availability – Check the backup history and check backup is available in drive – Yes
Destination DB latest backup date and availability – Optional (latest backup available )
Can we overwrite destination with or without backup – Check with the requester – Yes, overwrite take a backup before

 

Check the server version is compatible lower to higher

Step: 1 Find the Source & Destination/Target

Source: ABCD\sql2k5m_2 Database: RiskControl4P
Destination: XYZ Database: RiskControl2E

Step: 2 Check the source server latest backup is available – Source: ABCD\sql2k5m_2 Database: RiskControl4P, if not take a latest backup.

ABCD\sql2k5m_2 Database: RiskControl4P
H:\Backup_ITM0506817\RiskControl4P_full.bak

—- Backup history

SELECT top 100 b.server_name ,b.database_name,b.user_name, f.physical_device_name,
b.backup_finish_date,b.backup_size /1024/1024 AS size_MB,b.type,b.is_copy_only
,compressed_backup_size/1024/1024 AS C_size_MB
FROM MSDB.DBO.BACKUPMEDIAFAMILY F
JOIN MSDB.DBO.BACKUPSET B
ON (f.media_set_id=b.media_set_id)
--WHERE database_name='test'
--AND B.type='d'
ORDER BY b.backup_finish_date DESC

 

Step: 3 Check the Destination server latest backup is available – Destination: XYZ Database: RiskControl2E , if not take a latest backup, when the requester asked, need a backup before overwrite, otherwise leave it.

 

Step: 4 Move backup to target/destination location, you can use any of the following 3 options.

1 Directly Copy the Backup file from Source to destination (or) if any space issue

2 Go the File – Right Click -Properties- sharing or follow the below

3 \\server name\drive$\ or \\share name

Step: 5 – Start restore

-- To remove  existing users

alter database [RiskControl2E] set single_user with rollback immediate

RESTORE FILELISTONLY FROM DISK = 'Source server backup location' -- This could be a share path or copied in the destination path

restore filelistonly from disk='\\ABCD\Backup_RITM0506817\RiskControl4P_full.bak'

---- restore

Restore database <Destination database name> from disk =’Source server backup location’
With 
Move ‘logical name of data file’ to ’Destination data file location’, 
Move ‘logical name of log file’ to ’Destination log file location’

-- Run SYS.MASTER_FILES on destination to get MDF & LDF location

Restore database [RiskControl2E] from disk='\\ABCD\Backup_RITM0506817\RiskControl4P_full.bak'
with
move'RiskControl4R'to'M:\MSSQL2K5M_2\MSSQL\Data\RiskControl4P.mdf',
move'RiskControl4R_log'to'N:\MSSQL2K5M_2\MSSQL\Data\RiskControl4P_log.ldf',
recovery--,replace

-- For overwrite
--Restore database [RiskControl2E] from disk='\\ABCD\Backup_RITM0506817\RiskControl4P_full.bak' with replace

Alter database [RiskControl2E] set multi_user with rollback immediate

 

Step: 5 – optional, users permission backup

/* optional — take the destination user script for each DBs, when you r going to overwrite

Step :1 Take a script of logins & users both source & target and save in the folder

If its 2 or 3 Databases give Use Database_Name

Use Database_Name

And take the Script from folder – https://www.google.com/search?q=sql+server+user+script+permissions&rlz=1C1CHBD_enIN792IN792&oq=sql+server+user+script&aqs=chrome.1.69i57j0l5.6223j0j7&sourceid=chrome&ie=UTF-8

Step: 2 Take a script of logins for Source & target and save in the folder

And take the Script from folder – https://support.microsoft.com/en-in/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

DB level permission: https://www.sqlservercentral.com/scripts/script-db-level-permissions-v4-3

Make sure to validate manually all after login and user copy/transfer.

*/

 

Apply the create logins, if needed
Fix the orphan users, if anything. — sp_change_users_login @Action=’Report’

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

20 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

72 + = 74