Create and then restore - Help Please

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

Old Forums

CLOSED - General SQL Server

Create and then restore - Help Please

Author

Topic

Capt_Ron
Starting Member

45 Posts

Posted - 2005-07-11 : 12:01:47

I have been searching for a way to do the following. I have found pieces, but I don't understand how I can put them together to do what I want.

Here's what I need to do.

I have a VB.NET app that I need to create 4 databases and restore into those 4 databases backups. I have a database that has yearly backups. I have Last Year, 2 Years Ago, 3 Years Ago, and 4 Years Ago. They are the same database, but cleaned out at the end of each fiscal year.

I need to run a SQL that will create the 4 databases and then force a restore of the backups into each database. One of the problems is because the backups are of the same database, all the file info is the same. Same DB name, same Physical Names, Logical names, etc...

Could someone point me in the right direction?

Thank you for your help

Ron.

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-07-11 : 12:21:23

Restore the first database. Use sp_renamedb to rename the database database1999. Restore the second database using the WITH MOVE option and then use sp_renamedb to rename it database2000. Restore the third database with the WITH MOVE option.... catch my drift?

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me through the forum.

Kristen
Test

22859 Posts

Posted - 2005-07-11 : 13:07:31

Here's a RESTORE script (output from my Restore Routine), this should be "complete" in terms of all the commands you are likely to need. (Assuming only ONE MDF/LDF file pair being restored to a single drive location)

You need to substitute the TARGET_DB name you want to restore to (If it exists it will be overwrriten, if not it will be created), AND the SOURCE_DB_data and SOURCE_DB_log "logical" names - which you will have to discover from the existing backups, or from the existing database FROM WHICH the backups were made.

The straightforward substituations are in Green, the Logical Names ones are in Red


-- Get the Logical Names to substitute in the main script:
RESTORE FILELISTONLY FROM DISK = 'D:\MSSQL\BACKUP\MyFilename.BAK'

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

ALTER DATABASE TARGET_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE TARGET_DB
	FROM DISK = 'D:\MSSQL\BACKUP\MyFilename.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 'SOURCE_DB_data' TO 'D:\MSSQL\DATA\TARGET_DB.mdf', 
	MOVE 'SOURCE_DB_log' TO 'D:\MSSQL\DATA\TARGET_DB.ldf'
GO

/*
-- Restoring additional DIFF/TRANSACTION files, after the main backup:
-- Make sure the NORECOVERY option is used above
-- then repeat the following for each transaction log, in order

-- Restore appropriate DIFFERENTIAL backup:
RESTORE DATABASE TARGET_DB
	FROM DISK = 'D:\MSSQL\KBM_BACKUP\TARGET_DB_yyyymmdd_hhmm_Diff.BAK'
	WITH
--		RECOVERY	-- Use if NO more file to recover, database will be set ready to use
		NORECOVERY	-- Use if there are T/Logs to recover

-- Restore each LOG file in sequence:
RESTORE LOG TARGET_DB
	FROM DISK = 'D:\MSSQL\KBM_BACKUP\TARGET_DB_yyyymmdd_hhmm_Trans.BAK'
	WITH
--		RECOVERY	-- Use if NO more T/Logs to recover
		NORECOVERY	-- Use if more T/Logs to recover
*/

-- Rename logical names:
	ALTER DATABASE TARGET_DB MODIFY FILE (NAME = 'SOURCE_DB_data', NEWNAME = 'TARGET_DB_data')
GO
	ALTER DATABASE TARGET_DB MODIFY FILE (NAME = 'SOURCE_DB_log', NEWNAME = 'TARGET_DB_log')
GO

Kristen

Capt_Ron
Starting Member

45 Posts

Posted - 2005-07-11 : 14:27:38

Kristen,
First of all, Thank you very much. I really appreciate you taking the time to do this.

Let me check to make sure I understand what this is doing:
My questions are in purple in the code.
Also I can pass the path to the backup files and the path to the SQL Server database files right?

Thanks again
Ron


-- Get the Logical Names to substitute in the main script:
PARAM @BakPath varchar(100), @SQLPath varchar(100)

RESTORE FILELISTONLY FROM DISK = @BakPath  --'D:\MSSQL\BACKUP\MyFilename.BAK'

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

--<<<<<<
You set the DB to singleuser.  
  This locks the DB so only this SQL can run against it?
  DO you have to change it back later?>>>>>>
ALTER DATABASE TARGET_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE TARGET_DB  --<<<<<< If this does not exist will it create it? >>>>>>
	FROM DISK = 'D:\MSSQL\BACKUP\MyFilename.BAK'
	WITH
		REPLACE,
--		NORECOVERY,	-- Use if more T/Logs to recover
		RECOVERY,	-- Use if NO more T/Logs to recover
                --<<<<<< I only have one BAK file for each database so I'd use RECOVERY right?>>>>>>
		STATS = 10,	-- Show progress (every 10%)
        --<<<<<< SOURCE_DB_data and _log are the Logical names? >>>>>>
	MOVE 'SOURCE_DB_data' TO 'D:\MSSQL\DATA\TARGET_DB.mdf', 
	MOVE 'SOURCE_DB_log' TO 'D:\MSSQL\DATA\TARGET_DB.ldf'
GO

/*

--<<<<<< I have no other files for the database so these 2 sections would not be needed, right? >>>>>>
-- Restoring additional DIFF/TRANSACTION files, after the main backup:
-- Make sure the NORECOVERY option is used above
-- then repeat the following for each transaction log, in order

-- Restore appropriate DIFFERENTIAL backup:
RESTORE DATABASE TARGET_DB
	FROM DISK = 'D:\MSSQL\KBM_BACKUP\TARGET_DB_yyyymmdd_hhmm_Diff.BAK'
	WITH
--		RECOVERY	-- Use if NO more file to recover, database will be set ready to use
		NORECOVERY	-- Use if there are T/Logs to recover

-- Restore each LOG file in sequence:
RESTORE LOG TARGET_DB
	FROM DISK = 'D:\MSSQL\KBM_BACKUP\TARGET_DB_yyyymmdd_hhmm_Trans.BAK'
	WITH
--		RECOVERY	-- Use if NO more T/Logs to recover
		NORECOVERY	-- Use if more T/Logs to recover
*/

-- Rename logical names:
-- <<<<<< This will rename the logical names in SQL server.  As opposed to the MOVE command above renames the actual file name? >>>>>>
	ALTER DATABASE TARGET_DB MODIFY FILE (NAME = 'SOURCE_DB_data', NEWNAME = 'TARGET_DB_data')
GO
	ALTER DATABASE TARGET_DB MODIFY FILE (NAME = 'SOURCE_DB_log', NEWNAME = 'TARGET_DB_log')
GO

Kristen
Test

22859 Posts

Posted - 2005-07-11 : 18:40:13

You set the DB to singleuser.
This locks the DB so only this SQL can run against it?
DO you have to change it back later?

You don't need to set it back again - once the restore is done it is back to its original state

If this does not exist will it create it?

If there is NO existing database it will [always] be created. The [optional] REPLACE statement will force any existing database to be replaced.

I only have one BAK file for each database so I'd use RECOVERY right?

For single, FULL, backup file restore use RECOVERY on the first RESTORE command, and then ignore the DIFF & TRANS/LOG restore commands

SOURCE_DB_data and _log are the Logical names?

Yes, SOURCE_DB_data and _log are the Logical names (which you will have got from the "RESTORE FILELISTONLY ..." command)

This will rename the logical names in SQL server. As opposed to the MOVE command above renames the actual file name?

Yes, this is a logical file rename, and yes the earlier MOVE took care of the physical files. This is purely for asthetics, but I think it looks a bit confusing if the physical database filename are "FOO" and the logical names are "BAR" and thus represent some other database from which it was restored!

Also I can pass the path to the backup files and the path to the SQL Server database files right

Yup - they need to be local to the server (well, maybe the Backup file can be on a UNC or Share [I forget what's allowed!], but the database files should local)

Kristen

Capt_Ron
Starting Member

45 Posts

Posted - 2005-07-14 : 11:49:42

Thank you very much.
I tested it with a small database and now I'm going to totally mess things up by trying to pass variables to it for things such as the old database name, new database name, backup file location, etc...

Thanks again
Ron

Kristen
Test

22859 Posts

Posted - 2005-07-14 : 12:25:37

Hehehe .. you may be gone for some time then, eh?!

Kristen

Capt_Ron
Starting Member

45 Posts

Posted - 2005-07-14 : 12:28:09

That scares me

It shouldn't be all that hard.

Ron

Capt_Ron
Starting Member

45 Posts

Posted - 2005-07-14 : 14:48:32

Kirsten,
I keep getting errors when I try and create the procedure.

Could you take a look?

It doesn't like my variables. Especially the one in ALTER DATABASE.

Thanks
Ron


CREATE PROCEDURE WinSnapRestore

 @FileName1 varchar(100), @NewDB1 varchar(50), @OldLogicalData varchar(20), @OldLogicalLog varchar(20), @NewPhysNameData varchar(100), @NewPhysNameLog varchar(100), @NewLogicalNameData varchar(20), @NewLogicalNameLog varchar(20)

AS

-- Get the Logical Names to substitute in the main script:
RESTORE FILELISTONLY FROM DISK = @FileName1  --'C:\winsnap.BAK'

-- *** MAIN SCRIPT ** --


--ALTER DATABASE winsnap1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE @NewDB1 SET SINGLE_USER WITH ROLLBACK
GO

RESTORE DATABASE @NewDB1  --winsnap1
	FROM DISK = @FileName1  --'C:\winsnap.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 @OldLogicalData TO @NewPhysNameData  --'C:\Program Files\Microsoft SQL Server\MSSQL\Data\winsnap1.mdf', 
	MOVE @OldLogicalLog TO @NewPhysNameLog  --'C:\Program Files\Microsoft SQL Server\MSSQL\Data\winsnap1_log.ldf'
GO


	ALTER DATABASE @NewDB1 MODIFY FILE (NAME = @OldLogicalData, NEWNAME = @NewLogicalNameData)
GO
	ALTER DATABASE @NewDB1 MODIFY FILE (NAME = @OldLogicalLog, NEWNAME = @NewLogicalNameLog)
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources