Continuing on my SQL search script posted yesterday, I am posting two stored procedures for backing up and restoring Microsoft SQL databases easily.
I use the backup script frequently when publishing or making large changes to databases. Obviously, this script should really be used in addition to a scheduled maintenance and backup plan and is not intended to replace that.
Another thing to keep in mind is that these two procedures just abstract calling the built in backup and restore commands. I think this syntax is easier to remember and the hard coded options are right for what I tend to do. Feel free to tweak them as needed. I tend to create these procedures in my master database.
Here’s the SQL for BackupDatabase.
CREATE PROCEDURE BackupDatabase @database varchar(100), @backupDir varchar(500) AS IF RIGHT(@backupDir, 1) = '' SET @backupDir = @backupDir + @database + '.bak' ELSE SET @backupDir = @backupDir + '' + @database + '.bak' BACKUP DATABASE @database TO DISK = @backupDir WITH STATS = 10
The BackupDatabase procedure accepts to parameters, the name of the database to backup and the path to the directory where the backup file will be written. The following is an example of how to call the procedure.
master..BackupDatabase 'Northwind', 'd:temp'
This example will backup the Northwind database to the d:temp directory, creating a file named Northwind.bak. One thing I might suggest doing is altering the procedure to append the current date to the end of the file name.
The RestoreDatabase procedure depends on a helper procedure which returns the files which are contained within a backup file. You can call this procedure directly, but there’s really not much point to this. Execute the following SQL to create the procedure.
CREATE PROCEDURE GetBackupFileList @backupFile varchar(500) AS RESTORE FILELISTONLY FROM DISK = @backupFile
Once you have created the GetBackupFileList procedure the RestoreDatabase procedure can be created by executing this SQL code:
CREATE PROCEDURE RestoreDatabase @backupFile varchar(500),
@newDatabaseName varchar(100)
AS
-- // create a temp table to hold the file list for the db
CREATE TABLE #fileList (
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0)
)
-- // Declare a variable to hold the restore statement
DECLARE @RestoreStmt varchar(500)
-- // start the restore stmnt
SET @RestoreStmt = '
RESTORE DATABASE ' + @newDatabaseName + '
FROM DISK = '' + @backupFile + ''
WITH
'
INSERT #fileList
EXECUTE GetBackupFileList @backupFile
--- // declare some vars to hold the filenames and types
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
DECLARE @Type char(1)
DECLARE @LastSlash int
DECLARE fileListCsr CURSOR FOR
SELECT LogicalName, PhysicalName, Type
FROM #fileList
OPEN fileListCsr
FETCH NEXT FROM fileListCsr INTO @LogicalName, @PhysicalName, @Type
WHILE @@FETCH_STATUS = 0
BEGIN
-- // get the path from @PhysicalName
SET @LastSlash = CHARINDEX('', REVERSE(@PhysicalName))
SET @PhysicalName = LEFT(@PhysicalName, LEN(@PhysicalName) - @LastSlash) + ''
-- // check the type of this file
IF @Type = 'D'
SET @PhysicalName = @PhysicalName + @newDatabaseName + '_data.mdf'
ELSE
SET @PhysicalName = @PhysicalName + @newDatabaseName + '_log.ldf'
-- // update the file paths
SET @RestoreStmt = @RestoreStmt + '
MOVE '' + @LogicalName + '' TO '' + @PhysicalName + '',
'
FETCH NEXT FROM fileListCsr INTO @LogicalName, @PhysicalName, @Type
END
-- // add stats
SET @RestoreStmt = @RestoreStmt + ' STATS = 10'
PRINT @RestoreStmt
CLOSE fileListCsr
DROP TABLE #fileList
-- // execute the restore stmnt
EXEC( @RestoreStmt )
This RestoreDatabase procedure accepts two parameters, the path to the backup file and the name of the database to restore. The following is an example which restores the backup file we created earlier to a database named NewNorthwind.
master..RestoreDatabase 'd:tempNorthwind.bak', 'NewNorthwind'
I’ve found these two procedures to be very useful over the years, I hope you do too.
Please let me know if you have any feedback.
DougHughes.net 
Comments on: "MS SQL Backup and Restore Procedures" (14)
I’m in a corporate setting where I don’t have backup/restore rights on my database (where I do have create table, etc.). Will this script bypass the SA requirements and let me backup?
LikeLike
I would expect not. One thing you could try is just running the following command in QA. If it so, at least the backup procedure will work.
BACKUP DATABASE ‘Northwind’
TO DISK = ‘d:tempnorthwind.bak’
WITH STATS = 10
Why not ask for permissions? It seems important to me.
LikeLike
GetBackupFileList procedure is missing could u please comment on this
LikeLike
GetBackupFileList procedure is missing could u please comment on this
LikeLike
Get following error when running restore (sql 2005). Any ideas?
Msg 213, Level 16, State 7, Procedure GetBackupFileList, Line 5
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Procedure GetBackupFileList, Line 5
RESTORE FILELIST is terminating abnormally.
LikeLike
If you have a web app where you need to create new databases from a template database “on the fly”, this would be a great way, right?
Just make the backup .bak file once, then in the code behind call just the restore Stored Proc and pass it the desired new db name.
let me know if this isn’t the best solution, cheers!
LikeLike
Aron – I’m not sure I’d do it this way or not. I’d probably look at scripting the database first. On the other hand, simply having a backup does seem to be somewhat easier to mantain.
LikeLike
APatel, I’m receiving the same error. I tried adding “WITH REPLACE” to the code, but still seem to get the same error. Have you found a solution to the problem?
LikeLike
alter PROCEDURE sp_RestoreDB
@backupFile varchar(500),
@newDatabaseName varchar(100)
AS
— // create a temp table to hold the file list for the db
CREATE TABLE #fileList ( LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0) NULL,
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0) NULL,
ReadWriteLSN numeric(25,0) NULL,
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit )
— // Declare a variable to hold the restore statement
DECLARE @RestoreStmt varchar(500)
— // start the restore stmnt
SET @RestoreStmt = ‘
RESTORE DATABASE ‘ + @newDatabaseName + ‘
FROM DISK = ” + @backupFile + ”
WITH
‘
INSERT #fileList
EXECUTE GetBackupFileList @backupFile
— // declare some vars to hold the filenames and types
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
DECLARE @Type char(1)
DECLARE @LastSlash int
DECLARE fileListCsr CURSOR FOR
SELECT LogicalName, PhysicalName, Type
FROM #fileList
OPEN fileListCsr
FETCH NEXT FROM fileListCsr INTO @LogicalName, @PhysicalName, @Type
WHILE @@FETCH_STATUS = 0
BEGIN
— // get the path from @PhysicalName
SET @LastSlash = CHARINDEX(”, REVERSE(@PhysicalName))
SET @PhysicalName = LEFT(@PhysicalName, LEN(@PhysicalName) – @LastSlash) + ”
— // check the type of this file
IF @Type = ‘D’
SET @PhysicalName = @PhysicalName + @newDatabaseName + ‘_data.mdf’
ELSE
SET @PhysicalName = @PhysicalName + @newDatabaseName + ‘_log.ldf’
— // update the file paths
SET @RestoreStmt = @RestoreStmt + ‘
MOVE ” + @LogicalName + ” TO ” + @PhysicalName + ”,
‘
FETCH NEXT FROM fileListCsr INTO @LogicalName, @PhysicalName, @Type
END
— // add stats
SET @RestoreStmt = @RestoreStmt + ‘ STATS = 10’
PRINT @RestoreStmt
CLOSE fileListCsr
DROP TABLE #fileList
LikeLike
Your codes is so cool
its awsome bro
your codes is best I sureness used those kind of codes…
yahooooooOOOOoooOOOoo!!!!!
thanks
LikeLike
Can’t see, to get it to work …
Had to change this…
CREATE TABLE #fileList ( LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
Field numeric(20,0),
–FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0) NULL,
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0) NULL,
ReadWriteLSN numeric(25,0) NULL,
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit,
TDEThumbprint varchar(MAX) )
Still nothing worked ???
Does the database need to exists where it restores it to ?
I’m lost…
LikeLike
In answer to Ryan’s question no a stored procedure would not allow you to bypass the security rights you require to backup or restore a database in SQL 2000.
However in SQL 2005 a new piece of functionality wass introduced where you can let code run under the permisions of another user. This utilises the “EXECUTE AS” clause. If this were added to the start of the procedures under the pretext of running under a user with those specified rights (db_BackUPOperator) you would be able to perform the task without having the explicit permissions. You would need execute permissions on the stored procedures of course.
Rich
LikeLike
Hi,
I created these (backup, restore) stored procs and backup is working fine but restore is failed with next error message:
Server: Msg 3201, Level 16, State 2, Line 2
Cannot open backup device ‘e:dataMSSQLBACKUP + @backupFile + ‘. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
The command was:
EXEC RestoreDatabase ‘E:PharmaTrendPHARMANOVA_200911121128.bak’, ‘PHARMANOVA’
So I had backup my DB E:PharmaTrend folder and restore procedure tried to restore it from default MsSQL backup folder!!! Why?
Thx in advance for your help!
Wic
LikeLike
I resolved the issue…..
This RestoreProc will overwrite the db if it exists….
will restore the first file if the bak file contains more than one backup
ALTER PROCEDURE RestoreDatabase
@backupFile varchar(500),
@newDatabaseName varchar(100)
AS
— // create a temp table to hold the file list for the db
CREATE TABLE #fileList ( LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0) NULL,
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0) NULL,
ReadWriteLSN numeric(25,0) NULL,
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit )
— // Declare a variable to hold the restore statement
DECLARE @RestoreStmt varchar(500)
— // start the restore stmnt
SET @RestoreStmt = ‘
RESTORE DATABASE ‘ + @newDatabaseName + ‘
FROM DISK = ”’ + @backupFile + ”’
WITH FILE = 1,
‘
INSERT #fileList
EXECUTE GetBackupFileList @backupFile
— // declare some vars to hold the filenames and types
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
DECLARE @Type char(1)
DECLARE @LastSlash int
DECLARE fileListCsr CURSOR FOR
SELECT LogicalName, PhysicalName, Type
FROM #fileList
OPEN fileListCsr
FETCH NEXT FROM fileListCsr INTO @LogicalName, @PhysicalName, @Type
WHILE @@FETCH_STATUS = 0
BEGIN
— // get the path from @PhysicalName
SET @LastSlash = CHARINDEX(”, REVERSE(@PhysicalName))
SET @PhysicalName = LEFT(@PhysicalName, LEN(@PhysicalName) – @LastSlash) + ”
— // check the type of this file
IF @Type = ‘D’
SET @PhysicalName = @PhysicalName + @newDatabaseName + ‘_data.mdf’
ELSE
SET @PhysicalName = @PhysicalName + @newDatabaseName + ‘_log.ldf’
— // update the file paths
SET @RestoreStmt = @RestoreStmt + ‘
MOVE ”’ + @LogicalName + ”’ TO ”’ + @PhysicalName + ”’,
‘
FETCH NEXT FROM fileListCsr INTO @LogicalName, @PhysicalName, @Type
END
— // add stats
SET @RestoreStmt = @RestoreStmt + ‘ STATS = 10,REPLACE’
PRINT @RestoreStmt
CLOSE fileListCsr
DROP TABLE #fileList
— // execute the restore stmnt
EXEC( @RestoreStmt )
Shemeer NS
Technology Specialist
Mumbai
LikeLike