Database Restore on Existing one with same name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Database Restore on Existing one with same name

Author

Topic

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-08-25 : 06:45:35

Needs TSQL for restoring a database from backup on the server that already has the database with same name.

Kristen
Test

22859 Posts

Posted - 2005-08-25 : 08:14:57

NOTE: This post has been superseded by: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170474

This is what I use. The "logical names" may NOT comprise the database name, and thus may need working out using RESTORE FILELISTONLY.


USE master	-- (Can't sit in the database whilst its being restored!)
GO

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Or this to prevent even other SA users connecting to the DB
-- ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

-- Restore Full Backup
RESTORE DATABASE MyDatabase
	FROM DISK = 'x:\MSSQL\BACKUP\MyBackupFilename_Full.BAK'
	WITH
		REPLACE,
		NORECOVERY,	-- Use if more T/Logs to recover
--		RECOVERY,	-- Use if NO more T/Logs to recover
		STATS = 10,	-- Show progress (every 10%)
	MOVE 'MyDatabase_Data' TO 'x:\MSSQL\DATA\MyDatabase.mdf', 
	MOVE 'MyDatabase_Log' TO 'x:\MSSQL\DATA\MyDatabase.ldf'
GO

-- Optional restore Differential Backup
RESTORE DATABASE MyDatabase
	FROM DISK = 'x:\MSSQL\BACKUP\MyDatabase_Diff.BAK'
	WITH
--		RECOVERY	-- Use if NO more file to recover
		NORECOVERY	-- Use if there are T/Logs to recover
GO

-- Optional restore Transaction Log Backup
RESTORE LOG MyDatabase
	FROM DISK = 'x:\MSSQL\BACKUP\MyDatabase_yyyymmdd_hhmm_Trans.BAK'
	WITH
--		RECOVERY	-- Use if NO more T/Logs to recover
		NORECOVERY	-- Use if more T/Logs to recover
GO

-- Set the database ready for use (after all backups have been restored)
RESTORE DATABASE MyDatabase WITH RECOVERY
GO

-- Rename logical names (only needed if restoring from a backup for a Different database):
ALTER DATABASE MyDatabase 
		MODIFY FILE (NAME = 'OrigDatabase_Data', NEWNAME = 'MyDatabase_data')
GO
ALTER DATABASE MyDatabase 
		MODIFY FILE (NAME = 'OrigDatabase_Log', NEWNAME = 'MyDatabase_log')
GO

Edit: 06-Jun-2006 Added "RESTORE DATABASE MyDatabase RECOVERY"
Edit: 12-Oct-2005 Added "SET OFFLINE" alternative
Edit: 14-Apr-2010 Fixed a typo! (RESTORE LOG)
Edit: 17-Aug-2011 Fixed a typo! (RESTORE DATABASE MyDatabase WITH RECOVERY)

Kristen

asarak
Starting Member

36 Posts

Posted - 2005-08-25 : 08:56:08

Hi bridge i also use the same thing with just the following script

use master
--i use master in order to have no connections open to the database i have to restore

DECLARE @mname VARCHAR(64)
DECLARE @pdate char(6) , @pday char(2) , @pmonth char(2) , @pyear char(2)

-- declare variables to use them just to take the backup set which is like bYYMMDD file name.
--YY = Year
--MM = Month
--DD = Day

SET @pday = right('0'+convert(varchar(2),day(getdate())),2)
SET @pmonth= right('0'+convert(varchar(2),month(getdate())),2)
SET @pyear = right(convert(char(4),year(getdate())),2)
SET @mname ='d:\database\b'+@pyear+ @pmonth + @pday
-- sets the proper backup name

select @mname

restore database databasename from DISK = @mname

-- if you have already use once the restore and fix the file paths corerctly, it will
-- automatically use the same by any other restore you want (of the same database name)
-- After restoring there is no need to run anything else except a script to fix logins (if
-- you still have this kind of problem)

Hope to help you.
RGrds
ASARAK

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 07:28:37

NOTE: This post has been superseded by: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170474

This was in another thread ( http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41804 ), I've repeated it here in case useful to folk.

To restore a database you need to do three things:

1) Interogate the backup file to find what its logical names are
2) Restore the file into the appropriate database (including indicating where the path is)
3) "RENAME" the logical names to more appropriate ones

Step (1a) Directory Listing:


EXEC master.dbo.xp_cmdshell 'DIR x:\Mypath\*.* /O-D'

Step (1b) Logical Device names:


RESTORE FILELISTONLY FROM DISK = 'x:\Mypath\MyFullBackupfile.BAK'

This will give something like:


LogicalName        PhysicalName                 Type FileGroupName Size      MaxSize
------------------ ---------------------------- ---- ------------- --------- --------------
MyOldDatabase_data D:\MSSQL\DATA\MyDatabase.mdf D    PRIMARY       355467264 35184372080640
MyOldDatabase_log  D:\MSSQL\DATA\MyDatabase.ldf L    NULL          168624128 35184372080640

Step (2a):


RESTORE DATABASE MyNewDatabaseName
	FROM DISK = 'x:\Mypath\MyFullBackupfile.BAK'
	WITH
		REPLACE,	-- Overwrite DB - if one exists
		NORECOVERY,	-- Use if DIFFs / T/Logs to recover
