Advanced Search

Return to Snippet

Revision: 50864
at September 7, 2011 21:17 by plugables
Updated Code 
DECLARE @BAK_FILE_PATH varchar(300)
DECLARE @DB_TO_RESTORE_TO varchar(300)
DECLARE @SQL_SERVER_DATA_FOLDER varchar(300)

/* [BAK_FILE_PATH] : Path of backup file. e.g; D:\DatabaseBackup\MyOldDB.bak */
SET @BAK_FILE_PATH = 'D:\DatabaseBackup\MyOldDB.bak'

/* [DB_TO_RESTORE_TO] : Database to restore to. Should already exist. */
SET @DB_TO_RESTORE_TO = 'MyNewDB'

/* [SQL_SERVER_DATA_FOLDER] : Sql server data folder. Usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data */
SET @SQL_SERVER_DATA_FOLDER = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'


/*
  Nothing to edit below this line
*/

DECLARE @LOGICAL_NAME_Data varchar(300)
DECLARE @LOGICAL_NAME_Log varchar(300)
DECLARE @query varchar(3000)

/* Step 1 : Get the logical file names */
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''')

CREATE TABLE #restoretemp
(
LogicalName nvarchar(128)
,Old_PhysicalName nvarchar(128)
,[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)
,ReadWriteLSN numeric(25,0)
,BackupSizeInByte bigint
,SourceBlockSize int
,FilegroupID int
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN numeric(25,0)
,DifferentialbaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
)

INSERT #restoretemp EXEC (@query)

SELECT @LOGICAL_NAME_Data = LogicalName FROM #restoretemp WHERE [type] = 'D'
SELECT @LOGICAL_NAME_Log = LogicalName FROM #restoretemp WHERE [type] = 'L'

PRINT @LOGICAL_NAME_Data
PRINT @LOGICAL_NAME_Log

TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp

/* Step 2 : Restore the database */
SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXEC (@query)

SET @query = 'RESTORE DATABASE ' + @DB_TO_RESTORE_TO 
+ ' FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''') 
+ ' WITH REPLACE, ' 
+ ' MOVE ' + QUOTENAME(@LOGICAL_NAME_Data, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'.mdf') , '''')
+ ', MOVE ' + QUOTENAME(@LOGICAL_NAME_Log, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'_log.ldf'), '''') 
EXEC (@query)

SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET MULTI_USER'
EXEC (@query)

Revision: 50863
at September 7, 2011 21:15 by plugables
Updated Code 
DECLARE @BAK_FILE_PATH varchar(300)
DECLARE @DB_TO_RESTORE_TO varchar(300)
DECLARE @SQL_SERVER_DATA_FOLDER varchar(300)
DECLARE @LOGICAL_NAME_Data varchar(300)
DECLARE @LOGICAL_NAME_Log varchar(300)
DECLARE @query varchar(3000)


/* [BAK_FILE_PATH] : Path of backup file. e.g; D:\DatabaseBackup\MyOldDB.bak */
SET @BAK_FILE_PATH = 'D:\DatabaseBackup\MyOldDB.bak'

/* [DB_TO_RESTORE_TO] : Database to restore to. Should already exist. */
SET @DB_TO_RESTORE_TO = 'MyNewDB'

/* [SQL_SERVER_DATA_FOLDER] : Sql server data folder. Usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data */
SET @SQL_SERVER_DATA_FOLDER = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'


/*
  Nothing to edit below this line
*/

/* Step 1 : Get the logical file names */
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''')

CREATE TABLE #restoretemp
(
LogicalName nvarchar(128)
,Old_PhysicalName nvarchar(128)
,[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)
,ReadWriteLSN numeric(25,0)
,BackupSizeInByte bigint
,SourceBlockSize int
,FilegroupID int
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN numeric(25,0)
,DifferentialbaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
)

INSERT #restoretemp EXEC (@query)

SELECT @LOGICAL_NAME_Data = LogicalName FROM #restoretemp WHERE [type] = 'D'
SELECT @LOGICAL_NAME_Log = LogicalName FROM #restoretemp WHERE [type] = 'L'

PRINT @LOGICAL_NAME_Data
PRINT @LOGICAL_NAME_Log

TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp

/* Step 2 : Restore the database */
SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXEC (@query)

SET @query = 'RESTORE DATABASE ' + @DB_TO_RESTORE_TO 
+ ' FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''') 
+ ' WITH REPLACE, ' 
+ ' MOVE ' + QUOTENAME(@LOGICAL_NAME_Data, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'.mdf') , '''')
+ ', MOVE ' + QUOTENAME(@LOGICAL_NAME_Log, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'_log.ldf'), '''') 
EXEC (@query)

SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET MULTI_USER'
EXEC (@query)

Revision: 50862
at September 7, 2011 21:14 by plugables
Updated Code 
/*
[BAK_FILE_PATH] : Path of backup file. e.g; D:\DatabaseBackup\MyDB.bak
[DB_TO_RESTORE_TO] : Database to restore to. Should already exist.
[SQL_SERVER_DATA_FOLDER] : Sql server data folder. Usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
*/

