Using SQL Management Objects to create and restore SQL Databases.

  • Amriteshwar Singh

    SSC Rookie

    Points: 36

    Comments posted to this topic are about the item Using SQL Management Objects to create and restore SQL Databases.

  • Charles Kincaid

    SSChampion

    Points: 13593

    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.

    ATBCharles Kincaid

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice article ........

  • Johan Bijnens

    SSC Guru

    Points: 134265

    - 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


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

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

    - 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[/url] :alien: but most of the time this is me :hehe:

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply