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
Hi guys, can I know the steps on creating a database snapshot on a mirror database? Thx for the assistance. :)
Best Regards,
Hans
Hi
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.
Hi...
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.
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.
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?
Hello!
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.
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 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 Replies View RelatedI 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"
We recently migrated our production server from SQL 2005 (Standard) on Win2003(32-bit) to SQL2012 (Standard; v11.0.3000) on Win2008-R2(64bit). Single-server Dell R510 with 1.2TB storage. Everything went smoothly; the only nagging issue remaining is failure of our maintenance jobs. I tracked the issue down to failure of DBCC CHECKDB. Specifically, the error is:Executed as user: NT SERVICESQLSERVERAGENT. The database could not be exclusively locked to perform the operation. [SQLSTATE 42000] (Error 5030) Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be lockedI have Googled this issue and read extensively. For instance, informative blogs (albeit dated) such as these by Paul Randal (Managing
CheckDB by default takes an internal DB snapshot to get the consistent, point-in-time view of the DB that it needs. If that snapshot creation fails, then it will try to get an exclusive database lock before proceeding (same as if you had executed DBCC CHECKDB WITH TABLOCK). The root problem is not that the lock could not be obtained, it's that the internal database snapshot could not be created. msdn.microsoft.com/en-us/library/ms188796.aspx details the specific situations when an internal database snapshot is not created and table locking is attempted.
I have verified the SQLSERVERAGENT service account has full permissions on the SQLDATA directory where the databases reside and has full permissions on each database within the directory. Just for giggles, I created a job (run as SQLSERVERAGENT) that creates and then deletes a text file in the SQLDATA directory. It runs fine.
Also testedI get the snapshot creation error when manually running DBCC CHECKDB against any of our databases and when executing under a variety of administrator accounts that are members of the SQL sysadmin role and the Domain Admins security group (the Domain Admins is a member of the local Administrators group that has full permissions on all SQL directories/folders).
Additionally, the databases in question are small (200MB to 6GB) and the disk has plenty of elbow room (978GB free on 1.22TB RAID5 array) to create the internal database snapshots. CHECKDB doesn't surface an error message that is detailed enough to determine the precise cause of the error. Any example successfully running DBCC CHECKDB on the SQL2012 (Standard) on a Win2008 R2 (64-bit) server.
What is a snapshot from replication point of view.
------------------------
I think, therefore I am - Rene Descartes
Dear friends
I am a new fellow to replication.What is the problem I am getting is I am not able to create snapShot.Whenever I start Snapshot Agent it is going for half an hour and it will stuck in the case of one view saying like that 'failed to process bulk copy of data from dbo.syncobj_03x666*'.If I am publishing tables only then also I am getting same error.This view created by system.In my case publiher only working as a distributor.I created a SQL username which have access in subscriber & Publisher.Preferably I will be happy if someone suggest what and all are the primary criterias we have to keep in mind while doing Replication
Thanks in Advance
Filson
I get the following error message in the job history for
synchronization between two SQL 7 machines across the
network.
The process could not read file 'acct.sch' due to OS error
1231.
Is there something to fix this? It has been running fine
for almost 2 years and suddenly quit. There is a firewall
on both sides, if that helps.
TIA,
Cami
The database will grow to 40GB in a short while and I intend to schedule replication every hour. Can this not be done using snapshot replication??
Subject:
From:
Date: Snapshot Replication - Help!! (reply)
MAk (mak_999@yahoo.com)
5/7/2002 12:02:08 PM
Create jobs to copy database and restore database in destination servers
------------
Robert at 5/7/2002 11:00:30 AM
Yes and I would rather not use dts to accomplish this task.
------------
Ray Miao at 5/7/2002 10:02:15 AM
Do you have direct network connection to remote server? Did you try dts?
------------
Robert at 5/7/2002 9:08:06 AM
I've been trying to replicate a database to an off site server using snapshot replication. It is scheduled to run every hour but I've noticed when data is changed at the source it never gets replicated to the destination. Does anyone know why?? I can't use transactional replication beause not all the tables have primary keys and they can't be added due to code. Some tables have id colunms and have been created with the Not for Replication option on the subscriber. Any help will be appreciated.
Thanks
Robert
I can set up snapshot replication for those tables without foreign key constraints. But if there are foreign keys in the table, there will be error
message indicating that this object can not be dropped because it is referenced by ....
Do you have any remedy for that? Thanks
Hi,
I've problem in replicating data thru SNAPSHOT as I've the tables with Foreign Keys at subscriber end. The Truncate table is not working because these tables
were referenced by a FK. Even for recreating table during snapshot is also same problem. Any suggestions?
Thanks
TT
2 questions:
1) In snapshot replication, can the subsciber send info back to the publisher (even in a manual process)
2) In snapshot replication, do we need a distributor set up between the publisher and subscriber if there will only be a single subscriber, or can we write directly to it?
Thanks so much for any and all help!
Hi,
I am replicating all tables in the DB. the tables have PKs,FKs and identity columns. While truncating the data from the tables in subscriber getting
the constraint errors. Please suggest how to get rid of this error.
Thanks
Sam
Hi,
I am replicating all tables in the DB. the tables have PKs,FKs and identity columns. While truncating the data from the tables in subscriber getting
the constraint errors. Please suggest how to get rid of this error.
Thanks
Sam
Hi everybody,
We have merge replication between two servers with Sql Server 2000 Service Pack 2, and the Merge agent display the follow message :
The snapshot for this publication has become obsolete. The snapshot agent needs to be run again before the subscription can be synchronized.
But i have changes at the suscriber, How can I do to Merge the last changes if the merge agentes is stopped.
I appreciate any help.
thanks
Hi Guys,
We have a production server in East Coast (SQL Server 2000 SP2 - Database size is around 30 Gig). We have a reporting server is the West Coast. We need to replicate (transactional replication every one hour) from East coast to West coast. Is there any way that I can take a backup and restore upto the last transaction backup and then start replication agent on the production (by saying schema and data already exist). Basically we don't wan't to snapshot using FTP or bcp through WAN because it is going to be very slow.
If this is possible, will there be any validation problem.
Please help.
Thanks,
Anu