[dba-SQLServer] Create DB and Backup DB

John,

Below is the code to create a DB and Backup a DB.
I will let you do the research and get the SQL to do the restore.

It is a bit more than your requested single line though.

What you want to do is restore the backup you just created to
the DB you created.

Robert

P.S. While you do need to look for line wraps, it is not a problem
like VBA.


CREATE PROCEDURE dbo. pCreate_Load_DB
     @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
AS
     BEGIN
         SET NOCOUNT ON ;

         DECLARE @Sql nvarchar ( max ),
             @ErrorMessage varchar ( 500 )

         SET @ErrorMessage = 'No Error'


         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
         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 )'
         EXEC sp_executesql @Sql

         SET @Drive = 'F'
         SET @InstanceName = CAST ( ISNULL ( SERVERPROPERTY ( 
'InstanceName' ), SERVERPROPERTY ( 'ServerName' )) AS sysname )
         SET @BackupLocation = @Drive + ':\SQL_Backups\' + @InstanceName + '\'

         BEGIN TRY
             SET @SQL = 'BACKUP DATABASE ' + @DBNameBackup + ' TO 
DISK=' + CHAR ( 39 ) + @BackupLocation + @DBNameBackup + 
'_BACKUP.BAK' + CHAR ( 39 ) + ' WITH INIT'
             EXEC sp_ExecuteSql @SQL

             -- truncate the log files
             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


     END