Mike
Tips, tricks and solutions for software developers.
 

Backing up and restoring SQL Server databases

By Mike Gledhill

Backing up

Here's how to add a new index to a SQL Server table:

BACKUP DATABASE [Northwind]
TO DISK = 'D:\SQL Server\Northwind_Backup.bak' WITH INIT
RESTORE FILELISTONLY
FROM DISK = 'D:\SQL Server\Northwind_Backup.bak' WITH FILE = 1

This will display results like this:

After running the Backup command

Note that you don't have to stop the database before backing it up... but it is recommended.

Actually, you don't need the second part of that command. You could just run:

BACKUP DATABASE [Northwind]
TO DISK = 'D:\SQL Server\Northwind_Backup.bak' WITH INIT

..but personally, I like to just check that SQL Server is at least able to access it's backup file, after creating it.

Actually, I always truncate the Transaction Log before running a backup, but most users seem to think this is a terrible thing to do (in case you need to restore from a Log file at a later date), so I'm not allowed to recommend doing this...

Backing up - SQL Server 2008 and above

If you are using SQL Server 2008 or later, then you can ask for the backup to be compressed:

BACKUP DATABASE [Northwind]
TO DISK = 'D:\SQL Server\Northwind_Backup.bak' WITH INIT, COMPRESSION
RESTORE FILELISTONLY
FROM DISK = 'D:\SQL Server\Northwind_Backup.bak' WITH FILE = 1

Restoring from a backup

To restore a database from a .bak backup file, first you need to find the internal names of the data and log file:

RESTORE FILELISTONLY
FROM DISK = 'D:\SQL Server\Northwind_Backup.bak' WITH FILE = 1

After running the Backup command

Do you see the two "Logical Names" shown in the first column ?

You need to quote these in the RESTORE command, and specify where SQL Server should write the new Data and Log file for this database.

RESTORE DATABASE [Northwind]
FROM DISK = 'D:\SQL Server\Northwind_Backup.bak'
WITH MOVE 'Northwind' TO 'D:\SQL Server\Northwind.mdf',
MOVE 'Northwind_log' TO 'D:\SQL Server\Northwind_Log.mdf'

Bear in mind that if you have a compressed backup file, then any versions of SQL Server prior to SQL Server 2008 won't be able to restore them.

Comments