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

Using SQL Management Objects to create and restore SQL Databases. Expand / Collapse
Author
Message
Posted Wednesday, June 18, 2008 12:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 16, 2010 4:33 PM
Points: 22, Visits: 21
Comments posted to this topic are about the item Using SQL Management Objects to create and restore SQL Databases.
Post #518770
Posted Wednesday, June 18, 2008 3:41 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:06 PM
Points: 819, Visits: 2,023
Good article. Where can I get a list of all the objects, properties, and methods?

We recently installed a new drive on one of our SQL boxes. The boss was moving databases by detach, move file, reattach. I showed him a trick for moving files to new location using T-SQL in 4 lines (found it here). He got out a text editor and whipped up a script to do the rest. I was thinking about a "point-and-click" program to do the mundane sort of thing like that. Maybe these SMO objects could be the answer rather than my usual trick of writing Dot Net code that generates T-SQL statements and shoving them at the server.


ATB

Charles Kincaid

Post #519490
Posted Thursday, June 19, 2008 12:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:15 AM
Points: 5,471, Visits: 1,402
Nice article ........


Post #519620
Posted Thursday, June 19, 2008 2:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:03 AM
Points: 6,748, Visits: 8,544
- Nice intro to SMO for this purpose.

- Keep in mind, you have to inspect the .bak files content. They may contain more than one backup.

- You can even perform point in time restores using smo

If you add default location registry keys for your SQLInstance, you can even extract this info to automate your procedure.

-- ALZDBA
-- Add default Data- and LogFile locations
--
USE [master]
GO

Declare @DataPath NVarchar(2000)
Declare @LogPath NVarchar(2000)
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DataPath OUTPUT
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @LogPath OUTPUT
print @DataPath
print @LogPath

-- activate to install
If 0 = 1
begin
-- Adjust !!!
Select @DataPath = N'D:\MSSQL.1\MSSQL\Data' -- Aanpassen !
, @LogPath = N'D:\MSSQL.1\MSSQL\Data' -- Aanpassen !


/* SQL2005 always use xp_instance_regwrite */
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, @DataPath
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, @LogPath

end

/*
-- find default backup directory (elaborate to find datadirectory
-- activate cmdshell if disabled
EXEC sys.sp_configure N'show advanced options', N'1'
RECONFIGURE WITH OVERRIDE

declare @CmdShellSettingBefore table (cfgname varchar(128), minimum varchar(128), maximum varchar(128), config_value varchar(128), runvalue varchar(128))
insert into @CmdShellSettingBefore
EXEC sys.sp_configure N'xp_cmdshell'
if exists(select * from @CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')
begin
EXEC sys.sp_configure N'xp_cmdshell', N'1'
RECONFIGURE WITH OVERRIDE
end

-- Get Default-path
declare @DefaultBackupDirectory nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @DefaultBackupDirectory OUTPUT

Print 'Default BackupDirectory for instance [' + @@servername + '] = ' + @DefaultBackupDirectory
-- disable cmdshell if it was originaly disabled
if exists(select * from @CmdShellSettingBefore where cfgname = 'xp_cmdshell' and runvalue = '0')
begin
EXEC sys.sp_configure N'xp_cmdshell', N'0'
RECONFIGURE WITH OVERRIDE
end

*/




Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #519659
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse