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.
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.
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 = 'BACKUP LOCATION' RESTORE FILELISTONLY FROM DISK ='D:\BACKUP\DBA_TEST.BAK'
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 <database name> from disk =’backup location’
Move ‘logical name of data file’ to ’data file location’,
Move ‘logical name of log file’ to ’log file 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'
RESTORE DATABASE DBA_TEST FROM DISK ='D:\BACKUP\DBA_TEST.BAK' WITH REPLACE
SELECT * FROM SYS.MASTER_FILES ALTER DATABASE DBA_NEW MODIFY FILE (NAME = DBA,NEWNAME = DBA_NEW ) ALTER DATABASE DBA_NEW MODIFY FILE (NAME = DBA_LOG,NEWNAME = DBA_NEW_LOG )
The database restoration has done in different machine then, transfers the logins and the passwords by using this Microsoft script.
—-Backup & restore / refresh – note the source & target
Check the server version is compatible lower to higher
Step: 1 Find the Source & Destination
Source: ABCD\sql2k5m_2 Database: RiskControl4P
Destination: XYZ Database: RiskControl2E
Step: 2 Get confirmation – In this Target already Database (RiskControl2E) is there. Shall we overwrite? make sure the backups are available in the location.
Check that Server Name(ABCD\sql2k5m_2) & Location: H:\Backup_ITM0506817\RiskControl4P_full.bak
To Overwrite check the database Name: select * from sys.databases where name =’name’
Step :3 Shall we take backup before overwrite? If yes follow the below.
Find the Location for the Destination Server by using below query.
Server Name: XYZ
—- 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
ORDER BY b.backup_finish_date DESC
Get the previous backups from backup history,
Check with the users, if we are ok with the existing backup date&time by using Backup History Query. Take a latest backup if needed.
Check with Source Server : ABCD\sql2k5m_2 , go ahead. If needed run a latest backup of full or differential to the history location.
Move backup to target/destination location
Step :1 Directly Copy the Backup file from Source to destination (or) if any space issue
Step :2 Go the File – Right Click -Properties- sharing or follow the below
Step :3 \\server name\drive$\ or \\share name
/* 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
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
alter database [RiskControl2E] set single_user with rollback immediate
restore filelistonly from disk=’\\ABCD\Backup_RITM0506817\RiskControl4P_full.bak’
restore database [RiskControl2E] from disk=’\\ABCD\Backup_RITM0506817\RiskControl4P_full.bak’
alter database [RiskControl2E] set multi_user with rollback immediate
Apply the create logins, if needed
Fix the orphan users, if anything.