Saturday, September 11, 2010

Copying SQL Server databases with Backup and Restore

Please read the following articles:
Copying Databases with Backup and Restore
How to Restore a Database to a New Location and Name (Transact-SQL)
Examples
-------------
How to change recovery model of a database
----------------------------------------------
alter database [Database1] set recovery  full
alter database [Database1] set recovery  simple
==================================
How to backup a database
---------------------------
backup database [Database1] to disk  = 'c:\Backup\back.bak' with init
===============================================
How to find information about a database
-----------------------------------------
SP_HELPDB [Database1]
===============================================
How to find the logical name of the database
---------------------------------------------
RESTORE FILELISTONLY FROM DISK='full_path_of_backup_file'
===============================================
How to restore the database to a new server with a new name
------------------------------------------------------------
RESTORE DATABASE [Database2]
FROM DISK='full_path_of_backup_file'
WITH MOVE 'Database1' TO 'C:\Customers\DBS\Database2.mdf',
MOVE 'Database1_log' TO 'C:\Customers\DBS\Database2_log.ldf'
---------------------------------------
How to restore a vcenter database
---------------------------------------
----Alter Database to single user mode
ALTER DATABASE VIM_VCDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
----Restore Database
RESTORE DATABASE VIM_VCDB
FROM DISK='C:\Backup\VCDB.bak'
WITH REPLACE,
MOVE 'vcdb' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\VIM_VCDB.mdf',
MOVE 'vcdb_log' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\VIM_VCDB_log.ldf'
----If there is an error in statement above, enter the following 
----command to revert to multi user
ALTER DATABASE VIM_VCDB SET MULTI_USER
GO

No comments:

Post a Comment