Binaryworld - Backup and Restore of SQL Server database using easy script. ... [ SQL Server -> Backup-Restore ]

In this article I will show you the most common backup/restore commands.

- How to take full backup of a SQL Server database.
- How to resore full backup over the existing/new database.
- How to restore full backup at different path over existing/new database.

How to take full backup of a SQL Server database.

Click here to copy the following block

BACKUP DATABASE Northwind TO DISK='c:\Nwind_091505_full.bkp'

How to resore full backup over the existing/new database.

To replace your existing database with your full backup you have to specify REPLACE option. There are many other option you can use with WITH clause but we will not discuss in this article, check the MSDN for more info.

Click here to copy the following block

RESTORE DATABASE Northwind
FROM DISK = N'c:\Nwind_091505_full.bkp' WITH REPLACE

How to restore full backup with different path/filename.

If you restore full backup to new database or exising database then 2 things you must consider.

if destination datbase logical file names are different then backedup database logical filenames then you have to specify MOVE option which can specify logical file names of backedup database and destination file path. To verify the logical filenames contained in your backup you can run the following command.

Click here to copy the following block

exec sp_helpdb Northwind

Since we took backup of northwind database we have to run sp_helpdb for Northwind

Now run the following command which will either create a new database Northwind_New or it will overwrite if already exists

Click here to copy the following block

RESTORE DATABASE Northwind_New
FROM DISK = N'c:\Nwind_091505_full.bkp'
WITH REPLACE ,
MOVE N'Northwind' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\Northwind_new.MDF',
MOVE N'Northwind_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\Northwind_new_Log.LDF'