Can two databases have same logical filenames in the same instance?
In my instance, database already exists say A.
I have to restore DB B with the same backup used for database A in the same instance.
So, once the database B is restored, A and B will have same logical fileNames.
Can I leave it as is? Or do i alter only after B is restored?
Or is there a way where I can change the logical file for B while restoring?
Hello All,
I am facing problem with Logical File Name while restoring database. I am taking a backup of ABC database from Server1 in abc.bak file.
Then I am creating new database XYZ on Server2. Server1 and Server2 are not connected to each other in anyway, since both servers belong to different companies. Here on Server2 I want to restore backup of abc.bak on XYZ database. I select restore from Device, and on option tab I am selecting Force restore over existing database, in the below option I am specifying current Data & log file path for Move to physical file name.
But Logical file name shows ABC names, but if I tried to change those to XYZ names then restore fails. But if I keep ABC names then it works.
Is anybody knows how to over come this Logical Name issue?
Many thanks in advance….
Regards,
Santosh
If I run an ALTER DATABASE command that renames the Logical file name of a database, will I run into any unseen consequences in terms of day to day use of the database?
View 4 Replies View RelatedHello,
Is there any way to change the logical filename of exsiting database
Thanks
Lara
I have a SQL Server 2008 R2 Cluster. I have moved a file to another drive by adding a new file on the drive, doing a shrinkfile with emptyfile. I have removed the old file and when I try to rename the new file to the old files old name, I get an error.
ALTER DATABASE mydb REMOVE FILE myfilename
Msg 5009, Level 16, State 9, Line 1
One or more files listed in the statement could not be found or could not be initialized.
ALTER DATABASE mydb
modify FILE ( NAME = temp_filename
, NEWNAME = myfilename
)
Msg 1828, Level 16, State 3, Line 4
The logical file name "myfilename" is already in use. Choose a different name.
I have completed a failover, thinking the name was being held in cache. I've also renamed to something completely different then tried to rename to the old filename.
I want to back up my database to a logical drive and restore it from this logical device. But I am unable to see my logical device through sql server management studio interface. I am able to back up to a logical device and restore it from logical device through T-SQL code. I am wondering how come I am unable to see my logical device from sql server management studio interface. Is there any way, I can solve this problem? Thanks in advance.
View 3 Replies View RelatedHi,I am planning to automate a nighty restore of a DB on another servercan someone point me in the right direction with the SQL script tomodify the logical file names to the correct path and not the onescarried over with the DB??i.e the database is to be renamed on the new serverany help much appreciatedMany thanks in advance
View 14 Replies View RelatedIs it possible to backup/restore sql server databases in a logical way like oracle(exp/imp, expdp,impdp, can export/import tables/users etc)?my case, I have a database which has many data files, in different file groups, I would like to put them together into one file and don't want to shred the database.
View 6 Replies View RelatedI'm trying to create a standby server. Server 1 has all files locacted in the D drive, Server 2 will have the files located on the C drive. I've done a backup on server1 and moved the x.bak file to server 2. I then run the commandrestore filelistonlyfrom disk = 'C:Program FilesMicrosoft SQL ServerMSSQL$SQLSERVER03BACKUPHS_Webcalendar backup.BAK'and get the resultsHS_Webcalendar_dat D:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.mdf D PRIMARY 2097152 35184372080640HS_Webcalendar_log D:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.ldf L NULL 2097152 35184372080640When I run the restore commandrestore DATABASE HS_Webcalendarfrom disk = 'C:Program FilesMicrosoft SQL ServerMSSQL$SQLSERVER03BACKUPHS_Webcalendar backup.BAK'with move 'HS_Webcalendar_dat' to 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.mdf',move 'HS_Webcalendar_log' to 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.ldf', standby = 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_WebcalendarUndo.ldf'I'm getting the following errorServer: Msg 5105, Level 16, State 2, Line 1Device activation error. The physical file name 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.mdf' may be incorrect.Server: Msg 3156, Level 16, State 1, Line 1File 'HS_Webcalendar_dat' cannot be restored to 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.mdf'. Use WITH MOVE to identify a valid location for the file.Server: Msg 5105, Level 16, State 1, Line 1Device activation error. The physical file name 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.ldf' may be incorrect.Server: Msg 3156, Level 16, State 1, Line 1File 'HS_Webcalendar_log' cannot be restored to 'C:Program FilesMicrosoft SQL ServerMSSQLdataHS_Webcalendar.ldf'. Use WITH MOVE to identify a valid location for the file.Server: Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.
View 3 Replies View RelatedWe tried to restore an empty database from a backup and got:
'Load database encountered an invalid logical page number' error.
This then set the database as 'loading'
Anyone got any ideas?
We aren't exactly sure how big the first database was when the backup was done but if the new database isn't big enough to contain the backed up data, wouldn't we get a more meaningful error?
Janet
I have a full backup followed by transaction log every Monday, Wednesday and Friday, how can i restore this file using sql agent to automate restoration of backup files with different file-name.
View 6 Replies View RelatedWhat is the difference between Logical Join and Physical Join and there Types?
View 6 Replies View RelatedI am loading files with same format from two different locations in to one database. First, my SSIS connects to location 1.
Lets Say
Location1 : C:LoadFilesImport
Location2 : D:LoadFilesImport
Location one has three different set files starting with
First Set: AP_1, AP_2,AP_3,
Second Set: VD_1, VD_2, VD_3,
Third Set: BK_1,BK_2,BK_3,
This SSIS set to run every 3 hours, if it finds files of any set load them. While loading it has to insert into a derivedcolumn called CustID. If the file name Starts with AP_ , custiD values should be as 101
AP_1 goes to Table1
AP_2 goes to Table2
AP_3 goes to Table3
If the file name Starts with VD_ , custiD values should be as 201
If the file name Starts with BK_ , custiD values should be as 301
after processing all these files in first location, the SSIS looks for files in second location and does the same?
--How to achieve CustID depending upon file name ?
Doesn't appear you can do this.
Am I wrong?
Please tell me I am.
Hi Champs,
Scenario Configuration : VB.net 2005 Code, WebService for Executing SSIS on Server, SSIS deployed on the Database Server
Problem Description : We are developing windows applicaiton in which we call webservice which was deployed on the same server where SSIS packages are deployed.
Now from Code we are passing FilePath name in variable and execute the Package. But the SSis result says that
The file name "\computernamefol1fol2fol3fol4abc.txt" specified in the connection was not valid.
More Information:
1. Full Permission are given on this network folder.
2. Package executes successfully from SSIS development solution (BIS solution)
3. Deployed packed executes successfully from the Database Server.
4. From Development pc packege executes successfully.
5. Other packages deployed on the same server executed suucessfully with same configuration and scenario.
Only this package is not executing.
-- the only differece with this package with other is -
using ".txt" extension in Flat file connection and using VB Script task
Can any one suggest the appropirate solution for this problem...
Thanks
Tarang Pandya
We would like to benchmark our logical reads daily to show our improvement as we tune the queries over time.
I am using sys.dm_exec_query_stats summing the Physical and Logical Reads. Is this a viable option for gathering this metric? Is this a viable metric to gather?
select sum(total_physical_reads) as TotalPhyReads, sum(total_logical_reads) as TotalLogReads from sys.dm_exec_query_stats;
How best to provide performance based metrics.
I have a VIEW which is dynamically generated through complex dynamic SQL. Unfortunately the dynamic SQL uses "Select * from table" to select the columns because the programmer did that to reduce the amount of code in the dynamic SQL string as the code can't be debugged if it's too long.
Therefore, I have a VIEW with columns in it I don't need, and want to remove them from the view - I need to remove all columns with column names matching the syntax '%1%_2' .
The view is called TEMP_EXPORT_1
I can either use the code below to return a list of columns that I want removed:
select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name like '%1%_2'
Or I can use the code below to return the list of columns that I want to keep:
select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2'
Now how would I go about altering TEMP_EXPORT_1 view so that it no longer has these columns? I know views don't have a drop statement...
Therefore I tried the following but I'm not sure of the syntax:
ALTER VIEW dbo.TEMP_EXPORT_1
AS
SELECT (select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2')
FROM dbo.TEMP_EXPORT_1
Am I on the right track? how can I ALTER this view to remove these columns? ... I want to keep this separate from the code that generated the view as I want it as an optional procedure that can be run if needed.
Running this query in DR server to start SQL mirroring but encountered an error below.
Query:
use master
go
alter database test set partner= N'TCP://HOSTNAME.DOMAIN.GROUP.INTRANET:5023'
go
Error:
Msg 1452, Level 16, State 6, Line 2
The partner server instance name must be distinct from the server instance that manages the database. The ALTER DATABASE SET PARTNER command failed.
Without giving dbowner how can we grant permission to user to alter/create procs?
View 3 Replies View Relatedusing below query to raplace the string values (REPLACE abc with T1223), how to use the query without hard coding.
i want to store the values in another temp table and access in main query.
'abc', 'T1223',
'def', 'T456',
'ghi', 'T789',
'jkl', 'T1011',
'mno', 'T12'
select id,name,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(name,
'abc', 'T1223'),
'def', 'T456'),
'ghi', 'T789'),
'jkl', 'T1011'),
'mno', 'T12'))) New_id
from TAB
I need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
View 4 Replies View RelatedHi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedWe need to restore total 48 dbs on sql server 2012 for a migration task.
If we do manually restore all dbs it will take time so .
Any automation scrip to restore all dbs on a server.
I have TDE backup one serverA but There is no backup of certificates or keys from Server A. And no one knows the password used to create those backups. How do you restore the database XYZ at that time on Server B?
View 9 Replies View Related1.can we restore the db if it is offline?
2.restore information stores in table 'msdb..restorehistory' if we restore the db when the db is offline?
I am on SQL 2012 standard version and I am writing a script to restore database from .bak files on a network.
ALTER DATABASE DB1
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
----Restore Database
RESTORE DATABASE DB1
FROM DISK = 'N:SQLBackupDailyDB1_backup_2015_06_22_194002_0500494.bak'
WITH REPLACE
ALTER DATABASE db1 SET MULTI_USER
GO
Since I have to restore about 100 databases , I am planning to put the script in a cursor. However my problem is how can I get the bak file name dynamically .
So I'm testing some things in our new servers and was trying to restore a database from some striped backup sets. We have 4 files for our backups and restoring the FULL backups with no recovery worked beautifully via SSMS. But when I tried to restore the differentials (also striped across 4 files), the GUI gave me this error:
Unable to create restore plan due to break in the LSN chain.
How to locate when the break happened and I came across this article about how this is an SSMS 2012 bug.
So I tried the advice in this article to attempt a restore via Files and Filegroups, and ended up with the below error:
EDIT: Picture is attached if it is not showing in post.
I was able to restore via T-SQL, but I want to also be able to restore through the GUI.
I have this situation: -Two nodes cluster with two instances sql cluster 2012 and I need to move all on the new cluster 2012 with finally the same virtually name and instances.
It's possible to restore master order to preserve all security?
I have a DB, MDF is about 170GB, log 200GB. When I do a restore of it, the backup takes about 20 minutes to get to 100% but then it just stays there in Restoring Mode.
I have specified the WITH RECOVERY option.
What is happening that it doesn't commit the backup for some reason?
I'm working on a project where I need to build a small database and then copy it to a server at the client's site. I can't connect directly, so I have to use a VPN connection and use Remote Desktop, copy the database backup from my machine to the cloud, then download it to the client machine. The project is still in the early stages, and the client is still sending me data in CSV files and Excel spreadsheets. I'm periodically needing to do a complete refresh of the database at the client. I've hacked my way through it a couple of times, but I need to know the proper way to do it. I get errors on the restore step, telling me the file is in use.
View 8 Replies View RelatedIs it possible to restore two databases simultaneously in same sql instance?
View 3 Replies View RelatedIf one is regularly taking backups of system databases, when does it become necessary to rebuild the master database. I am looking for a situation where rebuilding the master is preferred to restoring it from backup.
View 1 Replies View RelatedI refresh QA environments with copies of our production database quite often. Many of the users also have read-only logins to production, but not all. I've noticed that in QA the users in the restored database are matched up with QA server logins that no longer have "DBO" as their default schema. We almost always use DBO, nothing else.
On the most recent restore, I didn't drop the target DB first, just restored with "replace." Does it matter whether I drop or replace in this instance?
The one user reporting issues could not open the database in management studio to view objects/tables etc. I noticed their default schema was their domain login, so fixed that and they now no longer have the issue.