DECLARE @BAK_FILE_PATH varchar(300)
DECLARE @DB_TO_RESTORE_TO varchar(300)
DECLARE @SQL_SERVER_DATA_FOLDER varchar(300)
DECLARE @LOGICAL_NAME_Data varchar(300)
DECLARE @LOGICAL_NAME_Log varchar(300)
DECLARE @query varchar(3000)

/* [BAK_FILE_PATH] : Path of backup file. e.g; D:\DatabaseBackup\MyOldDB.bak */
SET @BAK_FILE_PATH = 'D:\DatabaseBackup\MyOldDB.bak'

/* [DB_TO_RESTORE_TO] : Database to restore to. Should already exist. */
SET @DB_TO_RESTORE_TO = 'MyNewDB'

/* [SQL_SERVER_DATA_FOLDER] : Sql server data folder. Usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data */
SET @SQL_SERVER_DATA_FOLDER = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'


/*
  Nothing to edit below this line
*/

/* Step 1 : Get the logical file names */
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''')

CREATE TABLE #restoretemp
(
LogicalName nvarchar(128)
,Old_PhysicalName nvarchar(128)
,[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)
,ReadWriteLSN numeric(25,0)
,BackupSizeInByte bigint
,SourceBlockSize int
,FilegroupID int
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN numeric(25,0)
,DifferentialbaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
)

INSERT #restoretemp EXEC (@query)

SELECT @LOGICAL_NAME_Data = LogicalName FROM #restoretemp WHERE [type] = 'D'
SELECT @LOGICAL_NAME_Log = LogicalName FROM #restoretemp WHERE [type] = 'L'

PRINT @LOGICAL_NAME_Data
PRINT @LOGICAL_NAME_Log

TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp

/* Step 2 : Restore the database */
SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXEC (@query)

SET @query = 'RESTORE DATABASE ' + @DB_TO_RESTORE_TO 
+ ' FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''') 
+ ' WITH REPLACE, ' 
+ ' MOVE ' + QUOTENAME(@LOGICAL_NAME_Data, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'.mdf') , '''')
+ ', MOVE ' + QUOTENAME(@LOGICAL_NAME_Log, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'_log.ldf'), '''') 
EXEC (@query)

SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET MULTI_USER'
EXEC (@query)

Revision: 50861
at September 6, 2011 21:28 by plugables
Initial Code 
/*
[BAK_FILE_PATH] : Path of backup file. e.g; D:\Work\Local Work\Workspace\LocalInstalls\mumba07_store.bak
[DB_TO_RESTORE_TO] : Database to restore to. Should already exist.
[SQL_SERVER_DATA_FOLDER] : Sql server data folder. Usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
*/

DECLARE @BAK_FILE_PATH varchar(300)
DECLARE @DB_TO_RESTORE_TO varchar(300)
DECLARE @SQL_SERVER_DATA_FOLDER varchar(300)
DECLARE @LOGICAL_NAME_Data varchar(300)
DECLARE @LOGICAL_NAME_Log varchar(300)
DECLARE @query varchar(3000)

/* [BAK_FILE_PATH] : Path of backup file. e.g; D:\Work\Local Work\Workspace\LocalInstalls\mumba07_store.bak */
SET @BAK_FILE_PATH = 'd:\temp\backup.dat'

/* [DB_TO_RESTORE_TO] : Database to restore to. Should already exist. */
SET @DB_TO_RESTORE_TO = 'backdesigns'

/* [SQL_SERVER_DATA_FOLDER] : Sql server data folder. Usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data */
SET @SQL_SERVER_DATA_FOLDER = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data'

/*
  Nothing to edit below this
*/

/* Step 1 : Get the logical file names */
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''')

CREATE TABLE #restoretemp
(
LogicalName nvarchar(128)
,Old_PhysicalName nvarchar(128)
,[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)
,ReadWriteLSN numeric(25,0)
,BackupSizeInByte bigint
,SourceBlockSize int
,FilegroupID int
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN numeric(25,0)
,DifferentialbaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
)

INSERT #restoretemp EXEC (@query)

SELECT @LOGICAL_NAME_Data = LogicalName FROM #restoretemp WHERE [type] = 'D'
SELECT @LOGICAL_NAME_Log = LogicalName FROM #restoretemp WHERE [type] = 'L'

PRINT @LOGICAL_NAME_Data
PRINT @LOGICAL_NAME_Log

TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp

/* Step 2 : Restore the database */
SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXEC (@query)

SET @query = 'RESTORE DATABASE ' + @DB_TO_RESTORE_TO 
+ ' FROM DISK = ' + QUOTENAME(@BAK_FILE_PATH , '''') 
+ ' WITH REPLACE, ' 
+ ' MOVE ' + QUOTENAME(@LOGICAL_NAME_Data, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'.mdf') , '''')
+ ', MOVE ' + QUOTENAME(@LOGICAL_NAME_Log, '''') + ' TO ' + QUOTENAME((@SQL_SERVER_DATA_FOLDER+'\'+@DB_TO_RESTORE_TO+'_log.ldf'), '''') 
EXEC (@query)

SET @query = 'ALTER DATABASE ' + @DB_TO_RESTORE_TO + ' SET MULTI_USER'
EXEC (@query)
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Restore MSSQL database from backup (.bak)

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
sql, backup

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL