May 18, 2015 at 2:09 pm
RESTORE DATABASE BCCrestore
FROM DISK = 'D:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BCC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BCC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf'
WITH REPLACE;
GO
Msg 319, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
May 18, 2015 at 2:09 pm
David Webb-CDS (5/18/2015)
OK, let's try this:RESTORE DATABASE BCCrestore
FROM DISK = 'D:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BCC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BCC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf'
WITH REPLACE;
GO
Not quite. This:
RESTORE DATABASE BCCrestore
FROM DISK = 'C:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BBC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BBC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf',
REPLACE,
STATS = 10;
GO
May 18, 2015 at 2:13 pm
Yep, Lynn has it correct. Sorry.
May 18, 2015 at 2:13 pm
briancampbellmcad (5/18/2015)
This is not something I will be doing on a regular basis as I am just filling in for a DBA on sick-leave. I just need to get irate users seeing the correct data sometime tomorrow. Is the below what you mean wth 'replace'?:RESTORE DATABASE BCCrestore
FROM DISK = 'D:\BCC_DB_backup_201505020017.bak'
WITH REPLACE 'BCC' TO 'D:\Databases\Data\BCCrestore.mdf',
REPLACE 'BCC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf';
GO
Msg 155, Level 15, State 3, Line 3
'REPLACE' is not a recognized RESTORE option.
Those commands are safe to run as they really won't restore the database. they are only providing information from the backup file.
HOWEVER, before you go any further do this:
BACKUP DATABASE BCC_DB
To DISK = 'D:\BCC_DB_justincase.bak'
GO
That way if you do make a mistake and restore over the existing database, you can recover it up to when you made the backup.
-SQLBill
May 18, 2015 at 2:15 pm
RESTORE DATABASE BCCrestore
FROM DISK = 'C:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BBC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BBC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf',
REPLACE,
STATS = 10;
GO
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\BCC_DB_backup_201505020017.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
May 18, 2015 at 2:18 pm
briancampbellmcad (5/18/2015)
RESTORE DATABASE BCCrestoreFROM DISK = 'C:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BBC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BBC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf',
REPLACE,
STATS = 10;
GO
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\BCC_DB_backup_201505020017.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
You realize that you are just copying/pasting without even considering anything else you may have done so far.
Look at your second to last attempt to restore the database, your path to the backup file was on the D: drive not the C: drive.
May 18, 2015 at 2:19 pm
RESTORE DATABASE BCCrestore
FROM DISK = 'D:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BBC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BBC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf',
REPLACE,
STATS = 10;
GO
Backup was on D:, I think, just a typo.
May 18, 2015 at 2:19 pm
Ran these after backing up BCC_DB.... just in case
Results:
BackupNameBackupDescriptionBackupTypeExpirationDateCompressedPositionDeviceTypeUserNameServerNameDatabaseNameDatabaseVersionDatabaseCreationDateBackupSizeFirstLSNLastLSNCheckpointLSNDatabaseBackupLSNBackupStartDateBackupFinishDateSortOrderCodePageUnicodeLocaleIdUnicodeComparisonStyleCompatibilityLevelSoftwareVendorIdSoftwareVersionMajorSoftwareVersionMinorSoftwareVersionBuildMachineNameFlagsBindingIDRecoveryForkIDCollationFamilyGUIDHasBulkLoggedDataIsSnapshotIsReadOnlyIsSingleUserHasBackupChecksumsIsDamagedBeginsLogChainHasIncompleteMetaDataIsForceOfflineIsCopyOnlyFirstRecoveryForkIDForkPointLSNRecoveryModelDifferentialBaseLSNDifferentialBaseGUIDBackupTypeDescriptionBackupSetGUID
BCC_DB_backup_20150502001758NULL1NULL012NBCC-US\sqlAgentProxyBADLANDSBCC_DB6112011-06-21 16:21:42.000149130241820000006038002001820000006119000011820000006038002001820000005788002112015-05-02 00:18:01.0002015-05-02 00:18:01.0005201033196609904608904035BADLANDS512AF0B1488-EC09-4A5B-8B76-8684265F1ED84AEB5E8D-063E-47B4-AD55-B2277D2E8E0DSQL_Latin1_General_CP1_CI_AS4AEB5E8D-063E-47B4-AD55-B2277D2E8E0D00000000004AEB5E8D-063E-47B4-AD55-B2277D2E8E0DNULLFULLNULLNULLDatabase97CA0278-5202-4DD4-A29C-B901D707134E
LogicalNamePhysicalNameTypeFileGroupNameSizeMaxSizeFileIdCreateLSNDropLSNUniqueIdReadOnlyLSNReadWriteLSNBackupSizeInBytesSourceBlockSizeFileGroupIdLogGroupGUIDDifferentialBaseLSNDifferentialBaseGUIDIsReadOnlyIsPresent
BCCD:\Databases\Data\BCC.mdfDPRIMARY15728640351843720806401007174C4FC-9F7D-4140-B3FE-E330A0B77D0500141557765121NULL1820000005788002113DFC4FD7-AAF5-445F-980B-6EB90138AF3F01
BCC_logD:\Databases\Data\BCC_log.ldfLNULL1855324162199023255552200E4224B52-1752-461D-ACB3-806DBF9C96E10005120NULL000000000-0000-0000-0000-00000000000001
May 18, 2015 at 2:21 pm
Corrected
May 18, 2015 at 2:21 pm
RESTORE DATABASE BCCrestore
FROM DISK = 'D:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BBC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BBC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf',
REPLACE,
STATS = 10;
GO
Msg 3234, Level 16, State 2, Line 2
Logical file 'BBC' is not part of database 'BCCrestore'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
May 18, 2015 at 2:27 pm
The restore command provided should work. I backed up my Sandbox database and restored it as Sandbox5. The following is the command I used:
USE [master]
RESTORE DATABASE [Sandbox5]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\Sandbox_20150518.bak'
WITH FILE = 1,
MOVE N'Sandbox' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Sandbox5.mdf',
MOVE N'Sandbox_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\Sandbox5_log.ldf',
STATS = 5
GO
The same thing should work for restoring your BCC_DB database as BCCrestore.
May 18, 2015 at 2:28 pm
briancampbellmcad (5/18/2015)
RESTORE DATABASE BCCrestoreFROM DISK = 'D:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BBC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BBC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf',
REPLACE,
STATS = 10;
GO
Msg 3234, Level 16, State 2, Line 2
Logical file 'BBC' is not part of database 'BCCrestore'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Please stop just cutting and pasting and fix the typos some of us made in our posts.
May 18, 2015 at 2:31 pm
RESTORE DATABASE BCCrestore
FROM DISK = 'D:\BCC_DB_backup_201505020017.bak'
WITH MOVE 'BCC' TO 'D:\Databases\Data\BCCrestore.mdf',
MOVE 'BCC_log' TO 'D:\Databases\Data\BCCrestore_log.ldf',
REPLACE,
STATS = 10;
GO
May 18, 2015 at 2:38 pm
Isn't this for a database sored on my local machine? I'm trying to do it on a server based database.
May 18, 2015 at 2:42 pm
briancampbellmcad (5/18/2015)
Isn't this for a database sored on my local machine? I'm trying to do it on a server based database.
No when run on the server it will restore a copy of the database under a different name on the server.
Viewing 15 posts - 46 through 60 (of 65 total)
You must be logged in to reply to this topic. Login to reply