[dba-SQLServer] Create, Backup, and Restore to new location

All of the following has been tested and works in our production environment
except the restore portion.

What was missing from your code you last posted, John, was the move syntax.

Maybe there are enough comments in it this time and it is definitely not
doctoral thesis. I am on the list to learn from the likes of Paul.

Robert


CREATE PROCEDURE dbo. pCreate_Load_DB
     -- initialize all of the variables that need to be passed in
     @DBNameCreate VARCHAR ( 64 ),
     @DBNameBackup VARCHAR ( 64 ),
     @data_path NVARCHAR ( 256 ) = NULL,
     @BackupLocation VARCHAR ( 500 ) = NULL,
     @Drive CHAR ( 1 ),
     @InstanceName SYSNAME = NULL,
     @Drive VARCHAR ( 1 ) = NULL,
     @strDBDir varchar ( 200 )
AS
     BEGIN
         -- set the record count to off
         SET NOCOUNT ON ;

         -- setup some local variables
         DECLARE @Sql NVARCHAR ( MAX ),
             @ErrorMessage VARCHAR ( 500 )

         -- set the default value of the error message
         SET @ErrorMessage = 'No Error'

         -- if the path is not passed in, get the path used by the 
database as the default
         IF @data_path IS NULL
             BEGIN
                 SET @data_path = ( SELECT    SUBSTRING ( 
physical_name , 1, CHARINDEX ( N 'master.mdf' , LOWER ( physical_name )) - 1)
                                    FROM      master. sys.master_files
                                    WHERE     database_id = 1
                                             AND file_id = 1
                                  ) ;
             END

         -- execute the CREATE DATABASE statement
         -- build a string to hold the create dastabase statement
         SET @Sql = 'CREATE DATABASE Sales
             ON
             ( NAME = ' + @DBNameCreate + '_dat,
                 FILENAME = ' + CHAR ( 39 ) + @data_path + 
@DBNameCreate + 'dat.mdf' + CHAR ( 39 ) + ',
                 SIZE = 10,
                 FILEGROWTH = 5 )
             LOG ON
             ( NAME = ' + @DBNameCreate + '_log,
                 FILENAME = ' + CHAR ( 39 ) + @data_path + 
@DBNameCreate + 'log.ldf' + CHAR ( 39 ) + ',
                 SIZE = 5MB )'
         -- execute the sql statement you created above
         EXEC sp_executesql @Sql

         -- check to see if the drive letter was passed in
         IF @Drive IS NULL
             SET @Drive = 'F'

         -- check to see if the sql server instance name was passed in
         IF @InstanceName IS NULL
             SET @InstanceName = CAST ( ISNULL ( SERVERPROPERTY ( 
'InstanceName' ), SERVERPROPERTY ( 'ServerName' )) AS SYSNAME )

         -- check to see if the location for the backup was passed in
         if @BackupLocation is null
             SET @BackupLocation = @Drive + ':\SQL_Backups\' + 
@InstanceName + '\'

         -- wrap backing up the database into a TRY/Catch
         BEGIN TRY
             -- Create the string to do the backup
             -- use 'WITH INIT' to make sure only a single backup is 
stored in the backup file
             SET @SQL = 'BACKUP DATABASE ' + @DBNameBackup + ' TO 
DISK=' + CHAR ( 39 ) + @BackupLocation + @DBNameBackup + 
'_BACKUP.BAK' + CHAR ( 39 ) + ' WITH INIT'

             -- execute the SQL string created above
             EXEC sp_ExecuteSql @SQL

             -- truncate the log files
             -- the following code to the first END TRY can be left out
             -- it is used to truncate the logs on the database
             -- after a backup
             BEGIN
                 SET @Sql = 'ALTER DATABASE ' + @DBNameBackup + ' SET 
RECOVERY SIMPLE'
                 EXEC sp_executesql @Sql
             END
             BEGIN
                 SET @Sql = 'EXEC sp_dboption ' + CHAR ( 39 ) + 
@DBNameBackup + CHAR ( 39 ) + ', ' + CHAR ( 39 ) + 'trunc. log on 
chkpt.' + CHAR ( 39 ) + ', ' + CHAR ( 39 ) + 'TRUE' + CHAR ( 39 )
                 EXEC sp_executesql @Sql
             END
             BEGIN
                 CHECKPOINT
             END
             BEGIN
                 SET @Sql = 'DBCC SHRINKFILE (' + @DBNameBackup + '_Log,1)'
                 EXEC sp_executesql @Sql
             END
             BEGIN
                 SET @Sql = 'EXEC sp_dboption ' + CHAR ( 39 ) + 
@DBNameBackup + CHAR ( 39 ) + ', ' + CHAR ( 39 ) + 'trunc. log on 
chkpt.' + CHAR ( 39 ) + ', ' + CHAR ( 39 ) + 'FALSE' + CHAR ( 39 )
                 EXEC sp_executesql @Sql
             END
             BEGIN
                 SET @Sql = 'ALTER DATABASE ' + @DBNameBackup + ' SET 
RECOVERY FULL'
                 EXEC sp_executesql @Sql
             END
         END TRY
         BEGIN CATCH
             SELECT   @ErrorMessage = ERROR_MESSAGE () ;
         END CATCH

         -- now we can 'MOVE' the database to another location and 
restore it into the
         -- database we created in the beginning of this.
         BEGIN TRY
             -- build the restore database command with the MOVE 
function specified
             SET @Sql = 'RESTORE DATABASE ' + char ( 39 ) + 
@DBNameCreate + char ( 39 )
             SET @Sql = @Sql + ' FROM DISK = ' + CHAR ( 39 ) + 
@strDBDir + @DBNameBackup + '.bak' + CHAR ( 39 )
             SET @Sql = @Sql + ' WITH MOVE ' + char ( 13 ) + 
@DBNameBackup + '_Data' + CHAR ( 39 )
             SET @Sql = @Sql + ' TO ' + CHAR ( 39 ) + @strDBDir + 
@strDBNameTo + '.mdf' + ChaR ( 39 ),
             SET @Sql = @Sql + ' MOVE ' + char ( 39 ) + @DBNameBackup 
+ '_Log' + char ( 39 )
             SET @Sql = @Sql + ' TO ' + CHAR ( 39 ) + @strDBDir + 
@DBNameCreate + '.ldf' + CHAR ( 39 )

             -- Execute the sql statement created above
                 EXEC sp_executesql @Sql
         END TRY
         BEGIN CATCH
             SELECT   @ErrorMessage = ERROR_MESSAGE () ;
         END CATCH
     END