I have a created a snapshot and I'd like to know if there is a way for all the existing and future queries to refer to the database snapshot. Is there any way to do this?
View 4 RepliesHello!
I'm trying to use mirroring + snapshots for reporting on a large database.
I've set up the mirrored database and created a snapshot.
Everything looks fine unil a run some queries on the snapshot.
Some queries work some don't. the error is this:
Msg 954, Level 14, State 1, Line 1
The database "test" cannot be opened. It is acting as a mirror database.
From what i can tell when he's trying to use an index for the query, the engine tries to use it from the mirror database.
eg.
this query works fine:
select top 10 * from dbo.table1
this query gives the error:
select top 10 * from dbo.table1 order by col1 desc
What's going on and how can i make this setup work?
thanks.
Hi All,
My apologies if this question seems abit basic, but I'm a DBA by trade and programming .Net isn't my strong point ;)
I've enabled database mirroring on 3 SQL 2005 servers, a principal, a mirror and a witness.
Principal - SQL 2005 Enterprise Edition, SP1
Mirror - SQL 2005 Enterprise Edition, SP1
Witness - SQL 2005 Express, SP1
I've written some test code to test the mirroring but as soon as the connection is pulled from the principal, the client re-direct doesn't work and the program bombs. I'd be grateful fi someone could have a look at my code below and tell me if there's any schoolboy errors??
(NB, the user running the code is a sys admin on all 3 servers)
Thanks in advance.
Imports System.Data.SqlClient
Public Class Form1
Dim DCTADS As New SqlClient.SqlConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DCTADS.ConnectionString = "Network=dbmssocn; Server=STUD; Failover Partner=DBDRTEST; Database=mirrortest; Integrated Security=True"
DCTADS.Open()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New SqlClient.SqlCommand
cmd.CommandText = "Select fulldesc from unclproduct where internal = '20000110'"
cmd.Connection = DCTADS
Dim dr As SqlClient.SqlDataReader
dr = cmd.ExecuteReader
MsgBox(DCTADS.DataSource.ToString)
If dr.Read Then
MsgBox(dr("fulldesc").ToString)
End If
dr.Close()
End Sub
End Class
Hi All,
My apologies if this question seems abit basic, but I'm a DBA by trade and programming .Net isn't my strong point ;)
I've enabled database mirroring on 3 SQL 2005 servers, a principal, a mirror and a witness.
Principal - SQL 2005 Enterprise Edition, SP1
Mirror - SQL 2005 Enterprise Edition, SP1
Witness - SQL 2005 Express, SP1
I've written some test code to test the mirroring but as soon as the connection is pulled from the principal, the client re-direct doesn't work and the program bombs. I'd be grateful fi someone could have a look at my code below and tell me if there's any schoolboy errors??
(NB, the user running the code is a sys admin on all 3 servers)
Thanks in advance.
Imports System.Data.SqlClient
Public Class Form1
Dim DCTADS As New SqlClient.SqlConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DCTADS.ConnectionString = "Network=dbmssocn; Server=STUD; Failover Partner=DBDRTEST; Database=mirrortest; Integrated Security=True"
DCTADS.Open()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New SqlClient.SqlCommand
cmd.CommandText = "Select fulldesc from unclproduct where internal = '20000110'"
cmd.Connection = DCTADS
Dim dr As SqlClient.SqlDataReader
dr = cmd.ExecuteReader
MsgBox(DCTADS.DataSource.ToString)
If dr.Read Then
MsgBox(dr("fulldesc").ToString)
End If
dr.Close()
End Sub
End Class
I am trying to run a SQL query from a Different Datawarehouse DB on the same server but getting error:
Msg 208, Level 16, State 1, Line 3
Invalid object name 'VirtualManagerDB.dbo.tbl_Cloud_CloudCapacity'.
My query is
select VM1.ID, VM1.VirtualCPUCount, VM1.Memory, VM1.Storage
from [VirtualManagerDB].[dbo].[tbl_Cloud_CloudCapacity] AS VM1
Am running above query in a different database: OperationsManagerDW
In my procedure I need to pull data from a database which is on other server and domain.
As usual I have used the below query but having troubles with mode of authentication.
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=x.x.x.xsyz;Trusted_Connection=yes;','SELECT top 10 * FROM pcm.stats.visits') AS a
I get the below error:
"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication"
I understand from the error that the domain and mode of authentication is different.
I use windows auth and we are given sql auth on the remote server which is on different domain.
I was suggested to use 'sp_addlinkserver' to link the remote server and query the data.
I am just curious to know if we can succeed through link server, is there any script to use sql authentication of remote server in my procedure and pull the data.
I can use Profiler to see database usage activity. However, in addition to it, is there a good query I can use to see whether user databases are being used (last select, last update, last alter or last delete etc., with date/time stamp)?I am looking for both SQL2000 and SQL2005 as we need to decommission some of the older servers.
View 6 Replies View RelatedIs it possible to write query from different database engines?
One database A is in local server database engine and another database B is in another server located in different system.
Now , I need to write a query in database A based on table A and table B (another database engine).
I need a query to list the tables in SQL sever with respective database they belong across databases.
I can obtain list of tables from Sys.Tables / Sys.Objects Views but how do I correlate to which DB a table belongs to?
The Sys.Databases View list the databases with their respective Ids. But, the DBID is not part of Sys.Tables / Sys.Objects Views.
Which View will allow me to fetch database to table mappings?
I'm wondering if there is some sql I can run to check properties on a table. This would be used to verify things like data types, allow nulls and default values have been set to avoid mistakes. This could be done manually one table and one column at a time, but it would be a lot easier to look at it in the results window.
View 5 Replies View RelatedHi guys, can I know the steps on creating a database snapshot on a mirror database? Thx for the assistance. :)
Best Regards,
Hans
HiI am relatively new to databases. I would like to be able to run aquery that returns the t_no, b_no, status and cpu for the latestrecord for each unique combination of (t_no, b_no and cpu) before agiven point in time (say @snap_time). d_no is an autoincrementingprimary key.d_no t_no b_no status cpu update_time1 500 3 0 1 3:012 501 3 1 1 3:023 501 3 0 1 3:034 502 3 1 1 3:045 503 3 1 1 3:056 500 3 1 1 3:10In this example, if @snap_time = '3:15' the query would return the(t_no, b_no, status, cpu) for records 3, 4, 5, 6 because 1 issuperceded by 6 (which has the same t_no, b_no, cpu combination butoccurs later) and 2 is superceded by 3.If @snap_time = '3:03', the query would return records 1 and 3 becauserecords 4, 5 and 6 were created at a later update_time and record 3supercedes record 2.The query:SELECT MAX(update_time), t_no, b_no, cpu, statusFROM my_tableWHERE (time <= @snapTime)GROUP BY t_no, b_no, cpureturns an error:Column 'dbo.my_table.status' is invalid in the select list because itis not contained in either an aggregate function or the GROUP BYclause.And even if this did work, ideally I would like to suppress theupdate_time field from appearing.How could I do such a query and is it even possible without usingsomething more sophisticated than a select query?Many thanksJon
View 1 Replies View RelatedHi
I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.
Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.
The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.
I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.
All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.
I have a mirred database and have snapshot created from the mirroed database. I can do data selecting from the snapshot. But when the pricipal server is down , I can re-active the mirroed database.( I did succesfully upto this). Then I need to restore the database from snapshot and which failing with following error message.
Code Snippet
Msg 5123, Level 16, State 1, Line 3
CREATE FILE encountered operating system error 3(error not found) while attempting to open or create the physical file 'E:sql_datadevitst_mirrorlog st_mirror_log.ldf'.
Msg 5024, Level 16, State 2, Line 3
No entry found for the primary log file in sysfiles1. Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 3
The system could not activate enough of the database to rebuild the log.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
best way to snapshot results from a query?
View 3 Replies View RelatedHi...
Why is it that I encounter an error during execution of database snapshot i SQL Server 2005 Standard Edition. It says that database snapshot is not supported under standard versin. Is this true?
Thanks.
Need some help, i have some database snapshots files provided from an external source. Need to be able to understand how i get them back into a database format if possible.
Files for example are table1.bcp, table2.bcp with als a file called scheme.sql which sets up these tables in sql but does not populate them. Nothing else was provided except a .vcd file which i dont know whtat its for.
any clues?
Dan
When running the snapshot agent for a new publication I get the message:
Message: Query for data failed
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.ThrowNativeBcpOutException(CConnection* pNativeConnectionWrapper)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint, String strWhereClause)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strBaseBcpObjectName, Boolean fUnicodeConversion, String strDataFile, String strLoadOrderingHint)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.NativeBcpOutProvider.BcpOut(String strBcpObjectName, String strBcpObjectOwner, String strDataFile)
at Microsoft.SqlServer.Replication.Snapshot.MergeContentsBcpOutWorkItem.DoWork(MergeSnapshotProvider snapshotProvider, IBcpOutProvider bcpOutProvider)
at Microsoft.SqlServer.Replication.Snapshot.MergeContentsBcpOutThreadProvider.DoWork(WorkItem workItem)
at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
at Microsoft.SqlServer.Replication.WorkerThread.AgentThreadProc()
at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Message: COLV_90_TO_80: Cannot convert parameter param1: Resulting colv would have too many entries.
Stack: (Source: MSSQLServer, Error number: 20005)
Get help: http://help/20005
Message: The statement has been terminated.
Stack: (Source: MSSQLServer, Error number: 3621)
The database has another publication, that was created prior to upgrade to sql server 2005 from sql server 2000 sp4. This publication is working ok, and there are no problems when running the snapshot agent for this publication.
The publications are using filtered articles, and all properties for the publications are the same.
Any help to solve this problem will be much apreciated.
Does anyone know of a script I can run to take a snapshot of a SQL server 7 database? If not what is the best way to go about getting a snapshot of the db?
Thanks!
Hi everyone,
I am looking for a way to check to see if a database is a snapshot or not. You can for example check different properties of a database by running the following:
SELECT DATABASEPROPERTYEX('databasename','Recovery')
SELECT DATABASEPROPERTYEX('databasename','IsInStandBy')
SELECT DATABASEPROPERTYEX('databasename','Status')
So I am trying to see if there is a way to check for a snapshot
Thanks,
Reghardt
When configuring snapshot replication of the OnePoint database used by MOM i am having the following error being reported in SQL Replication Monitor:
Command attempted:
CREATE TABLE "dbo"."Computer"(
"idComputer" "uniqueidentifier" ROWGUIDCOL NOT NULL,
"Name" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
"Description" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"CustomData1" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"CustomData2" "nvarchar"(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"Domain" "nvarchar"(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
"DNSName" "nvarchar"(512) COLLATE SQL_Latin1_General_CP1
(Transaction sequence number: 0x000004670002E32300BF00000002, Command ID: 375)
Error messages:
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_ComputerHeartbeat", or the name is ambiguous. (Source: MSSQLServer, Error number: 4121)
Get help: http://help/4121
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_ComputerHeartbeat", or the name is ambiguous. (Source: MSSQLServer, Error number: 4121)
Get help: http://help/4121
Unfortunately I can't find much help with this error.
Any suggestions?
Hi all,
To get current data from our Mirrored Database, we can use snapshot.
But,right now, we have logshipped database, so for reports we are getting almost current data.
So, i wanted to know, how current data i can get from Database Snapshots for the Reports.
Thanks.
I have created a snapshot
CREATE DATABASE AdventureWorks_Snapshot1200 ON
(NAME = N'AdventureWorks_Data', FILENAME=
N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAW_1200.ss')
AS SNAPSHOT Of AdventureWorks
I changed the records
AddressLine1
from 1970 Napa Ct. to 1970 Napa Ct. Apt 1
When i run
select * from Person.Address
AventureWorks_Snapshot1200 where addressid = '1'
select * from Person.Address
AventureWorks where addressid = '1'
Both appear as 1
When i change Apt to 2 and run it both appear 2
Changed again to 3 both appeared as 3 too.
I was under the impression that the snapshot would remain as 1 when the new update 2 has been performed.
Any light be appreciated.
We've got a problem with our replication. If I try to run the snapshot agent I get the message "Query for data failed".
The detail view of this message shows the following:
Hello Every
I have a problem with ASP.NET connect to Database Snapshot SQL Server 2005.
Step One: I create database snapshot.
Step Two: I want to use ASP.NET to Database Snapshot that create already. But I don't know connection string to database snapshot.
Please help me ............................
Thank You,
The following list of actions leads to a corrupt database on SQL Server 2005.
Create a database snapshot
Drop a table in the database
Backup the database
Restore from backup
Revert to the snapshot
I'm not entirely surprised that it results in a corruption, what is surprising is that I can revert from a snapshot after restoring. There needs to be some kind of check to prevent reverting to a snapshot in a case like this. Until SQL Server prevents you from doing it, I'd recommend a best practice is to delete all snapshots before you restore a database so that you cannot do this by accident.
Hi,
I need to restore a sql server 2000 database which does not have any backups apart from a snapshot folder which contains some .bcp, .IDX and .Sch files. (there was a transactional replication setup on this database at some point and the snapshot folder is still there.)
Is this possible? any help will be highly appreciated.
thanks,
Alind
I would like to use SQL Server's built in database snapshot feature. We have a database (e.g., ThatDatabase), and I would like to capture a snapshot of it at exactly midnight each morning. I would need the snapshot to have the same name each day (e.g., ThatDatabase12AM). My non-technical end users (statisticians with SAS) are going to use the snapshot a lot, but are not going to be able to handle it it having a different name every day.
Obviously, I could set up a job with a start time of 23:59:50 that drops the existing snapshot and then creates a new snapshot. This would have the problem that the snapshot name disappears for a few seconds every day. That would either a) kill user sessions, or b) block the snapshot drop and delay the new snapshot until after exactly 12:00 AM.
Hi All
I am in process of moving a SQL 2005 solution from a development box that used local storage to UAT environment with SAN attached storage. The solution uses database snapshots
The database files are on the SAN storage but during testing I was unable to create a Database snapshot on the SAN disk. Creating snapshots on the local disk worked fine.
Is their some restriction/problem in using the database snapshot technology with SAN storage?
All,
We have SQL 2005 db mirror configured with a witness server for high availability. Node 1 is the principal and Node 2 is the mirror. A nightly job creates a snapshot on Node 2. The snapshot is used for previous day reporting queries. We have now been asked to present another copy of the database for near-time reporting. I thought about possibly adding a peer-to-peer replication as part of my environment but was hoping to see what everyone else out there is doing.
Regards,
Ian
I have huge database on prod. One time I tried to run DBCC CHECKDB, it took more than a day. My question is can I created a snapshot of the prod database on the same server and run DBCC CHECKDB on the Snapshot DB? will doing this interfere production database? I don’t have option to make copy of the database on a test server and run it there.
View 9 Replies View RelatedHow do I set up a database snapshot and backup the database for Windows Server Update Services 3.0, that uses the "desktop" version of SQL server on the same server running Widows Server 2003? There does not seem to be a "Management Plans" sub-folder under the "management" folder in the Management Studio Express console- so that's probably my first request- how do I establish a "Management Plans" subfolder, then how do I (step-by-step) set up a backup of the SUSDB?
View 21 Replies View RelatedProcess to create snapshot failed (twice per day snapshot of the database is being created for some people to work on). Morning snapshot worked fine but the afternoon one failed. The snapshot is being created from Mirror database. I used this code:
CREATE DATABASE [DB_snapshot] ON
(name = N'DB',filename = N'D:SnapshotDB.SQLSnapshot' )
,(name = N'indexes',filename = N'D:SnapshotDB_indexes.SQLSnapshot' )
AS SNAPSHOT OF DB
And the error message i get
Msg 1823, Level 16, State 6, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 7, Line 1
A database snapshot cannot be created because it failed to start.
Msg 3456, Level 21, State 1, Line 1
Could not redo log record (202011:19306:2), for transaction ID (0:0), on page (1:1823948), allocation unit 281474979397632, database 'DB_snapshot' (database ID 6). Page: LSN = (201954:220201:1), allocation unit = 281474979397632, type = 1. Log: OpCode = 4, context 18, PrevPageLSN: (202010:23679:1). Restore from a backup of the database, or repair the database.
Msg 3313, Level 21, State 1, Line 1
During redoing of a logged operation in database 'DB_snapshot', an error occurred at log record ID (202011:19306:2). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
What might be causing this error? Mirroring seems to be working OK. status is "Synchronized"