Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Restoring a database onto a Reporting Server

Status
Not open for further replies.

DBAWinnipeg

Programmer
Apr 14, 2004
173
CA
OK Once again I'm at the mercy of you fine tek-tip coding machines :)

(Manditory suck up for quick response) heehhe

OK we do backups through the maintenance planner so our file names are as follows:

PROD_db_200404200102.bak

Now if I try and restore that file via a script

RESTORE DATABASE CRMREP
FROM DISK = '\\crmprd01\Data\CRMPRD_db_200404200102.BAK'

I'm wondering 2 things

1) does the Restore Database function accept wildcards
ie) CRMPRD_db_*.BAK

or

2) does anyone have a script to run that will search a folder for a file with a wildcard (CRMPRD_db_*.BAK) and return the complete file name which I then could use in my RESTORE DATABASE script.




Thanks in advance!!!

Colin in da 'Peg :)
 
OK... I figured as much so now what? :)

Thanks in advance!!!

Colin in da 'Peg :)
 
A work around would be ...

Create a Temp or Local table as such:

Code:
CREATE TABLE [dbo].[DBs_On_Disc] (
	[DBOD_Key] [int] IDENTITY (1, 1) NOT NULL ,
	[DB_On_Disc_Text] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]

ALTER TABLE [dbo].[DBs_On_Disc] WITH NOCHECK ADD 
	CONSTRAINT [PK_DBs_On_Disc] PRIMARY KEY  CLUSTERED 
	(
		[DBOD_Key]
	) WITH  FILLFACTOR = 90  ON [PRIMARY]
Then run the following commands to pull the files you need. Of course, change the needed commands to meet your servers directory structure:
Code:
EXEC	Master.dbo.xp_cmdshell 'dir C:\MyDir\CRMPRD_db_*.BAK /s >  C:\DB2Restr.txt'

INSERT INTO DBs_On_Disc 
EXEC Master.dbo.xp_cmdshell 'type C:\DB2Restr.txt'

EXEC	Master.dbo.xp_cmdshell 'del C:\DB2Restr.txt'

Now you have a table loaded w/ the backup files you may need to restore. You may need to format the DB_On_Disc_Text field but that should be a piece of cake.

Once you have that, read thru the DB_On_Disc_Text table incrementing DBOD_Key from 1 by 1 in a WHILE loop and build your RESTORE syntax dynamically.

Hope this help!

Thanks

J. Kusch
 
DBAWinnepeg,

Please do not cross-post. You also have this question in the programming forum at Thread183-824527. This is the more appropriate forum for this topic.

-SQLBill
 
Thanks for the responses

function worked like a charm


Thanks again


DBAWinnipeg
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top