The Byte Stops Here

Sometimes you need a simple script to backup the full DB to a file.

Some of the uses of the script I found were:

• To create a nightly job; simply have a hard file backup on a drive
• To generate a file on demand for restore to other environments (dev/stage/uat)
• And to be part of some build process
  

This script automatically appends a timestamp, so each time you have a fresh copy of the DB. Works great for us! See the associated restore script as well.

See: http://blog.tech-cats.com/2007/10/sql-server-script-to-backup-database-to.html

I used to be a fan of MS SQL Server 2000. It was so easy to get up and running.

But, SQL Server 2005, is another story. To do even the most basic of things, requires, extensive knowledge or ugly workarounds.

We do a lot of merge replication with SQL Server 2005. We added some tables to two-way merges, and the replication failed. as usual, it was due to foreign key dependencies. (By default, SQL Sevrer wants to drop the table, and re-create it with replication properties.)

There is no command that I know of, where you can select the PK, and say, give me all foreign keys that link to this PK. Easy enough Use Case though, right? There are some scripts out there to give similar column names, but those aren't accurate, because they dont have a foreign key reference necessarily.

After some searching, I found this. It was everything I needed. If you guys have better scripts, do share!

select cast(f.name  as varchar(255)) as foreign_key_name
    , r.keycnt
    , cast(c.name as  varchar(255)) as foreign_table
    , cast(fc.name as varchar(255)) as  foreign_column_1
    , cast(fc2.name as varchar(255)) as foreign_column_2
    ,  cast(p.name as varchar(255)) as primary_table
    , cast(rc.name as varchar(255))  as primary_column_1
    , cast(rc2.name as varchar(255)) as  primary_column_2
    from sysobjects f
    inner join sysobjects c on  f.parent_obj = c.id
    inner join sysreferences r on f.id =  r.constid
    inner join sysobjects p on r.rkeyid = p.id
    inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
    inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
    left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
    left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
    where f.type =  'F'
 ORDER BY cast(p.name as varchar(255))