SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Database Restore on Existing one with same name
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bridge
Yak Posting Veteran

93 Posts

Posted - 08/25/2005 :  06:45:35  Show Profile  Reply with Quote
Needs TSQL for restoring a database from backup on the server that already has the database with same name.

Kristen
Test

United Kingdom
22431 Posts

Posted - 08/25/2005 :  08:14:57  Show Profile  Reply with Quote
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

Edited by - Kristen on 01/20/2012 05:01:35
Go to Top of Page

asarak
Starting Member

Greece
36 Posts

Posted - 08/25/2005 :  08:56:08  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/16/2006 :  07:28:37  Show Profile  Reply with Quote
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

Edited by - Kristen on 01/20/2012 05:01:50
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 11/11/2006 :  01:45:01  Show Profile  Reply with Quote
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

Edited by - Kristen on 01/20/2012 05:02:42
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/11/2006 :  14:26:11  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 11/30/2006 :  11:18:03  Show Profile  Reply with Quote
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

Edited by - Kristen on 01/20/2012 05:03:20
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000