Facundo writes "Hi:
How can i add a timestamp to a generated file of a complete database backup, the idea is to archive full database backups using the date to identified it.
Thanks a lot.
FB"
Backup failed to complete the command backup database [ ] TO VIRTUAL DEVICE = ' { 853D3FC0 - 45EA -85B1 - 54F0EA379CAC } 24 ' WITH SNAPSHOT , BUFFERCOUNT = 1 , BLOCKSIZE = 1024
View 1 Replies View Related
How do you add a specific timestamp to a backup? For example, if the backups are going to the same drive location on disk and you want to retain 3 days worth of backups online, how do you add the timestamp to the filename to make each backup unique?
F:MSSQL.1MSSQLBackup and your user database is <xyz>_<timestamp>.bak
The user database dumps each night at 9 PM.
You want to keep 3 days of online backups.
On the SQL Server the Event Viewer shows the same messages and errors every evening between 22:05:00 and 22:08:00. The following information messages are shown for every database:
"I/O is frozen on database <database name>. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."
"I/O was resumed on database <database name>. No user action is required."
"Database backed up. Database: <database name>, creation date(time): 2003/04/08(09:13:36), pages dumped: 306, first LSN: 44:148:37, last LSN: 44:165:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{A79410F7-4AC5-47CE-9E9B-F91660F1072B}4'}). This is an informational message only. No user action is required."
After the 3 messages the following error message is shown for every database:
"BACKUP failed to complete the command BACKUP LOG <database name>. Check the backup application log for detailed messages."
I have added a Maintenance Plan but these jobs run after 02:00:00 at night.
Where can I find the command or setup which will backup all databases and log files at 22:00:00 in the evening?
Hi Everyone....
Crazy one here....
I need to populate a table with all the times that
are available in a 24 hour period, down to the 5 minute
interval.
So the table should look like....
id ds (datetime stamp)
--- --------------------------
0 1/1/2005 00:00:00
1 1/1/2005 00:05:00
2 1/1/2005 00:10:00
3 1/1/2005 00:15:00
.........
xx 1/1/2005 23:55:00
Please advise on a way to accomplish this in a script....
thanks
tony
One of my SQL Servers (SQL 2000 SP4) is reporting this error in the Maintenance Plan. But shortly after it shows this error is does successfully backup my Databases (per log history), however the plan is indicating "failure".
I have no idea how to resolve this nor why it is happening.
Rob.
There is a lot of documention concerning data(base) backups, but I could not find information about how to backup a complete SQL Server instance (data + configuration) or the SQL server configuration (= everything, that is not user payload). you always re-configure your system after restoring, especially because you will forget some settings?I found following things necessary to backup configuration:
server-wide:
security: login, roles, ...
server objectsserver properties (e.g. collation, connection or memory settings; see SSMS "Server - Properties")
per database
database settings (SSMS - database - properties)scheme
1. Is there anything missing?In case of a severe system crash I would like to be able to recover the system using a full system image of the virtual machine. However I just create these images after significant changes (e.g. Windows service pack).The SQL server should be backuped independently from these system images.
2. Even if I used SQL Server maintenance plan, choose "backup all databases" and additionally backup resources DB manually I think, that some configuration is still missing, right? Where are the security configuration and server properties saved? In my system database there are some tables, but there is very little content inside, so that the data from the views INFORMATION_SCHEMA* and sys.* obviously is not saved there.SQL Server system objects, such as sys.objects, are physically persisted in theResource database, but they logically appear in the sys schema of every database. URL....What does that mean: Are all DB/table schemes, logins, ... saved in resource DB?
3. Does a usual SQL Server full database backup also contain all settings concerning this database (database properties, logins, ...)?
4. Is there at least a way to backup ONLY the configuration (server-wide and database) without data? The only tool I could find is DACPAC, that exports a database's scheme and some other configuration, but e.g. the database properties are not included: By default, the database created during the deployment will have the default settings from the CREATE DATABASE statement. The exception is that the database collation and compatibility level are set to the values from the source database. URL..
I need to copy a just-created bak file to another drive after the backup task has completed. I don't see anything in the job toolbox which works with file system operations like this. But still it must be a common need..There are ways to script this or use third part tools but I am looking for something native to the sql server 2012 SSMS toolset, if possible.
An alternate approach would be to run the backup job again, after the main backup, and change the destination to the alternate location. But I was thinking that another backup job would probably invoke more overhead on the server than a simple file copy operation. If I do end up taking this approach I could also use the cleanup task to toss older bak files in the alt dir.
how can i copy a complete database (tables, views, stored procedures) with/in the sql server 2005 "server mgm. studio". the import/ export function only copys the data (tables).
sql server 2000 had a nice tool for that (import/ export data). but how can i do that with the sql server 2005. can't find anything ...
Hello all! A while ago I started cataloging my collection of old radio recordings and I wrote a simple Visual Basic 2005 program to display them in a nice UI. I have implemented an SQL database with the project and all is fine, when I run the program it connects to the sql server and downloads the data. But when I try and change something by connecting from within Visual Basic at runtime with this code:
Private Sub Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Save.Click
Dim ConnectionString As New SqlConnection("Server=MARK-HOMESQLEXPRESS;Database=Library.mdf;Uid=MARK-HOMEMark;Pwd=sodoff;")
Dim sqlString As String
ConnectionString.Open()
sqlString = "UPDATE JackBenny SET Rating='Excellent' WHERE [Episode Number]=" & epNumber
ConnectionString.BeginTransaction(sqlString)
ConnectionString.Close()
End Sub
I get this error code in the log and a general unable to login error within Visual basic itself:
2007-11-22 15:44:39.78 Logon Error: 18456, Severity: 14, State: 5.
2007-11-22 15:44:39.78 Logon Login failed for user 'Mark'. [CLIENT: <local machine>
The state of 5 refers to "User ID is not valid." but I can login to SQL Server Management Studio Express just fine with the username and password in the code posted. Also of note, I can login to the Server Managment with the sa account username and password. When I use the credentials in visual basic code, same error. Any ideas? I am trying to connect to a database which is on this computer (local) in the root folder of the application, called Library.mdg.
Thanks so much all
Hi,
How can I encrypt / decrypt whole database (data, objects ... everything) in SQL Server 2005 Express Edition?
Quick solution from any champion of Databases would be highly entertained.
Waiting ..
Thanks a lot.
Hello,
I want to truncate all tables present in the particular database, Is there any simple way to do it? or do I have to do it on individula basis (table by table)?
regards
A couple of days ago I installed SQL Server 2005 developer. To pave the way for this I removed SQL Server 2000 and 2005 Express. Since then I've been unable to connect from any of my applications or websites in Visual Studio 2005 to either:- my new 2005 Developer database engine- my colleagues 2005 developer database engine. My colleague can connect to my databases from his visual studio on his machine. I am receiving unspecified OleDb errors.Anyone else had this happen? Anyone know where I should start looking? I've uninstalled all .Net frameworks and re-installed 1.1 and 2.0.Many thanks, Sam
View 3 Replies View Related
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
HiI have have two linked SQL Servers and I am trying to get remote writesworking correctly (fast).I have configured the DB link on both machines to:Point at each others DB.I have security set up to map each others server loginsand Server Options: Collation Compatible, Data Access, RPC, RPC Out, UseRemote Collation all checkedMy problem is that when a SP performsBegin TransactionUpdate Local TableUpdate Remote TableCommit TranIt takes several seconds to complete. (about 7 seconds not acceptable tous)This is due to the remote update - how can I improve the response time?example of a stored procedures that takes timewhere ACSMSM is a remote (linked) SQL Server.procedure [psm].ams_Update_VFE@strResult varchar(8) = 'Failure' output,@strErrorDesc varchar(512) = 'SP Not Executed' output,@strVFEID varchar(16),@strDescription varchar(64),@strVFEVirtualRoot varchar(255),@strVFEPhysicalRoot varchar(255),@strAuditPath varchar(255),@strDefaultBranding varchar(16),@strIPAddress varchar(23)asdeclare @strStep varchar(32)declare @trancount intSet XACT_ABORT ONset @trancount = @@trancountset @strStep = 'Start of Stored Proc'if (@trancount = 0)BEGIN TRANSACTION mytranelsesave tran mytran/* start insert sp code here */set @strStep = 'Write VFE to MSM'updateACSMSM.msmprim.msm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID;set @strStep = 'Write VFE to PSM'updateACSPSM.psmprim.psm.VFECONFIGsetDESCRIPTION = @strDescription,VFEVIRTUALROOT = @strVFEVirtualRoot,VFEPHYSICALROOT = @strVFEPhysicalRoot,AUDITPATH = @strAuditPath,DEFAULTBRANDING = @strDefaultBranding,IPADDRESS = @strIPAddresswhereVFEID = @strVFEID/* end insert sp code here */if (@@error <> 0)beginrollback tran mytranset @strResult = 'Failure'set @strErrorDesc = 'Fail @ Step :' + @strStep + ' Error : ' + @@Errorreturn -1969endelsebeginset @strResult = 'Success'set @strErrorDesc = ''end-- commit tran if we started itif (@trancount = 0)commit tranreturn 0
View 2 Replies View RelatedI have read the forums and fail to be encouraged about using the timestamp to handle concurrency. I have SQL 2005 and am using the DataReader and command objects for data manipulation. The database has about a dozen related tables to each other in some way. Correct me if I am wrong, but using timestamps means I must store each table's original rowversion at read time in several possible combinations of variables (based on the joined tables at the time of read). Is this right? Then at update time, what if some related tables are timestamped with the original value and others have changed since read time? How would each update stored procedure know which other dependent tables were fetched at read time? It's easy to corrupt the data this way.
SELECT s.OrderNumber, a.UserName AS OrderedBy, cn.LastName + ', ' + cn.FirstName AS ContactName, s.CarbonCopy, s.Application, s.OrderDate,
s.FollowUpDate, s.ProdStartDate, s.InternalNote, s.SampleNote, s.UpdatedBy, s.DateLastUpdated, cn.EMail AS ContactEmail, s.MfgID,
co.CompanyName, s.ShipVia, cn2.LastName + ', ' + cn2.FirstName AS ContactName2, co2.CompanyName AS Expr1,
cn3.LastName + ', ' + cn3.FirstName AS ContactName3, s.MfgContactID, s.DistributorID, s.DistContactID, s.CustomerContactID,
s.VersionStamp, a.VersionStamp, cn.VersionStamp, co.VersionStamp, cn3.VersionStamp <------- Is all this really necessary?
FROM Samples AS s LEFT OUTER JOIN
Associates AS a ON s.OrderedByID = a.AssociateID LEFT OUTER JOIN
Contacts AS cn ON s.CustomerContactID = cn.ContactID INNER JOIN
Companies AS co ON s.MfgID = co.CompanyID LEFT OUTER JOIN
Contacts AS cn3 ON s.DistContactID = cn3.ContactID LEFT OUTER JOIN
Contacts AS cn2 ON s.MfgContactID = cn2.ContactID LEFT OUTER JOIN
Companies AS co2 ON s.DistributorID = co2.CompanyID
WHERE (s.SampleID = @SampleID)
What would be a simple way to update a table that depends on other tables having original version stamps? Then at runtime how would I enforce it without generating violations when the users were updating, say, the Samples table and just viewing the dependent tables' columns, not changing them.
When i try to load tables from DB2 OS390 Via DTS and DB2 ODBC it
give me an error with the timestamp field. Its (DTS) says the timestamp
field on SQL is marked read only.
Is there anyway around this problem?
Hello!
I think I am missing some essential idea when working with a timestamp column, maybe someone around here can give me the needed pointers on how-to...
My situation is: I want to copy data periodically from one DB to another. Both are in the same SQL instance, so there are no transaction, networking or similar issues involved. It all really comes down to identifing the new rows in source and moving them over to destination.
I added a TIMESTAMP column to my source, and I can see it count up slowly for every line inserted. On the destination side, I added a table containig just one field, type binary(8) to store the last value to which the last transfer ran.
Notice that on source side I got the counter on every row, but on destination side I got only one value, I thought it would be waste to carry over the data for every row when I really only need the latest one.
Now, what my package does is:
a) Select the last used binary(8) value into a package variable named TS of type object (works)
b) Start a dataflow, where the source is SELECT statement and the WHERE clause is TIMESTAMP > package variable TS (works)
c) Multicast the data into two recordsets
d) One recordset makes most of the columns flow through some Lookups, Derive Columns into a Destination and are written back. (works)
e) The other exit of the Multicast shall go through all lines and catch the highest TIMESTAMP that came by using an Aggregate and write it down, since that is the point I need to pick up later. (and that is the problem)
Problems are: I cannot run Aggregate max on the TIMESTAMP. Only Count is allowed.
So I need to convert the BINARY(8) into a number to be able to catch the max value. Converting between BINARY(8) and DT_I8 or similar seems not possible, or the result is wrong due to the byte ordering (MSB/LSB, most/less significant byte first)
Later on, I need to write out my new found highest value again, but here the same problem applies. How do I convert from DT_I8 back to BINARY(8)?
Having my own reference value stored as bigint instead of binary(8) does not work either: You cannot assign bigint from a select to a package variable, bigint are loaded as string (as I read in a blog, and I think they are right since I get exactly the same errors).
So...
HOW does one work in an efficient way with a TIMESTAMP column to Aggregate it to max and store out this max value?
Or do you all keep the TIMESTAMP from source appended to your rows and stored with each row in destination (Wasting eight precious bytes a row)?
Thanks for your comments!
Ralf
Script: [URL] ....
I need to move all of the contents of one database into anther with the same schema, and it looks like this might be just what I need. But it is from 2007, so I wonder if it is still current?
Also, having tried to run it on another database to generate the script that will actually do the copying, I have a few questions. It looks like it generates statements to import the data twice. For example:
BULK INSERT [TaPerfGDB].[dbo].[i1]
FROM 'C:Tempi1.Dat'
WITH (FORMATFILE = 'C:Tempi1.FMT',
BATCHSIZE = 1000000,
ERRORFILE = 'C:TempBI_i1.ERR',
TABLOCK);
And a little later:
INSERT INTO [TaPerfGDB].[dbo].[i1]
SELECT *
FROM OPENROWSET(BULK 'C:Tempi1.Dat',
FORMATFILE='C:Tempi1.Xml'
) as t1;
That does not really make any sense to me. It also generates statements like this:
bcp "[TaPerfGDB].[dbo].[GDB_GEOMNETWORKS]" format nul -n -CRAW -f "C:TempGDB_GEOMNETWORKS.fmt" --S"PGALLUCC-M7" -T
What is the deal with the double hyphen by the servername? Won't it just see that as a comment? It can be easily fixed, but I am just suprised that it is still there after all these years. My purpose in doing this is a desparate attempt to salvege a database that sits on a server with multiple drive errors. These prevent backups, so I cannot just restore the database on the new server. That is why I want to try an approach that goes table by table, so that at least all the tables which are not touched by the drive errors can be moved.
It is a 3 TB database running on SQL Server 2008 R2 std. ed.
Hi there
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
Thanks
Jane
I'm trying to filter out timestamps that land exactly at .000 milliseconds. (e.g. 2007-12-05 16:30:50.000) Do I have to convert the timestamp to a string first and then use the LIKE statement? If so, can somebody show me how. I'm pretty green to SQL but know the basics. Any help would be greatly appreciated!
View 2 Replies View RelatedI have an Access XP ADE application connected to a SQL Server 7.0 SP4database. I have created a timestamp column in the main table.Unfortunately, I am now getting persistent write conflict errors.The order of operations are:1. The application starts and loads the recordset into the form using astored procedure.2. I modify a field and press a save button which uses me.dirty=false toforce a save.3. The field is saved to the database. Using profiler I can observe themodified field being saved. As I would expect, the update statement isusing the primary key and the timestamp column value. For the sake of thisdiscussion let's assume the value of the timestamp is 5ad9.4. Without navigating off the record, I alter the same field (or adifferent field) and press save again and a write conflict will appear.Using profiler I can see the update statement that is attempting to updatethe record. The update statement is using the previous value (5ad9) of thetimestamp column.I thought that the timestamp column value is incremented each time therecord is updated. The ADE application does not appear to be recognizingthe new timestamp value.Any help or advice you could give would be appreciated.ThanksGeorge
View 3 Replies View RelatedGreetings once again SSIS friends,
I have some source tables which contain timestamp fields (that's timestamp data type not datetime). My dimension table holds the maximum timestamp value as a varbinary(8).
I want my package to have a variable that holds that value but I don't know which data type to use for this. The reason for this is because I want to use that variable to then retrieve all records from my source table that have a timestamp value greater than the value stored in the variable.
Please advise on what data type is suitable.
Thanks for your help in advance.
Hello all. I was just wondering how I could add the current date and time to the name of the .bak file. I perform a full backup daily and would like to keep a weeks worth of backups, but everytime I run the job, it just overites the previous one.
The command in the wizzard is:
BACKUP DATABASE [snl] TO DISK = N'C:Backups_for_FTPSNL_Full.bak' WITH NOINIT , NOUNLOAD , NAME = N'snl backup', SKIP , STATS = 10, DESCRIPTION = N'SNL Backup', NOFORMAT
Thanks,
Parallon
We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
View 5 Replies View RelatedI have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
View 13 Replies View RelatedI should restore a SQL Server 2005 Database from backup. The backup contains three files, named user.bak0, user.bak1 and user.bak2.
How is the syntax of the restore filelistonly and the restore database ... ?
I usualy write
restore filelistonly from disk = 'path and filenam.bak'
restore database. zy
from disk = 'path and filename.bak'
with replace,
move.....
move....
This works but I cannot use it with a splitted backup file. The files are much too big to put together to one file.
Thanks in advance for any help.
I have a full backup and several diff backup,now i want to restore
firstly,I restore full backup
RESTORE DATABASE ***
FROM DISK = 'D:databackup200610140000.bak'
WITH NORECOVERY
GO
it's working,then i don;'t know how to continue
Thanks in advance
I am new to ms SQL. I only have the use of the Enterprise Manager.
Creating tables I understand. However I am confused on how to add data to
a table or view the data in a table. Can this be done through Enterprise Manager? If I am adding a large amount of data do I have to use the query window. This seems like a tedious method. In Access yo have a form basically pop up where you can type in the record sets. Any advice would be appreciated! Sincerely,
Bill Bequette
Hi All,I am facing this issue very frist time. I add a logical device asfollowing:USE masterGOEXEC sp_addumpdevice 'disk', 'AdvWorksData','C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLBACKUPAdvWorksData.bak'-- Create a logical backup device, AdvWorksLog.USE masterGOEXEC sp_addumpdevice 'disk', 'AdvWorksLog','C:Program FilesMicrosoft SQLServerMSSQL.1MSSQLBACKUPAdvWorksLog.bak'-- Back up the full AdventureWorks database.BACKUP DATABASE AdventureWorks TO AdvWorksData-- Back up the AdventureWorks log.BACKUP LOG AdventureWorksTO AdvWorksLogThe database backup is completed successfully but log's backup failswith the following messages:Msg 4214, Level 16, State 1, Line 1BACKUP LOG cannot be performed because there is no current databasebackup.Msg 3013, Level 16, State 1, Line 1BACKUP LOG is terminating abnormally.We are moving from SQL 2000 to SQL 2005. I have ensured that databasehas full recovery mode on. We are using SQL 2005 on Windows 2003 ServerSP1.Further more, I also created a plan to test it, and plan also failswhen backing up the logs.Can anyone shed some light on this issue.Thanks in advance.Najm
View 1 Replies View RelatedHi,
I have a MS SQL Server 2005 Enterprise Edition 9.0.2153 which manages my BizTalk Server 2006 Databases. The BizTalk server installer automatically creates a job named Backup BizTalk Server (BizTalkMgmtDb) which should back up the databases and transaction protocolls.
The SQL Server Agent runs under domain-administrator account which has full access to my backup directory D:ackups.
The job executes the following steps:
Step 1: BackupFull:
Command:
exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, 'D:Backups' /* location of backup files */
Step 2: MarkAndBackupLog
Command:
exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, 'D:Backups' /* location of backup files */
My challenges are:
1. The job doesn't back up my databases(D:Backups is empty after executing the job)
2. I get every time the following error in error protocol of the job:
BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Fehler 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Fehler 3013).
I have already switched the Databases from full to simple recovery mode and vica versa, it didn't help. Also, the above stored procedures doesn't include neither the TRUNCATE nor the LOG parameters for the logfiles so I wasn't able to solve this issue by adjusting these flags.
Any help would be appreciated.
Thanks in advance,
Greg
I am using sql server 2000 and windows server 2003 standard edition:
My database backup job is failing due to lack of disk space. I am taking the backup onto E drive and the
available free space on E drive is 6.85 GB and there are no other drives I can use for the database backups.
The size of mdf file is 21 GB and that of ldf file is 4.2 GB.
The transaction log back up job of that db ran fine.
This database recovery model is Full and
Auto shrink is not checked.
There is one primary filegroup for the database.
In this situation, I am thinking of the following option:
1. Backup the db and log files onto another network shared drive.
If I want to still use the same server E drive to backup the db and log files instead of using another network shared drives.
how can I do that.
Please let me know the best way of handling this issue.
Any help is greatly appreciated. Thanks!
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
Thanks!
SQL Server 2005 on Windows 2003 Server x64.