Microsoft SQL Server, restore a backup of a database with one command
Solution 1
The solution:
Using various resources I came up with the below stored procedure that lets you cut down this restore into one step. I hope it proves as useful to others as it has been to myself.
ALTER PROCEDURE [dbo].[sp_makedev]
@backupfile sysname,
@newdatabase sysname
AS
BEGIN
DECLARE @fname VARCHAR(200)
DECLARE @dirfile VARCHAR(300)
DECLARE @LogicalName NVARCHAR(128)
DECLARE @PhysicalName NVARCHAR(260)
DECLARE @type CHAR(1)
DECLARE @sql NVARCHAR(1000)
DECLARE @mdfFilePath varchar(1000)
DECLARE @ldfFilePath varchar(1000)
CREATE TABLE #dbfiles(
LogicalName NVARCHAR(128)
,PhysicalName NVARCHAR(260)
,Type CHAR(1)
,FileGroupName NVARCHAR(128)
,Size numeric(20,0)
,MaxSize numeric(20,0)
,FileId INT
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0)
,UniqueId uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes INT
,SourceBlockSize INT
,FilegroupId INT
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly INT
,IsPresent INT
)
set @mdfFilePath = ''c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data''
set @ldfFilePath = ''c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data''
set @sql = ''RESTORE DATABASE '' + @newdatabase + '' FROM DISK = '''''' + @backupfile + '''''' WITH MOVE ''
DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, [type] FROM #dbfiles
INSERT #dbfiles
EXEC(''RESTORE FILELISTONLY FROM DISK = '''''' + @backupfile + '''''''')
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
WHILE @@FETCH_STATUS = 0
BEGIN
IF @type = ''D''
SET @sql = @sql + '''''''' + @LogicalName + '''''' TO '''''' + @mdfFilePath + ''\'' + @newdatabase + ''.mdf'''', MOVE ''
ELSE IF @type = ''L''
SET @sql = @sql + '''''''' + @LogicalName + '''''' TO '''''' + @ldfFilePath + ''\'' + @newdatabase + ''.ldf''''''
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
END
CLOSE dbfiles
DEALLOCATE dbfiles
EXEC(@SQL)
END
I'm sure a few things about this query can be improved, however I already wasted enough time just trying to come to this solution. Regardless I'd love to hear some feedback. I hope that others find this useful!
Solution 2
Here is a SQL script that will restore a database with no interaction required. Just enter your "Source Database" & your "Destination Database" - the script will do the rest :)
SET NOCOUNT ON;
DECLARE
@MySourceDatabase NVarchar(1000),
@MyDestinationDatabase NVarchar(100),
@DeviceFrom NVarchar(1000),
@DeviceTo NVarchar(1000),
@LogicalName NVarchar(1000),
@PhysicalName NVarchar(1000),
@SQL NVarchar(MAX),
@RowsToProcess integer,
@CurrentRow integer,
@Comma NVarchar(25);
--SOURCE DATABASE (DATABASE TO RESTORE)
SET @MySourceDatabase = 'D:\Backups\backup_db.bak';
--DESTINATION DATABASE (DATABASE TO RESTORE TO)
SET @MyDestinationDatabase = 'mydatabase_db';
SELECT @DeviceFrom = SUBSTRING(physical_name, 1,
CHARINDEX(@MyDestinationDatabase + '.mdf',
physical_name) - 1)
FROM master.sys.master_files
WHERE name = @MyDestinationDatabase AND FILE_ID = 1;
SET @SQL = 'RESTORE DATABASE ' + @MyDestinationDatabase + ' FROM DISK = ''' + @MySourceDatabase + ''' WITH ';
SET @CurrentRow = 0;
SET @Comma = ',';
DECLARE @FileList TABLE (
RowID int not null primary key identity(1,1)
,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)
,UniqueId uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes BIGINT
,SourceBlockSize BIGINT
,FilegroupId BIGINT
,LogGroupGUID uniqueidentifier
,DifferentialBaseLSN numeric(25)
,DifferentialBaseGUID uniqueidentifier
,IsReadOnly BIGINT
,IsPresent BIGINT
,TDEThumbprint VARBINARY(32) -- Remove this line for SQL Server 2005
);
INSERT INTO @FileList
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @MySourceDatabase + '''')
SET @RowsToProcess = @@RowCount;
WHILE @CurrentRow < @RowsToProcess
BEGIN
SET @CurrentRow= @CurrentRow + 1;
BEGIN
IF @CurrentRow = @RowsToProcess
SET @Comma = ',REPLACE';
END
SELECT @LogicalName = LogicalName,@PhysicalName = PhysicalName FROM @FileList WHERE RowID=@CurrentRow;
SET @PhysicalName = Replace(@PhysicalName,@LogicalName,@MyDestinationDatabase);
SET @SQL = @SQL + 'MOVE ''' + @LogicalName + ''' TO ''' + @PhysicalName + '''' + @Comma + '';
END
--PREVIEW THE GENERATED QUERY
SELECT @SQL;
--EXECUTE THE GENERATED QUERY
--EXEC(@SQL);
It will automatically produce a query like:
RESTORE DATABASE mydatabase_db
FROM DISK = 'D:\Backups\backup_db.bak'
WITH
MOVE 'backup_db'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mydatabase_db.mdf',
MOVE 'backup_db_log'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\mydatabase_db.LDF',
REPLACE
Comments
-
danieljimenez about 2 years
When we copy a database down from production, we make a backup of the database, zip it up and copy the backup down. Then we have to restore using the SQL Server GUI, which involves navigating through several menus and windows. As far as I know, you can not do this with SQL Server's built in stored procedures because you may not know the
logical filename
of the database (which is required to restore). So doing this via query consists of the following:RESTORE FILELISTONLY FROM DISK = 'C:\backup_of_production_database.bak' GO
The above provides the logical file names from the backup file, you then have to use these logical names in the next query...
RESTORE DATABASE NewDevelopmentDatabase FROM DISK = 'C:\backup_of_production_database.bak' WITH MOVE 'YourMDFLogicalName' TO 'C:\mssql\data\DataYourMDFFile.mdf', MOVE 'YourLDFLogicalName' TO 'C:\mssql\data\DataYourLDFFile.mdf'
As you can see this seems inefficient because you must manually enter the logical file names into the next query.
You can find my solution to this problem as an answer below.