Database Backup & Restore
Mahmudul Hasan Lead SQA Engineer, Enosis Solutions
Copyright @ Enosis Solutions
Covered Topic
Why we Need Backup Database Backup and Restore in SQL Server
Copyright @ Enosis Solutions
Why Backup?
Only way to reliably protect your SQL Server data. Recover Data from many failures
Media failure. User errors, for example, dropping a table by mistake. Hardware failures, for example, a damaged disk drive or permanent loss of a server. Natural disasters.
Replicate or Create similar environment
3 Copyright @ Enosis Solutions
Backup & Restore in SQL Server
Using sqlcmd Management Studio is not available Can be run using Batch file Using T-SQL Using query Using SQL Server Management Studio Visual/Wizard based
4 Copyright @ Enosis Solutions
Backup using command line
C:\>sqlcmd -U enosisqa -P Enosis123 -S
MAHMUDULPC\SQLEXPRESS -Q "BACKUP DATABASE SBPro_QA TO DISK='D:\SBPro.bak WITH FORMAT " What WITH FORMAT Does ?
It overwrite any existing backups and create a new
Copyright @ Enosis Solutions
Restore using command line
C:\>sqlcmd U enosisQA P Enosis123 S MAHMUDULPC\SQLEXPRESS -Q "RESTORE DATABASE SBPro_QA FROM DISK='D:\SBPro.bak
C:\>sqlcmd U enosisQA P Enosis123 S MAHMUDULPC\SQLEXPRESS -Q RESTORE DATABASE SBPro_QA FROM DISK='d:\SBPro.bak WITH MOVE SBPro_QA' TO 'D:\SBPro_QA.mdf', MOVE SBPro_QA_log' TO 'D:\SBPro_QA_log.ldf
* Using STATS
6 Copyright @ Enosis Solutions
Backup using T-SQL
BACKUP DATABASE SBPro_QA TO DISK='E:\Knowledge Session\TSQL\SBPro_TSQL.bak' WITH FORMAT GO
Copyright @ Enosis Solutions
Restore using T-SQL
RESTORE DATABASE SBPro_TSQL FROM DISK='E:\Knowledge Session\TSQL\SBPro_TSQL.bak' WITH MOVE 'SBPro_QA' TO 'E:\Knowledge Session\TSQL\SBPro_TSQL.mdf', MOVE 'SBPro_QA_log' TO 'E:\Knowledge Session\TSQL\SBPro_TSQL_log.ldf' GO
Note: Logical File Name should be same.
RESTORE FILELISTONLY FROM DISK= 'D:\SBPro.bak' to get the logical file name.
Copyright @ Enosis Solutions
Backup using Management Studio
1. Once connected to your database server, you will need to browse to your database in the left window pane of Management Studio. 2. Right Click on your database, and under "Tasks" choose "Back Up... 3. Ensure that the "Backup type" is set to "Full" to get a Full backup of your database 4. In the Destination section Remove the existing backup path and Add your desired destination (e.g. D:\SBPro_20120808.Bak 5. On the left-hand side of the window, click on 'Options', and then click on 'Overwrite all existing backup sets'. 6. Click on Ok to finish the task
Copyright @ Enosis Solutions
Restore using Management Studio
1. 2. 3. Connect to the Database server using Management Studio Right click on your database and go to Tasks > Restore > Database Select From device and click the small grey box on the right labeled ... . A window will pop up, Click Add and browse to the location of your .bak file. Click OK. Check the checkbox under the column Restore next to the database information in the bottom pane. Go to the Options (top-left). Check Overwrite the existing database. Click OK to begin the database restoration
4. 5. 6. 7.
10
Copyright @ Enosis Solutions
Other Backup & Restore Technique
File Copy Copy the .mdf .ndf and .ldf file and attach using Management studio. Using SQL Scripts Time consuming but good for small databases Tasks -> Generate Scripts http://sqlgen.codeplex.com/ http://opendbiff.codeplex.com/ http://www.sqlscriptgenerator.com/
11 Copyright @ Enosis Solutions
Challenges in SQL Server
Backup remote SQL server database It store the .bak file in the server where the DB Engine installed. Takes long time for big databases
12
Copyright @ Enosis Solutions
Solutions
Backup in the server then copy the bak file in local Backup in Map Drive / Nas drive To reduce the file transfer time compress the .bak file using 7zip or rar. Use compressed backup. (example: 66 GB of Data compressed to 7 GB, almost 90%)
13 Copyright @ Enosis Solutions
Thank You
14
Copyright @ Enosis Solutions
Questions & Answer
Q: Compatibility A: You can not restore a backup taken from an older version of SQL Server
15
Copyright @ Enosis Solutions
