SQL Backup and Restore Commands

22 Jul

Some useful code snippets for backing up and restoring data in MS SQL.

Create full backup: (also output times for reference)

select {fn Now()}  as ‘Start Time’
BACKUP DATABASE DBNAME TO DISK = ‘d:\DBNAME.bak’ WITH INIT
select {fn Now()}  as ‘End Time’

Create differential backup (from your last full backup):

BACKUP DATABASE DBNAME TO DISK = ‘d:\DBNAME.diff’ WITH INTI, DIFFERENTIAL

Restore full backup (and allow further backups to be restored):

RESTORE DATABASE DBNAME FROM DISK = ‘d:\DBNAME.bak’ WITH REPLACE, NORECOVERY

Now, if the restore is taking place on a differnt box, your logs etc may all want to be in different locations. First, extract the data from the backup file and note the logical names for the log and data files using this command:

RESTORE FILELISTONLY
FROM DISK = ‘d:\DBNAME.bak’
GO

Then, extend the restord command to specify the old and new locations of the log and data files:

RESTORE DATABASE DataBaseName FROM DISK = ‘d:\DBNAME.bak’ WITH REPLACE, NORECOVERY, MOVE ‘DBNAME_Data’ TO ‘d:\DBNAME.mdf’, MOVE ‘DBNAME_Log’ TO ‘d:\DataBaseName .ldf’

Restore differential backup:

RESTORE DATABASE DBNAME FROM DISK = ‘d:\DBNAME.diff’  WITH RECOVERY

Display log file sizes

Display log file sizes:
DBCC SQLPERF(logspace)

Display backup data information, including file lists:

RESTORE FILELISTONLY
FROM DISK = ‘d:\DBNAME.bak’
GO

And if any databases are still in Recovery… status, then this will take them out of recovery mode and put them live:

RESTORE  DATABASE DBNAME WITH RECOVERY