Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: SSIS Package

  1. #1
    Join Date
    Jan 2006
    Posts
    54

    Unanswered: SSIS Package

    Possible?

    One of our partner organisations runs a scheduled maintenance job to leave a SQL Server backup file on a file share we can access.

    This is run every night.

    Initially I have imported the backup file onto our SQL Server (2008) manually.

    Can I set up an SSIS package to delete the current database and re-import from the backup file every day?

    Any other thoughts / solutions?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Any reason you don't want to simply use T-SQL and a job (i.e. no SSIS)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2006
    Posts
    54
    Quote Originally Posted by pootle flump
    Any reason you don't want to simply use T-SQL and a job (i.e. no SSIS)?
    Very new to SQL Server so not entirely sure on the syntax I'd need to use for T-SQL.

    Was the main reason I suggested SSIS as I was wondering if I could use a wizard...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well, to delete a database you write:
    Code:
    DROP DATABASE myDatabaseName
    Sample restore from backup:
    Code:
    --Database to restore
    RESTORE DATABASE MyDatabaseName --NOTE - we can enter whatever name we like for the restored db name.
    --From file location
        FROM DISK = '\\theShare\MyDatabaseName_restore.bak'
    --This is the only file so rollback any incomplete transactions
        WITH RECOVERY, 
    --Move dat file to...
        MOVE 'OLDLogicalDataName_Data' TO 
    'D:\SQLData\MyDatabaseName_Data.MDF', 
    --move log file to....
        MOVE 'OLDLogicalLogName_Log' 
    TO 'E:\SQLLogs\MyDatabaseName_Log.LDF',
    --Get a message every 10% of the process completed.
        STATS = 10
    GO
    check Books Online for more syntax info
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2006
    Posts
    54
    Quote Originally Posted by pootle flump
    Well, to delete a database you write:
    Code:
    DROP DATABASE myDatabaseName
    Sample restore from backup:
    Code:
    --Database to restore
    RESTORE DATABASE MyDatabaseName --NOTE - we can enter whatever name we like for the restored db name.
    --From file location
        FROM DISK = '\\theShare\MyDatabaseName_restore.bak'
    --This is the only file so rollback any incomplete transactions
        WITH RECOVERY, 
    --Move dat file to...
        MOVE 'OLDLogicalDataName_Data' TO 
    'D:\SQLData\MyDatabaseName_Data.MDF', 
    --move log file to....
        MOVE 'OLDLogicalLogName_Log' 
    TO 'E:\SQLLogs\MyDatabaseName_Log.LDF',
    --Get a message every 10% of the process completed.
        STATS = 10
    GO
    check Books Online for more syntax info
    Where do I write this code and how can I run a scheduled job every night?

    Thanks

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Personally, I prefer it in a stored procedure which I then call from a job. Look up "Jobs [SQL Server Agent]" in books online.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2006
    Posts
    54
    Quote Originally Posted by pootle flump
    Personally, I prefer it in a stored procedure which I then call from a job. Look up "Jobs [SQL Server Agent]" in books online.

    Thanks - I'll have a look.

    I assume I would use the same syntax in the SP?

    The .BAK Filenames are in the format DBNAME_DATEBACKEDUP.BAK

    Any idea's how to get the most recent file in the SP?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Usually you would use a DOS command to get the DIR into a table using xp_cmdshell and interrogate that. Or you could use CLR. Or you could interrogate system tables. Which ever way, you will need to use dynamic SQL which increases complexity.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2006
    Posts
    54
    I set up the T-SQL Statement and ran it manually.

    The query took about 30 seconds to run, but the database is still showing as Restoring in the database list.

    Does it usually take long?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Depends on the size of database and if file is local or across network.

    Did you look at the messages window? The code would tell you progress.

    You will need to refresh SSMS to see the current state of any object.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2006
    Posts
    54
    Quote Originally Posted by pootle flump
    Depends on the size of database and if file is local or across network.

    Did you look at the messages window? The code would tell you progress.

    You will need to refresh SSMS to see the current state of any object.
    3GB & Local

    Didn't include the STATS line....

    Wasn't sure if I should have it for a overnight job.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    3GB is teeny - should not take long assuming there is plenty of room on the disk. You should have left the Stats line in if you want progress

    Did you refresh SSMS?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jan 2006
    Posts
    54
    Quote Originally Posted by pootle flump
    3GB is teeny - should not take long assuming there is plenty of room on the disk. You should have left the Stats line in if you want progress

    Did you refresh SSMS?
    9Gb free on C:\
    240 GB Free on D:\ (Where DB is Stored / Where Share is)

    I know I should have!!

    Hit F5 several times - been restoring for nearly a few hours now.

    Can I stop it?

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by michaelro
    Didn't include the STATS line....
    Actually - could you post THE EXACT code you executed. I have a suspicion you omitted something else....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jan 2006
    Posts
    54
    Code:
    --DROP DATABASE EQUIP
    RESTORE DATABASE EQUIP
    FROM DISK = 'D:\Backup\EQUIP_backup_200902100600.bak'
    WITH NORECOVERY,
    MOVE 'Equip_Data' TO 'D:\MSSQL\EQUIP.MDF',
    MOVE 'Equip_log' TO 'D:\MSSQL\EQUIP_log.LDF'
    --WITH REPLACE
    GO
    I commented out the Drop and Replace lines as I ran the drop line seperately yesterday.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •