How to backup and restore a SQL Database

If the SQL databases are in simple recovery

Backup

ALTER DATABASE dbname

SET READ_ONLY;

GO
 

Backup database dbname

to disk = 'C:\...\dbname.bak'

 
Restore


Restore database dbname

from disk =  'C:\...\dbname.bak'

with recovery, replace

 

ALTER DATABASE dbname

SET MULTI_USER;

GO

If the databases are in full recovery model

Backup

Backup database dbname

to disk = 'C:\...\dbname.bak'

Backup log dbname

to disk = 'C:\...\dbname.trn'

with norecovery

 
Restore

 
Restore database dbname

from disk = 'C:\...\dbname.bak'

with norecovery, replace

Restore log test2

from disk = 'C:\...\dbname.trn'

with recovery
 
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Tags: backup, restore, SQL

Trackback from your site.