December 21, 2015 at 7:23 am
How do I Restore Backup to a d different Database Name?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 21, 2015 at 7:31 am
https://msdn.microsoft.com/en-us/library/ms186858.aspx Example E is a good starting point.
December 21, 2015 at 8:45 am
Welsh Corgi (12/21/2015)
How do I Restore Backup to a d different Database Name?
Did you even check BOL for this? It's something which I would expect someone with thousands of SSC posts to do routinely before posting.
Also, you'd better be sure that your database's views/procs etc do not contain fully qualified names, or this is a very bad idea.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 21, 2015 at 12:50 pm
Thank you for your kind words,
I have done this before.
What is the syntax?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 21, 2015 at 12:55 pm
What is the syntax?
From the link Steve posted above:
--To Restore an Entire Database from a Full database backup (a Complete Restore):
RESTORE DATABASE { database_name | @database_name_var }
[ FROM <backup_device> [ ,...n ] ]
[ WITH
{
[ RECOVERY | NORECOVERY | STANDBY =
{standby_file_name | @standby_file_name_var }
]
| , <general_WITH_options> [ ,...n ]
| , <replication_WITH_option>
| , <change_data_capture_WITH_option>
| , <FILESTREAM_WITH_option>
| , <service_broker_WITH options>
| , <point_in_time_WITH_options—RESTORE_DATABASE>
} [ ,...n ]
]
[;]
--To perform the first step of the initial restore sequence-- of a piecemeal restore:
RESTORE DATABASE { database_name | @database_name_var }
<files_or_filegroups> [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
WITH
PARTIAL, NORECOVERY
[ , <general_WITH_options> [ ,...n ]
| , <point_in_time_WITH_options—RESTORE_DATABASE>
] [ ,...n ]
[;]
--To Restore Specific Files or Filegroups:
RESTORE DATABASE { database_name | @database_name_var }
<file_or_filegroup> [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
WITH
{
[ RECOVERY | NORECOVERY ]
[ , <general_WITH_options> [ ,...n ] ]
} [ ,...n ]
[;]
--To Restore Specific Pages:
RESTORE DATABASE { database_name | @database_name_var }
PAGE = 'file:page [ ,...n ]'
[ , <file_or_filegroups> ] [ ,...n ]
[ FROM <backup_device> [ ,...n ] ]
WITH
NORECOVERY
[ , <general_WITH_options> [ ,...n ] ]
[;]
--To Restore a Transaction Log:
RESTORE LOG { database_name | @database_name_var }
[ <file_or_filegroup_or_pages> [ ,...n ] ]
[ FROM <backup_device> [ ,...n ] ]
[ WITH
{
[ RECOVERY | NORECOVERY | STANDBY =
{standby_file_name | @standby_file_name_var }
]
| , <general_WITH_options> [ ,...n ]
| , <replication_WITH_option>
| , <point_in_time_WITH_options—RESTORE_LOG>
} [ ,...n ]
]
[;]
--To Revert a Database to a Database Snapshot:
RESTORE DATABASE { database_name | @database_name_var }
FROM DATABASE_SNAPSHOT = database_snapshot_name<backup_device>::=
{
{ logical_backup_device_name |
@logical_backup_device_name_var }
| { DISK | TAPE | URL } = { 'physical_backup_device_name' |
@physical_backup_device_name_var }
}
Note: URL is the format used to specify the location and the file name for the Windows Azure Blob. Although Windows Azure storage is a service, the implementation is similar to disk and tape to allow for a consistent and seemless restore experince for all the three devices.<files_or_filegroups>::=
{
FILE = { logical_file_name_in_backup | @logical_file_name_in_backup_var }
| FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }
| READ_WRITE_FILEGROUPS
}
<general_WITH_options> [ ,...n ]::=--Restore Operation Options
MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'
[ ,...n ]
| REPLACE
| RESTART
| RESTRICTED_USER | CREDENTIAL
--Backup Set Options
| FILE = { backup_set_file_number | @backup_set_file_number }
| PASSWORD = { password | @password_variable }
--Media Set Options
| MEDIANAME = { media_name | @media_name_variable }
| MEDIAPASSWORD = { mediapassword | @mediapassword_variable }
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Data Transfer Options
| BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
--Error Management Options
| { CHECKSUM | NO_CHECKSUM }
| { STOP_ON_ERROR | CONTINUE_AFTER_ERROR }
--Monitoring Options
| STATS [ = percentage ]
--Tape Options
| { REWIND | NOREWIND }
| { UNLOAD | NOUNLOAD }
<replication_WITH_option>::=
| KEEP_REPLICATION
<change_data_capture_WITH_option>::=
| KEEP_CDC
<FILESTREAM_WITH_option>::=
| FILESTREAM ( DIRECTORY_NAME = directory_name )
<service_broker_WITH_options>::=
| ENABLE_BROKER
| ERROR_BROKER_CONVERSATIONS
| NEW_BROKER
<point_in_time_WITH_options—RESTORE_DATABASE>::=
| {
STOPAT = { 'datetime' | @datetime_var }
| STOPATMARK = 'lsn:lsn_number'
[ AFTER 'datetime' ]
| STOPBEFOREMARK = 'lsn:lsn_number'
[ AFTER 'datetime' ]
}
<point_in_time_WITH_options—RESTORE_LOG>::=
| {
STOPAT = { 'datetime' | @datetime_var }
| STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime' ]
| STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
[ AFTER 'datetime' ]
}
December 21, 2015 at 4:35 pm
cI am just trying to restore PrismDataSave from a PrismData backup
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 21, 2015 at 4:48 pm
Welsh Corgi (12/21/2015)
cI am just trying to restore PrismDataSave from a PrismData backup
Everything you need (and more) is up there. Trying figuring out what you think you need and post it here. We'll help you get the correct syntax, but you need to learn how to read Books Online to figure this out as well. You do really want to always rely on people on the internet or do you want to get to the point where you can help others, whether on the internet or in the workplace?
December 21, 2015 at 5:01 pm
Thanks!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 21, 2015 at 9:58 pm
Check out the following link hope this will help you: http://dba.stackexchange.com/questions/51489/sql-server-2012-restore-backup-to-new-db-name
December 22, 2015 at 9:35 am
Welsh Corgi (12/21/2015)
Thanks!
Sarcasm noted.
USE [master]
RESTORE DATABASE [AdventureWorks2014Test] -- Restore to a Different name
FROM DISK = N'C:\SQLBackups\AW2014.bak' -- Using the backup of the "production" database
WITH FILE = 1,
MOVE N'AdventureWorks2014_Data' TO N'C:\SQLData\AdventureWorks2014Test_Data.mdf',
MOVE N'AdventureWorks2014_Log' TO N'C:\SQLLogs\AdventureWorks2014Test_Log.ldf',
NOUNLOAD,
STATS = 5;
GO
Here, this is what you need.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply