IT Blog

  • Blog
  • Technology
    • Technology
    • Architecture
    • CMS
    • CRM
    • Web
    • DotNET
    • Python
    • Database
    • BI
    • Program Language
  • Users
    • Login
    • Register
    • Forgot Password?
  • ENEN
    • 中文中文
    • ENEN
Experience IT
In a World of Technology, People Make the Difference.
  1. Home
  2. Technology
  3. Database
  4. SQL Server Database Backup - 3 Methods

SQL Server Database Backup - 3 Methods

2016-11-01 1195 Views 0 Like 0 Comments

Database backup is not the DBA-only work. Most of developers and even small business owners also need to perform this task. This article will introduce 3 methods to back up database from SQL Server.

Table of Contents

  • Method 1 – from management studio
    • Connect to database server
    • Expend Databases folder
    • Right click on the database that you want to backup
    • from Context menu click Task
    • from sub menu click on Back Up…
    • on the popup window click on Add
    •  on Select Backup Destination window you could enter file location and file name directly or click on … button to specify a backup file.
    • Click on OK button to close the window, then click on OK again and wait for the completion.
  • Method 2 – using T-SQL script
  • Method 3 – using SSIS Service
    • Create a SSIS project from Visual Studio (SSDT installed). Note that the project template is under Business Intelligence folder, not SQL Server.
    • Create a package and open it, drag and drop “Back Up Database Task” onto the Control Flow work space.
    • Double click on the task, “Back Up Database Task” window will popup.
    • Click on "New…" button to specify the database connection.
    • On "Back Up Database task" window click on Database(s) drop down button, and select the database option you want to back up.
    • In Destination tab, specify Folder location and click OK.
    • Save the package.
    • You could execute the task right inside the studio. Right click on the task and select Execute Task from context menu.
    • Now you can run the package or debug the package directly in the Visual Studio, or you can deploy the package to SQL Server to run.

Method 1 – from management studio

Connect to database server

Expend Databases folder

Right click on the database that you want to backup

from Context menu click Task

from sub menu click on Back Up…

on the popup window click on Add

 on Select Backup Destination window you could enter file location and file name directly or click on … button to specify a backup file.

Click on OK button to close the window, then click on OK again and wait for the completion.

Method 2 – using T-SQL script

You could either to execute the script on Management Studio or store it as stored procedure to execute.

Here’s the script for backing up all user databases:

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

--specify database backup directory
SET @path = 'C:\DB\MSSQL11.MSSQLSERVER\MSSQL\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

-- exclude system db
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
	BACKUP DATABASE @name TO DISK = @fileName
	FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Method 3 – using SSIS Service

In order to use SSIS Service, you need SQL Server Development Tool (SSDT) or SQL Server Business Intelligence Development Studio (BIDS) for SQL 2008 and older to create a package. You can deploy the package to SQL Server and schedule a job to auto-run it.

Create a SSIS project from Visual Studio (SSDT installed). Note that the project template is under Business Intelligence folder, not SQL Server.

Create a package and open it, drag and drop “Back Up Database Task” onto the Control Flow work space.

Double click on the task, “Back Up Database Task” window will popup.

Click on "New…" button to specify the database connection.

On "Back Up Database task" window click on Database(s) drop down button, and select the database option you want to back up.

In Destination tab, specify Folder location and click OK.

Save the package.

You could execute the task right inside the studio. Right click on the task and select Execute Task from context menu.


You will see the green check mark when the execution completed.

Now you can run the package or debug the package directly in the Visual Studio, or you can deploy the package to SQL Server to run.

Note: Old blog post on google blogspot

.

Loading

error
fb-share-icon
Tweet
fb-share-icon
IT Team
Author: IT Team

Tags: Blog
Last updated:2021-04-11

IT Team

This person is lazy and left nothing

Like
Next >
Newest Hotspots Random
Newest Hotspots Random
Rich editor not working Making web page scroll down automatically Getting data from Dapper result All Unicode Chars How to keep and display contact form 7 data Common Regular Expressions
Jetpack conflict with Crelly Slider Tracking referral ID for registered user in WordPress Apostrophe 2 Theme, the image on page is not tidy up. OneDrive for Business 文件到 Google Drive 文件 Symbols: Stars Reporting notes
Categories
  • Architecture
  • BI
  • C#
  • CSS
  • Database
  • DotNET
  • Hosting
  • HTML
  • JavaScript
  • PHP
  • Program Language
  • Python
  • Security
  • SEO
  • Technology
  • Web
  • Wordpress

COPYRIGHT © 2021 Hostlike IT Blog. All rights reserved.

This site is supported by Hostlike.com