Home Forums SQL Server 2005 Backups Backup and Restore of Entire SQL Server 2005 Instance RE: Backup and Restore of Entire SQL Server 2005 Instance

  • A) Shutdown all SQL services

    B) Backup files and/or system

    C) Export/backup associated registry items or the entire registry

    D) Startup all SQL services

    If you shutdown all SQL services and perform a full Windows backup including the system state then you can restore the entire system with the sql installation configuration.

    If you shutdown all SQL services, copy all of the data files, (mdf, ndf and ldf), then you can restore to anywhere. If the instance is the same name then copy the data files to the new location and start the SQL services. If the instance is another name you will have to perform a few more tsql steps to synchronize the system to the data file name(s).

    I have a vbs script that generates an xml document which includes the original path and file names for each database, then performs a sql shutdown. Next the document is used to copy the files including the mssql system database files to a backup location. Finally a sql startup is performed.

    The vbs restore routine performs a sql shutdown, then reads the document and restores the files to their original path. Finally a sql startup is performed.

    [font="Arial"]Clifton G. Collins III[/font]