Backup and Restore

Question

Post reply

Backup and Restore

ibnzakariyyahh

SSC Enthusiast

Points: 187
More actions
December 19, 2008 at 3:30 am

#203067

i want the code for backup database and restore database. thanks! please kindly help with the SQL statement.
Saheed.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

rajiniforu Ten Centuries Points: 1147 More actions · Answer 1

Hi,

You will get the complete syntax of various types of backup and restores from SQL BOL (books Online).

However find the below basic syntax for full backup and restore of the database as follows:

>> Backup databasa database_name to disk='Path'

eg: backup database master to disk='c:\backup\master.bak'

>> restore database database_name from disk='provide the backup location'

eg: restore database master from disk='c:\backup\master.bak'

Also find the below links for your reference, this would give you a complete detailed information on backup and restore:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

http://www.sqlservercentral.com/articles/Product+Reviews/litespeedforsqlserver/1862/

Regards,

Rajini

ibnzakariyyahh SSC Enthusiast Points: 187 More actions · Answer 2

ibnzakariyyahh

SSC Enthusiast

Points: 187

December 19, 2008 at 5:40 am

#915148

thank you very much raj. the code is very useful.

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 3

Be sure you read a bit to understand what you're doing and don't just run the code. You could get yourself into trouble.

If you have more questions, specifically about your situation, please feel free to post them.

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 4

...and just to add to Steve's comments, and to quote Paul Randall, your backup process is only as good as your restore process (loosely quoted), so test your backups by restoring them somewhere else to make sure that your process is working correctly. 🙂

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Atul DBA Hall of Fame Points: 3610 More actions · Answer 5

Here is the script that will allow you to backup each database within your instance of SQL Server. You will need to change the @path to the appropriate backup directory and each backup file will take on the name of "DBnameYYYDDMM.BAK".

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

======================

Assuming that the database backup file name is c:\backupsorthwindwind.bak, the following command will restore the database to nwind_new:

RESTORE DATABASE nwind_new FROM DISK = 'c:\backupsorthwindwind.bak'

WITH

MOVE 'northwind' TO 'd:\Program Files\Microsoft SQL

Server\Datawind_new.mdf'

MOVE 'northwind_log' TO 'd:\Program Files\Microsoft SQL

Server\Datawind_new_log.ldf'

Read this article for more information : http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx