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:
BackupName BackupDescription BackupType ExpirationDate Compressed Position DeviceType UserName ServerName DatabaseName DatabaseVersion DatabaseCreationDate BackupSize FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BackupStartDate BackupFinishDate SortOrder CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName Flags BindingID RecoveryForkID Collation FamilyGUID HasBulkLoggedData IsSnapshot IsReadOnly IsSingleUser HasBackupChecksums IsDamaged BeginsLogChain HasIncompleteMetaData IsForceOffline IsCopyOnly FirstRecoveryForkID ForkPointLSN RecoveryModel DifferentialBaseLSN DifferentialBaseGUID BackupTypeDescription BackupSetGUID
BCC_DB_backup_20150502001758 NULL 1 NULL 0 1 2 NBCC-US\sqlAgentProxy BADLANDS BCC_DB 611 2011-06-21 16:21:42.000 14913024 182000000603800200 182000000611900001 182000000603800200 182000000578800211 2015-05-02 00:18:01.000 2015-05-02 00:18:01.000 52 0 1033 196609 90 4608 9 0 4035 BADLANDS 512 AF0B1488-EC09-4A5B-8B76-8684265F1ED8 4AEB5E8D-063E-47B4-AD55-B2277D2E8E0D SQL_Latin1_General_CP1_CI_AS 4AEB5E8D-063E-47B4-AD55-B2277D2E8E0D 0 0 0 0 0 0 0 0 0 0 4AEB5E8D-063E-47B4-AD55-B2277D2E8E0D NULL FULL NULL NULL Database 97CA0278-5202-4DD4-A29C-B901D707134E
LogicalName PhysicalName Type FileGroupName Size MaxSize FileId CreateLSN DropLSN UniqueId ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN DifferentialBaseGUID IsReadOnly IsPresent
BCC D:\Databases\Data\BCC.mdf D PRIMARY 15728640 35184372080640 1 0 0 7174C4FC-9F7D-4140-B3FE-E330A0B77D05 0 0 14155776 512 1 NULL 182000000578800211 3DFC4FD7-AAF5-445F-980B-6EB90138AF3F 0 1
BCC_log D:\Databases\Data\BCC_log.ldf L NULL 185532416 2199023255552 2 0 0 E4224B52-1752-461D-ACB3-806DBF9C96E1 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1
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 61 total)
You must be logged in to reply to this topic. Login to reply