Creating a compressed SQL Server Database backup
March 19th, 2009
1 comment
In this post I will describe how to create a compressed backup of any SQL Server database in Windows. This is especially useful if you have Express or MSDE edition of SQL Server where there’s no SQL Agent. Let’s look a this example:
- First we will create a folder, where we want to keep or database backups. Let’s call it C:\DbBackup.
- Now, we’ll create a sql script that will backup our database. I will use Northwind database in this example. The created backup file will have the name Northwind_[current date]. bak Open a Notepad and paste the following T-SQL code:
- Save file as NorthwindBackup.sql
-- Create backup of NorthWind Database -- add current date to the file name and -- save it inside C:\DbBackup folder -- DECLARE @str NVARCHAR(255) SET @str=N'C:\DbBackup\Northwind_'+CONVERT(VARCHAR, GETDATE(), 105)+'.bak' BACKUP DATABASE Northwind TO DISK=@str
