Backing up databases is one of the tasks perform by database administrators in many organizations. The aim is to make a copy of the database with data either to restore to another server, keep it to restore in case of issues on the database, migrating application, for use on a desktop or for any other purpose which does not require directly touching the current database. In one of my posts I wrote on how to get the database size reduced in case its too large to restore to another server. I will focus on how to get a back up in sql server using SSMS and I will perform the demonstration using adventureworks database on my desktop but the same applies to any back up you may want to perform. Note that not everyone can perform a back up on a server and any back up performed is recorded in the msdb database so auditors can tell who perfomed backup of which database. To perform back up of a database, you must have db_owner/db_backupoperator database roles or a sysadmin role on the server will give you access to back up any database on that server .
Lets now go through the steps of backing up the database. First step is to fire up SSMS and connect to the server on which the database resides. It might help you to check the size of he database first using below query or just right click the database and go to properties and the size will show up there.
-- database size
SELECT database_name = DB_NAME(database_id)
, log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db
GROUP BY database_id
To start with the back up process, right click on the database you want to backup,go to tasks menu and click on back up. You should see the snapshot below.
There are lot of things to discuss about the back up window but l will keep it short. We are on the general page here. In the Source section, it tells you the database you are backing up, in my case adventureworks2016ctp3. Then it shows the backup type which you can change but we are doing a full back up. There is another option for differential backup which is simply to backup changes that have happened after you have performed a previous backup. With the full back up, you are getting the entire data set in the database. There is an option to choose copy-only backup which is backing up the database without affecting the sequence of the back ups recorded. You choose this option if you don’t want the back up you are doing to affect any upcoming back ups or previous ones so the database back up sequence will not change. The backup components section is where you chose to back up the database entirely or just some files and file groups. In our case, we choose to back up the entire database. The destination section gives option to choose which device you want to back up to and in our case we are backing up to a disk. There is the box where you add the location to backup the database to. By default, there are locations set up for backups, data files and log files during installation of sql server but you can choose to back up to any location on the server or to a shared location as long as you have access to it. In my case l am just backing up to the default location and using the default backup name adventureworks2016ctp3.bak. Note you can click the add button to browse to a new location and give a name for your back up file. You can use the remove button to remove any location already added in the box. Clicking on the content button will give you some details about the backup. Next page is Media Options which you can get to by clicking the Media Options at the top left. You should see the below in the Media Options page.
From above, we see the Overwrite Media section. Here we chose backup to the existing media set and overwrite all existing back up set. This means to overwrite backup set we are creating in case it already exists. The backup set information is stored in the msdb. There are other options to backup to a new media set and erase all existing backup sets but we are not interested in that at this time. The reliability section gives option to verify the back up after its performed to make sure the back up is not corrupt during the process. There is option to continue the back up even if an error occurs but by default the whole process will fail when an error occurs. We will not talk about the transaction log and tap drive section as its not necessary so lets go to the next page which is backup options. You should see the below in the Backup Options page.
The backup set section section includes name of the backup set and the description. The Backup set will expire section is used to specify number of days before backup set can be overwritten or you can specify a date after which the backup set can be overwritten.We choose the default of 0 days which means the backup set will never expire. There is the interesting section of compression and in which we set backup compression to use default server setting. This means that the compression is going to use whatever option is set when installing sql server. SQL server can be set to use compression when backing up or do not use compression and this is done at installation. You can also click on the drop down and choose compress back up or do not compress back up. I usually go with compress backup as it makes the files smaller compared to the original size on the server and easy to transfer across the network. Some people will choose do not compress back up especially if they have issue restoring the compressed backup, apart from that l see no reason why not to compress the back up. If you want to check what is the default setting on the server then use the query below.
SELECT *
FROM sys.configurations
WHERE name = 'backup compression default' ;
There is the last option of Encryption of the backup but we are not interested in this. This is only when you are backing up to a new media set by the way. With Encryption, the backup is protected and can only be restored if you know the encryption key.
So before we click on the OK button to perform the back up, take a look at the top of the above snapshot and see script option. With this option, you can generate sql script of the entire settings you you have just performed to a file, to your clipboard or create a job out of it. Isn’t that cool? Here is the script generated for our set up.
BACKUP DATABASE [AdventureWorks2016CTP3] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2016CTP3.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2016CTP3-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM, CONTINUE_AFTER_ERROR
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'AdventureWorks2016CTP3' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'AdventureWorks2016CTP3' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''AdventureWorks2016CTP3'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\AdventureWorks2016CTP3.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
Generated script can be summarize as below.
BACKUP DATABASE [databaseName] TO DISK = [fullnameofbackupfile], NAME = [nameofbackupset]
If you want to check which back ups have been perform on the server, use the query below. Ordering it by backup_set_id descending puts the latest back up information at the top. Also l added the script to check what is the status of the back up in case its taking some time to complete.
SELECT * FROM msdb..backupset
ORDER BY backup_set_id DESC
-- check back up status
SELECT command,s.text,start_time,percent_complete,estimated_completion_time/1000 as "seconds to go",
dateadd(second,estimated_completion_time/1000, getdate()) as "estimated completion time"
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
After the backup is done, you are ready to restore it to another server or your desktop if you want to do so. I will be covering restoring databases in another post. Let me know what you think about this lesson and any suggestions welcome. Thanks for reading.
