Oct 19, 2007

MSSQL 2005 Backup and Restore SQL Syntax

RESTORE DB
---------------
RESTORE DATABASE myNewDb
FROM DISK = 'C:\Documents and Settings\Administrator\Desktop\family_backup.bak'
WITH MOVE 'myOldDb_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\myNewDb.mdf',
MOVE 'myOldDb_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\myNewDb.ldf',
REPLACE

**Note:
If you are about to restore the old version sql2000 DB,
you can create a sql2005 db with the same name first,
change the DB Properties > Options > Capability Level > SQL2000 (80),
then you proceed with the above sql syntax


BACKUP DB
--------------
use [myNewDb]
BACKUP DATABASE [myNewDb] TO DISK = N'D:\Database Backup\myNewDb.bak'
WITH NOFORMAT, NOINIT, NAME = N'myNewDb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

No comments:

Post a Comment