--		RECOVERY,	-- Use if NO more files to recover, database will be set ready to use
		STATS = 10,	-- Show progress (every 10%)
	MOVE 'MyOldDatabase_data' TO 'x:\MySQLDataPath\MyNewDatabaseName.mdf', 
	MOVE 'MyOldDatabase_log' TO 'x:\MySQLDataPath\MyNewDatabaseName.ldf'
GO

Step (2b)

If you have a Differential backup to restore do this:


RESTORE DATABASE MyNewDatabaseName
	FROM DISK = 'x:\Mypath\MyDifferentialBackupfile.BAK'
	WITH
		NORECOVERY	-- Use if there are T/Logs to recover
--		RECOVERY	-- Use if NO more files to recover, database will be set ready to use

Step (2c)

If you have any Transaction Backup files to restore repeat this for each one in turn:


RESTORE LOG MyNewDatabaseName
	FROM DISK = 'x:\Mypath\MyTransactionBackupfile.BAK'
	WITH
		NORECOVERY	-- Use if more T/Logs to recover
--		RECOVERY	-- Use if NO more T/Logs to recover, database will be set ready to use

Step (3)


-- Set the database ready for use (after all backups have been restored)
RESTORE DATABASE MyDatabase WITH RECOVERY
GO

-- Rename logical names:
ALTER DATABASE MyNewDatabaseName MODIFY FILE (NAME = 'MyOldDatabase_data', NEWNAME = 'MyNewDatabase_data')
GO
ALTER DATABASE MyNewDatabaseName MODIFY FILE (NAME = 'MyOldDatabase_log', NEWNAME = 'MyNewDatabase_log')
GO

To get a list of TLog files to restore see: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60109

If you want to restore to a point-in-time then for the LAST TLog restore use:


RESTORE LOG MyNewDatabaseName
	FROM DISK = 'x:\Mypath\MyLastTransactionBackupfile.BAK'
	WITH
		NORECOVERY	-- Use if more T/Logs to recover
--		RECOVERY	-- Use if NO more T/Logs to recover, database will be set ready to use
		, STOPAT = '20061231 10:31'	-- Restore up to 31-Dec-2006 10:31

Edit: 06-Jun-2006 Added "RESTORE DATABASE MyDatabase RECOVERY"
Edit: 02-Nov-2006 Added "STOPAT"

Edit: 17-Aug-2011 Fixed a typo! (RESTORE DATABASE MyDatabase WITH RECOVERY)

Kristen

Kristen
Test

22859 Posts

Posted - 2006-11-11 : 01:45:01

NOTE: This post has been superseded by: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170474#667478

To see what was recently restored:


SELECT DISTINCT TOP 100
	RH.destination_database_name,
 	RF.file_number,
	RH.restore_date,
	RF.destination_phys_drive,
	RF.destination_phys_name,
	[Backup Set Name]=BS.name,
	BS.description,
	BS.database_creation_date,
	BS.backup_start_date,
	BS.database_name,
	BS.server_name,
	RH.restore_type,
	RH.replace,
	RH.stop_at
FROM	msdb.dbo.restorehistory AS RH
	LEFT OUTER JOIN msdb.dbo.restorefile AS RF
		ON RF.restore_history_id = RH.restore_history_id
	LEFT OUTER JOIN msdb.dbo.restorefilegroup AS RG
		ON RG.restore_history_id = RH.restore_history_id
	LEFT OUTER JOIN msdb.dbo.backupset AS BS
		ON BS.backup_set_id = RH.backup_set_id
WHERE	1=1
-- 	AND RH.destination_database_name = N'MyDatabaseName'	-- SELECT DB_NAME()
	-- restore_type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
-- 	AND RH.restore_type = 'D'
ORDER BY RH.restore_date DESC,
	RF.file_number DESC

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-11 : 14:26:11

You cna use the script on this link to generate the restore command directly from the backup file.

Create Restore Command from DB Backup File
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71665

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 11:18:03

NOTE: This post has been superseded by: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170474#667477

And to see what was recently backed up:


SELECT TOP 100
--	database_name,
	backup_set_id,
	backup_start_date,
	backup_finish_date,
	type,
	backup_size,
	name,
	user_name,
	first_lsn,
	last_lsn,
	database_backup_lsn,
	description
--	, * 
FROM	msdb.dbo.backupset
WHERE	1=1
--	AND database_name = N'MyDatabaseName'	-- SELECT DB_NAME()
	-- type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
-- 	AND type='D'
ORDER BY backup_start_date DESC, database_name

-- Version 2
SELECT TOP 100
--	BS.database_name,
	BS.backup_set_id,
	BS.backup_start_date,
	BS.backup_finish_date,
	BS.type,
	BS.backup_size,
	BS.name,
	BS.user_name,
	BS.first_lsn,
	BS.last_lsn,
	BS.database_backup_lsn,
	BS.description,
	BMF.family_sequence_number,
	BMF.device_type,
	BMF.physical_device_name
--	, * 
FROM	msdb.dbo.backupset AS BS
	LEFT OUTER JOIN msdb.dbo.backupmediafamily AS BMF
		ON BMF.media_set_id = BS.media_set_id
WHERE	1=1
	AND BS.database_name = N'MyDatabaseName'	-- SELECT DB_NAME()
	-- type : D=Full, I=Differential, L=Log, G=FileGroup, V=VerifyOnly
-- 	AND BS.type='D'
ORDER BY BS.backup_start_date DESC, BS.database_name, BMF.family_sequence_number

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources