jwcolby
jwcolby at colbyconsulting.com
Thu Mar 27 15:56:57 CDT 2008
Thanks Robert, I will give it a try. And I do see your comments. When I
looked at your last version my eyes glazed over (as they still do here) so
it may be that there were comments there as well and I was just too glassy
eyed to see them. I can mostly read this stuff if I take the time. I
cannot troubleshoot it however.
Thanks you very much for the code and I will try it out right now to see
where I get. I must say I like the begin / end as well as the error
handling stuff. It kinds of breaks the code up into definable parts.
There are two Drive parameters specified, and I get an immediate error when
I try to parse it.
Msg 134, Level 15, State 1, Procedure pCreate_Load_DB, Line 14
The variable name '@Drive' has already been declared. Variable names must be
unique within a query batch or stored procedure.
Msg 102, Level 15, State 1, Procedure pCreate_Load_DB, Line 32
Incorrect syntax near 'master.mdf'.
Msg 137, Level 15, State 2, Procedure pCreate_Load_DB, Line 113
Must declare the scalar variable "@strDBDir".
Msg 137, Level 15, State 2, Procedure pCreate_Load_DB, Line 115
Must declare the scalar variable "@strDBDir".
Msg 137, Level 15, State 2, Procedure pCreate_Load_DB, Line 117
Must declare the scalar variable "@strDBDir".
I deleted the first one on the idea that the second one was the same thing
that would allow a null. Now when I parse I get:
Msg 102, Level 15, State 1, Procedure pCreate_Load_DB, Line 31
Incorrect syntax near 'master.mdf'.
Msg 137, Level 15, State 2, Procedure pCreate_Load_DB, Line 114
Must declare the scalar variable "@strDBNameTo"
Is there anyway to turn on line numbering so that I can see what line number
means? Is the line number an absolute count down from the Procedure
declaration?
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: Thursday, March 27, 2008 4:27 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com