May 7, 2013 at 7:52 am
hi,
i'm in the process of automating the database restore using powershell....
but before getting the default paths of data and log files of an instance...i would like to check if database already exists or not ?
Please let me know how to get that information...
IF EXISTS (select d.name,f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and d.name = 'Test_Restore')
select f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and d.name = 'master'
before the restore i would like to check if db exist, if it's in it should generate the data and log file path . if it's not in we need to get default path of instance or master db path.
please let me know
Thanks in advance
May 7, 2013 at 8:53 am
You are doing it right. What issue you are facing?
Instaed of if exists use "If NOT EXISTS"
Check for DB from sys. sysdatabases then get path from sys.master_files.
May 7, 2013 at 9:21 am
Thanks Neeraj..
i'm executing like this
IF NOT EXISTS (select d.name, f.physical_name from sys.master_files f, sys.sysdatabases d where f.database_id = d.dbid and
d.name = 'Test_Restore')
select f.physical_name from sys.master_files f, sys.databases d where f.database_id = d.database_id and
d.name = 'master'
i didnt get any output...
May 7, 2013 at 9:37 am
Do you already have Database named Test_Restore?
May 7, 2013 at 11:01 am
yes its already in instance
May 7, 2013 at 11:33 am
So, if already exists, it will not return anything from the second sql. Because you are checking for not exists?
I thought that you want the path for Master DB only if the Test_Restore doesn't exist.
May 7, 2013 at 12:05 pm
The default data path is stored in the registry. Here are some answers, but it's the one that reads the registry that you want
May 7, 2013 at 12:08 pm
Do not use sys.sysdatabases. It is a SQL Server 2000 system and is included for backward compatibility. You should use sys.databases instead.
May 8, 2013 at 7:17 am
thanks guys for the response...
my requirement has changed a bit...
i'm looking for something like this...
check if destination database exists, if it exists select physical location path ( data and log file )
else get the default instance path.
query to get the default instance path :
DECLARE @defaultDataLocation nvarchar(400)
DECLARE @defaultLogLocation nvarchar(400)
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'
Please let me know the script...this is for database restore automation....so when we give the destination db parameters...it must check the db first....
thanks in advance
May 8, 2013 at 7:21 am
Neeraj Dwivedi (5/7/2013)
So, if already exists, it will not return anything from the second sql. Because you are checking for not exists?I thought that you want the path for Master DB only if the Test_Restore doesn't exist.
Hi Neeraj,
you are correct when i put if exists i get master db....for if not exists i dont see anything...
but i'm looking in a different way...please see my latest post....
thanks for your time
May 8, 2013 at 9:14 am
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:
May 8, 2013 at 12:51 pm
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:
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..
May 8, 2013 at 2:23 pm
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'
May 8, 2013 at 4:45 pm
You're not setting the result of the select in the first part to a variable.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply