Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

check if database exist before restore Expand / Collapse
Author
Message
Posted Wednesday, May 8, 2013 9:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:46 AM
Points: 33,200, Visits: 15,343
You can query sys.databases to see if the database exists.

If so, then you'd have to query sys.database_files in that database to get the file location. You can do that like this:
DECLARE @sqlCommand nvarchar(1000)
DECLARE @db varchar(75)
DECLARE @file VARCHAR(500)
SET @db = 'AdventureWorks2008'
SET @sqlCommand = 'select @f = physical_name from ' + @db + '.sys.database_files where file_id = 1'
EXECUTE sp_executesql @sqlCommand, N'@f varchar(500) OUTPUT', @f=@file OUTPUT
select @file

The problem is that you might have multiple files. You'll have at least 2, but you could have more. It's not necessarily simple to get all these and construct a restore statement.

If it doesn't exist, then you just restore. You don't need the default path to do the restore.

However the thing you do need to do is read the backup file to determine how many files you need. A RESTORE FILELIST ONLY will do this, with the backup path. From there, you can reconstruct the WITH MOVE commands. It's not hard to do, but it takes a little work. This script can help there:

http://www.sqlservercentral.com/scripts/T-SQL/63271/









Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1450641
Posted Wednesday, May 8, 2013 12:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 94, Visits: 755
Steve Jones - SSC Editor (5/8/2013)
You can query sys.databases to see if the database exists.

If so, then you'd have to query sys.database_files in that database to get the file location. You can do that like this:
DECLARE @sqlCommand nvarchar(1000)
DECLARE @db varchar(75)
DECLARE @file VARCHAR(500)
SET @db = 'AdventureWorks2008'
SET @sqlCommand = 'select @f = physical_name from ' + @db + '.sys.database_files where file_id = 1'
EXECUTE sp_executesql @sqlCommand, N'@f varchar(500) OUTPUT', @f=@file OUTPUT
select @file

The problem is that you might have multiple files. You'll have at least 2, but you could have more. It's not necessarily simple to get all these and construct a restore statement.

If it doesn't exist, then you just restore. You don't need the default path to do the restore.

However the thing you do need to do is read the backup file to determine how many files you need. A RESTORE FILELIST ONLY will do this, with the backup path. From there, you can reconstruct the WITH MOVE commands. It's not hard to do, but it takes a little work. This script can help there:

http://www.sqlservercentral.com/scripts/T-SQL/63271/






Thanks Steve...

I'm looking for all the files..i got the script here...

declare @db varchar(100)
set @db = 'test_restore'
select physical_name from sys.master_files where DB_NAME(database_id) = @db

but we need to frame something like this....
check for database , if it exists execute above code else get the default path( we alreayd have script for this )

Fyi, we dont do sql restore directly..we use commvault ( third party tool ) ..because of this we dont have much flexibility and we cannot see the backup path...

please let me know your thoughts

thanks in advance..
Post #1450754
Posted Wednesday, May 8, 2013 2:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 94, Visits: 755
i'm missing something here....please let me know...

DECLARE @db varchar(100)
DECLARE @defaultDataLocation nvarchar(400)
DECLARE @defaultLogLocation nvarchar(400)

--if (@db = 'test_restore')
IF @db = 'test_restore'
PRINT 'The database exists'
Begin
select physical_name from sys.master_files where DB_NAME(database_id) = @db
END
ELSE

EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData',
@defaultDataLocation OUTPUT

EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',
@defaultLogLocation OUTPUT
SELECT @defaultDataLocation AS 'Default Data Location',@defaultLogLocation AS 'Default Log Location'
Post #1450789
Posted Wednesday, May 8, 2013 4:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:46 AM
Points: 33,200, Visits: 15,343
You're not setting the result of the select in the first part to a variable.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1450832
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse