[dba-SQLServer] Create DB and Backup DB

Wow! 


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Robert L.
Stewart
Sent: Tuesday, March 25, 2008 5:06 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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 


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com