Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ivikasrana
Starting Member
3 Posts |
 Posted - 2014-07-14 : 09:22:15
|
I'm creating a module for our testing team to copy/database without having access to the complete server. I do not want them to access the sql server or any wizard but to give them a webpage where they can copy live database to a testing database on the same server without bugging up the development team.
We are using SQL 2012 Devloper
Any help would be appreciated...
-----------------------
USE MASTER
GO
DECLARE @Folder VARCHAR(MAX) = 'E:\MSSQLBackup\'
DECLARE @PathFile VARCHAR(MAX) = @Folder + 'SomeDB.bak'
DECLARE @PathDB VARCHAR(MAX) = @Folder + 'SomeDBTest.mdf'
DECLARE @PathLog VARCHAR(MAX) = @Folder + 'SomeDBTest_Log.ldf'
BACKUP DATABASE SomeDB TO DISK = @PathFile WITH FORMAT, COMPRESSION
Alter Database SomeDBTest SET SINGLE_USER With ROLLBACK IMMEDIATE
RESTORE FILELISTONLY FROM DISK = @PathFile --found this online but its useless
RESTORE DATABASE SomeDBTest FROM DISK = @PathFile
WITH NORECOVERY,
MOVE 'SomeDBTest' TO @PathDB,
MOVE 'SomeDBTest_Log' TO @PathLog
ALTER DATABASE SomeDBTest SET MULTI_USER;
------------------------
Processed 257943 pages for database 'SomeDB', file 'SomeDB' on file 1.
Processed 2 pages for database 'SomeDB', file 'SomeDB_log' on file 1.
BACKUP DATABASE successfully processed 257945 pages in 1.255 seconds (1716.994 MB/sec).
(2 row(s) affected)
Msg 3154, Level 16, State 4, Line 13
The backup set holds a backup of a database other than the existing 'SomeDBTest' database.
Msg 3013, Level 16, State 1, Line 13
RESTORE DATABASE is terminating abnormally.
|
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-07-14 : 11:27:58
|
I made some minor changes and tested. I usually kill connections when I restore a database, so I am including a kill spid proc which you can create in master.
CREATE PROC [Dba].[KillConnectionsForDatabase]
@Dbname varchar(50)
AS
BEGIN
DECLARE @SPIDs TABLE(killSpid nvarchar(20))
DECLARE @Rows int
INSERT INTO @SPIDs
SELECT DISTINCT 'KILL ' + CAST(SPID as varchar(5)) FROM Master..sysprocesses where dbid = db_id(@Dbname)
DECLARE SpidKill CURSOR
READ_ONLY
FOR SELECT killSpid FROM @SPIDs
DECLARE @killSpid nvarchar(20)
OPEN SpidKill
FETCH NEXT FROM SpidKill INTO @killSpid
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
EXECUTE Sp_executesql @killSpid
END
FETCH NEXT FROM SpidKill INTO @killSpid
END
CLOSE SpidKill
DEALLOCATE SpidKill
END
DECLARE @Folder VARCHAR(MAX) = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVERLOCAL\MSSQL\'
DECLARE @BackupDb varchar(50) = 'DEV'
DECLARE @LogicalBackupDbDataName varchar(50) = 'DEV'
DECLARE @LogicalBackupDbLofName varchar(50) = 'DEV_Log'
DECLARE @RestoreDB varchar(50) = 'SomeDB'
DECLARE @PathFile VARCHAR(MAX) = @Folder + 'Backup\' + 'DEV.bak'
DECLARE @PathDB VARCHAR(MAX) = @Folder + 'Data\' + 'SomeDBTest.mdf'
DECLARE @PathLog VARCHAR(MAX) = @Folder + 'Data\' + 'SomeDBTest_Log.ldf'
BACKUP DATABASE Dev TO DISK = @PathFile WITH INIT,COMPRESSION
EXEC [Dba].[KillConnectionsForDatabase] 'SomeDB'
RESTORE DATABASE @RestoreDB FROM DISK = @PathFile
WITH
MOVE @LogicalBackupDbDataName TO @PathDB,
MOVE @LogicalBackupDbLofName TO @PathLog
,REPLACE,RECOVERY |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-14 : 12:11:19
|
You just need the REPLACE option added to your WITH.
Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/ |
|
|
|
ivikasrana
Starting Member
3 Posts |
Posted - 2014-07-14 : 13:24:13
|
quote:
Originally posted by tkizer
You just need the REPLACE option added to your WITH.
Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
See, let me clear here.. I want to replace SomeDBTest files not SomeDB files, I am taking backup of SombDB and want to restore it in SomeDBTest, why would I take backup of SomeDB and restore it back since I wanted to restore it into a new test database.
Is there any possible way to copy SomeDB to SomeDBTest ?
Here is my Code after using WITH REPLACE:
RESTORE DATABASE SomeDBTest FROM DISK = 'E:\MSSQLBackup\SomeDB.bak'
WITH RECOVERY, REPLACE,
MOVE 'SomeDBTest' TO 'E:\MSSQLBackup\SomeDBTest.mdf',
MOVE 'SomeDBTest_Log' TO 'E:\MSSQLBackup\SomeDBTest_log.ldf'
------------------------------
Msg 3234, Level 16, State 2, Line 2
Logical file 'SomeDBTest' is not part of database 'SomeDBTest'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-14 : 13:28:22
|
Your MOVE is incorrect. The logical files need to reflect SomeDB and not SomeDBTest. You can run RESTORE FILELISTONLY to verify which logical files to use or check the source database's properties.
RESTORE DATABASE SomeDBTest FROM DISK = 'E:\MSSQLBackup\SomeDB.bak'
WITH RECOVERY, REPLACE,
MOVE 'SomeDB' TO 'E:\MSSQLBackup\SomeDBTest.mdf',
MOVE 'SomeDB_Log' TO 'E:\MSSQLBackup\SomeDBTest_log.ldf'
Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/ |
|
|
|
ivikasrana
Starting Member
3 Posts |
Posted - 2014-07-14 : 13:37:23
|
quote:
Originally posted by tkizer
Your MOVE is incorrect. The logical files need to reflect SomeDB and not SomeDBTest. You can run RESTORE FILELISTONLY to verify which logical files to use or check the source database's properties.
RESTORE DATABASE SomeDBTest FROM DISK = 'E:\MSSQLBackup\SomeDB.bak'
WITH RECOVERY, REPLACE,
MOVE 'SomeDB' TO 'E:\MSSQLBackup\SomeDBTest.mdf',
MOVE 'SomeDB_Log' TO 'E:\MSSQLBackup\SomeDBTest_log.ldf'
Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
thanks, its working |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Msg 3154 The backup set holds a backup of a databa